[BUG] Quering Based On Referenced Entity Failed After Creating The Index

[AlBannaTechno]

Version 5.0.9

Describe the bug When I try to query based on a referenced entity, everything works fine until I create the index for that query?

Code to Reproduce

Suppose we have this

  class Human
        {
            public int Id { get; set; }
            public string Name { get; set; }

            [BsonRef("Homes")]
            public List<Home> Homes { get; set; }

            public Human()
            {
                Homes = new List<Home>();
            }
        }

        class Home
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public decimal Value { get; set; }
        }

And We have the next code to work with DB

    var db = new LiteDatabase(new MemoryStream());
        var humans = db.GetCollection<Human>("Humans");
        var homes = db.GetCollection<Home>("Homes");

    var h1 = new Home
            {
                Name = "Home N 1",
                Value = 655555
            };
            var h2 = new Home
            {
                Name = "Home N 2",
                Value = 854234
            };
            var h3 = new Home
            {
                Name = "Home N 3",
                Value = 788856
            };

            homes.Insert(h1);
            homes.Insert(h2);
            homes.Insert(h3);

            var human1 = new Human
            {
                Name = "Person 1",
                Homes = new List<Home>
                {
                    h1,h3
                }
            };

            var human2 = new Human
            {
                Name = "Person 2",
                Homes = new List<Home>
                {
                    h2
                }
            };

            var human3 = new Human
            {
                Name = "Person 32",
                Homes = new List<Home>
                {
                    h2,h3
                }
            };

            humans.Insert(human1);
            humans.Insert(human2);
            humans.Insert(human3);

and we need to get all Humans that Have a Home With Value More Than N ?

This Works

            var ls = humans.Include(h => h.Homes).Query().Where(Query.Any().EQ("$.Homes[*].Value", 655555)).ToList(); // OK

But if we define the index

            humans.EnsureIndex("$.Homes[*].Value");

we will not get any value ??

Expected behavior I think LiteDb Should Show The Result As Expected

Additional context @bnascimento Can you help me with that, I have a question

Suppose I create the next index

            humans.Include(h => h.Homes).EnsureIndex("home_value",h => h.Homes[0].Value);

How could I use this index explicitly? I see that in old Version 4 docs, also on the UnitTests in the current version, But can not apply the same case

I see a thing like this

            humans.Include(h => h.Homes).Query().Where(Query.GTE("home_value", 0));
        // or, for nested object with no DbRef, just document
       humans.Find(Query.GT("home_value", 0)).ToList();

But I can not use that Not work, even if I just use nested collection without any reference to another document,

I think, we need a revolution to revive the documentation, there are tones of information in the Tests, but not documented, I tried to do that, but I have leaks of information about LiteDb, but I will try to figure it.

[fubar-coder]

I stumbled upon this behavior too. I guess what happens on Insert is this:

• The reference to the Home object gets replaced by a DbRef
• The indexer tries to add a value from the property Value, but a DbRef doesn't have this property
  • This results in null(?) or nothing(?) being added to the index
• The Human object is inserted

What should happen is this:

• The index should be added to the Home collection (on the Value property)
• The index of the Home collection should be used for your query
• An index on a DbRefed object doesn't make sense and should at least cause a runtime error

Be aware that a DebRef never ensures referential integrity. It's just a tool to reduce the amount of duplicated information. The DbRefd object simply gets removed. It will never be persisted.

I'm not sure if it's simple to change the behavior above, but I feel the danger of deadlocks when I think about it.

This are some options what you could do instead:

• Try to make a reverse query. IOW: Try to make a query starting from the Home collection which should reference the Human collection.
• Split the query into two parts:
  • Query the Home collection first
  • Use the Home object IDs to query the Human collection
  • If you want to keep the DbRef, you can query for Human.Homes[*].$id ANY = @id
  • If you want to replace the DbRef and use the ID directly, you could use something like Human.HomeIds[*] ANY = @id

[AlBannaTechno]

@fubar-coder, until now I have no time to debug the source code of LiteDB, but, to notice, when creating an index with Fluent Syntax, and Querying With Fluent Syntax without explicitly writing the SQL statements, everything works fine,

Thank you for your idea, but the solution you provide, will degrees the performance to the worst level when using pagination,

So to get everything to work in all cases, this LiteDB Issue must fix, as soon as I have time for that I will work on it.

[fubar-coder]

@AlBannaTechno I think that it, when you use the fluent syntax, just doesn't use the index at all and just applies the condition after the Home object was merged back into the Human object.