[QUESTION] querying documents having a subdocument property that is in a list

[ssteiner]

Here's my document

    class PhoneBookCategory
    {
        public Guid Id { get; set; }
        public List<PhoneBook> PhoneBooks { get; set; }
    }

    class PhoneBook
    {
        public Guid Id { get; set; }
    }

what I would like is to query the ILiteCollection returning all documents that have a PhoneBook matching a list of Ids.

using Linq Syntax I'd write

    List<Guid> myList = [Guid.NewGuid(), Guid.NewGuid()];
    GetCollection<PhoneBookCategory>().Find(u => u.PhoneBooks.Any(x => myList.Contains(x.Id));

That doesn't work for known reasons. So I tried to write it using the Query helpers:

    var bsArray = new BsonArray();
    myList.Foreach(u => bsArray.Add(u));
    GetCollection<PhoneBookCategory>().Query().Where(Query.In("$.PhoneBooks[*].Id ALL", bsArray))).ToList();

That runs, but it returns all PhoneBookCategory objects hat have no PhoneBooks, not all PhoneBookCategory that are in myList.

What am I missing?

I tried doing it raw in LiteDB Studio, came up with this:

SELECT $ FROM phoneBookCategorys INCLUDE PhoneBooks WHERE $.PhoneBooks[*].Id ALL IN GUID('2d66ceca-91bd-4e68-a8d0-e7e19af65169') and still... with ALL I'm getting my 3 records that have an empty PhoneBooks collection, with ANY, I get zero results.

Here's one raw document from the DB - I'd expect this to be found with the GUID I'm providing

{
  "_id": {"$guid": "6cd5d963-a13c-47b3-90ab-100945a2c21b"},
  "PhoneBooks": 
  [
    {
      "_id": {"$guid": "2d66ceca-91bd-4e68-a8d0-e7e19af65169"}
    },
    {
      "_id": {"$guid": "998b90d9-ea82-4726-9d4b-64924fefc00f"}
    }
  ],
  "NumberOfPhoneBooks": 0,
  "ExternalImportObjectIdentifier": "Imported Category 1",
  "Name": "Imported Category 1",
  "Description": "hello",
  "IsDefault": false
}

[ssteiner]

Hmm.. so I finally figured out what's going on by looking at the raw query again. The prop I'm trying to search for is _id in the JSON. so if I write SELECT $ FROM phoneBookCategorys INCLUDE PhoneBooks WHERE $.PhoneBooks[*]._id ANY IN [GUID('2d66ceca-91bd-4e68-a8d0-e7e19af65169')] then things work as expected. I wish there were an error like 'there's no property called Id $.PhoneBooks[*]' (the ANY now works as expected)