Litedb: [QUESTION] System.NotSupportedException: Any/All requires simple parameter on left side. - Querying nested array, how can I accomplish this?

[atresnjo]

LiteDB Version: 5.0.3

Hey guys, thanks for the work on LiteDB ! I am checking out if LiteDB might work for my use case. Thanks for the help!

My entities:

    public class TagEntryModel
    {
        public int Id { get; set; }
        public string Value { get; set; }
    }

    public class JobEntryModel
    {
        public int Id { get; set; }
        public string Company { get; set; }
        public string Title { get; set; }
        public string Subtitle { get; set; }
        public string Url { get; set; }
        public DateTime Date { get; set; }
        [BsonRef("JobEntryModel")] public List<TagEntryModel> Tags { get; set; } = new List<TagEntryModel>();
    }

Basically I want to be able to filter out job entries by tags, title, company and then order by date. Is this possible? What I have right now is:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using LiteDB;

namespace LiteDbTestApp
{
    class Program
    {
       static void TestQuery()
        {
            if (File.Exists("test.db"))
                File.Delete("test.db");

            using var db = new LiteDatabase("Filename=test.db;Connection=Direct");
            var tagCollection = db.GetCollection<TagEntryModel>();
            var jobCollection = db.GetCollection<JobEntryModel>();

            var tag = new TagEntryModel {Value = "react"};
            tagCollection.Insert(tag);
            tagCollection.EnsureIndex(x => x.Value);

            var job = new JobEntryModel
                {Title = "random job title", Company = "random company", Tags = new List<TagEntryModel> {tag}};
            jobCollection.Insert(job);
            jobCollection.EnsureIndex(x => x.Tags);

            var findTags = new List<string> {"react"};
            var query = jobCollection.Query()
                .Include(x => x.Tags)
                .Where(x => x.Title.Contains("ti"))
                .Where(x => x.Tags.Select(model => model.Value).Any(b => findTags.Contains(b)))
                .OrderBy(x => x.Date)
                .Limit(50);

            var reader = query.ExecuteReader();
            var entities = reader.ToList();
            Console.WriteLine(entities.Count);
        }

        static async Task Main(string[] args)
        {
            TestQuery();
        }
}

Right now I am getting the following exception thrown:

Unhandled exception. System.NotSupportedException: Any/All requires simple parameter on left side. Eg: x.Customers.Select(c => c.Name).Any(n => n.StartsWith('J'))

[lbnascimento]

@atresnjo Currently, the Linq-to-BsonExpression converter only supports simple expressions inside Any, i.e. expressions where the left side uses the parameter (it would be really simple to add support for it if Linq had a In method, but it doesn't).

It's possible to use SQL syntax, however the result would be an array of BsonDocument and the results would have to be manually mapped to the .NET class using BsonMapper.Global.Deserialize.

C# var results = db.Execute("select $ from JobEntryModel include Tags where $.Tags[*].Value any in @0", BsonMapper.Global.Serialize(findTags)).ToArray();

We are currently thinking of ways to improve our BsonValue mappings and implicit conversions.

[atresnjo]

@atresnjo Currently, the Linq-to-BsonExpression converter only supports simple expressions inside Any, i.e. expressions where the left side uses the parameter (it would be really simple to add support for it if Linq had a In method, but it doesn't).

It's possible to use SQL syntax, however the result would be an array of BsonDocument and the results would have to be manually mapped to the .NET class using BsonMapper.Global.Deserialize.

var results = db.Execute("select $ from JobEntryModel include Tags where $.Tags[*].Value any in @0", BsonMapper.Global.Serialize(findTags)).ToArray();

We are currently thinking of ways to improve our BsonValue mappings and implicit conversions.

@lbnascimento

Okay, that makes sense. Thanks for the help, but using your suggestion I still get an empty collection returned. What am I doing wrong? By the way, as Tags.Value will be accessed a lot I supposed I should index it? How can I accomplish this? Thanks for the help! :)

        static void TestQuery()
        {
            using var db = new LiteDatabase("Filename=test.db;Connection=Direct");
            var tagCollection = db.GetCollection<TagEntryModel>();
            var jobCollection = db.GetCollection<JobEntryModel>();

            foreach (var tagEntryModel in tagCollection.FindAll())
            {
                tagCollection.DeleteMany(model => model.Id == tagEntryModel.Id);
            }

            foreach (var tagEntryModel in jobCollection.FindAll())
            {
                jobCollection.DeleteMany(model => model.Id == tagEntryModel.Id);
            }

            var firstTag = new TagEntryModel {Value = "react"};
            tagCollection.Insert(firstTag);

            var otherTag = new TagEntryModel {Value = "csharp"};
            tagCollection.Insert(otherTag);
            tagCollection.EnsureIndex(x => x.Value);

            var firstJob = new JobEntryModel
                {Title = "random job title", Company = "random company", Tags = new List<TagEntryModel> {firstTag}};
            jobCollection.Insert(firstJob);

            var otherJob = new JobEntryModel
                {Title = "cant find", Company = "random dont find", Tags = new List<TagEntryModel> {otherTag}};
            jobCollection.Insert(otherJob);
            jobCollection.EnsureIndex(x => x.Tags);

            var findTags = new List<string> {"react"};

            var results = db.Execute("select $ from JobEntryModel include Tags where $.Tags[*].Value any in @0",
                BsonMapper.Global.Serialize(findTags)).ToArray();
            var entities = results.ToList();
            Console.WriteLine(entities.Count);
        }

JobEntryModel collection

TagEntryModel collection

[lbnascimento]

@atresnjo The BsonRef attribute in Tags is wrong, it should be [BsonRef("TagEntryModel")]

[lbnascimento]

@atresnjo About the index, in your example you wouldn't be able to do anything relevant with Tags because it is a reference to another collection, and you can only index information pertaining to the same collection.

Actually, I think it would be best for the Tags to be stored as embedded documents inside every JobEntryModel. It's usually better to use embedded documents instead of references when modeling for a document-oriented database.

[atresnjo]

@atresnjo The BsonRef attribute in Tags is wrong, it should be [BsonRef("TagEntryModel")]

My bad! Thank you a lot for the help.