LiteDB query on list

Question

I am having issues being able to figure out how to query a list inside an object effectively.

I have the following classes:

 public class IdentitySubstitutionModel
 {
        public Guid Id { get; set; }
        public List<IdentityKeyPair> IdentityKeys { get; set; }

 }

 public class IdentityKeyPair 
 {
        public string Key { get; set; }

        public string Value { get; set; }
         ....
 }

I need to complete a query on the IdentityKeys list in IdentitySubstitutionModel, but the only way I have been effectively able to do this without getting errors takes way too long to complete by calling FindAll() first and then executing the query:

                using (var db = new LiteDatabase(_databaseFileName))
                {
                    // Get a collection (or create, if doesn't exist)
                    var collection = db.GetCollection<IdentitySubstitutionModel>(_tableName);

                    // Index document using document Name property
                    collection.EnsureIndex(x => x.IdentityKeys[0].Key);
                    collection.EnsureIndex(x => x.IdentityKeys[0].Value);

                    results = collection.FindAll().Where(p => p.IdentityKeys.Any(x => x.Key == 
                    identityKeyPair.Key && x.Value == identityKeyPair.Value)).ToList();
            }

I have tried many other ways, including this way, but I end up getting an error:

collection.Find(x => (x.IdentityKeys[0].Key == identityKeyPair.Key && x.IdentityKeys[0].Value == identityKeyPair.Value)).ToList();

Am I constructing the indexes wrong or is there a different way I need to do to complete the query?

score 0 · Answer 1 · answered Jun 19 '20 at 16:50

LiteDb is a No-Sql engine, it stores data as blobs of JSON. There are indexes, like you found out, but those indexes are on the JSON level, on the top level, the same as searches.

So unless you can bring your condition to the top level, the engine will have to unpack every single JSON object to get the managed array to apply your filter on, which obviously is very slow.

There are multiple ways you could do this, one of which would be to change your collection to have IdentityKeyPair and Guid as top level fields, and you repeat the same Guid for every IdentityKeyPair it's supposed to replace.

LiteDB query on list

1 Answers1