Query by an element of an array of objects using C# driver

Question

I have objects like this sample in my database:

{ 
    "_id" : ObjectId("62623cd94c111e4e73c98e88"), 
    "CDate" : null, 
    "CUser" : "dexadminserver", 
    "MDate" : null, 
    "MUser" : null, 
    "ObjState" : "Active", 
    "CustomerName" : "Any sample company", 
    "CustomerNumber" : "2022.4-3480448518", 
    "VatId" : "CHE-468.077.207", 
    "CustomerType" : "SomeType", 
    "Description" : "Owner of the DEX Cloud System.", 
    "CustomerAddress" : {
        "Street" : "bla bla street", 
        "AdrInfo1" : null, 
        "AdrInfo2" : null, 
        "ZipCode" : "12345", 
        "City" : "Elons Paradise", 
        "Country" : "Mars", 
        "IsoCountryCode" : "MARS"
    }, 
    "CustomerInvoiceAddress" : {
        "Street" : "Any area", 
        "AdrInfo1" : null, 
        "AdrInfo2" : null, 
        "ZipCode" : "12345", 
        "City" : "Elons Paradise", 
        "Country" : "Mars", 
        "IsoCountryCode" : "MARS"
    }, 
    "CustomerContactData" : {
        "CompanyPhone" : null, 
        "CompanyEMail" : "[email protected]", 
        "CompanyHomepage" : null, 
        "SupportPhone" : null, 
        "SupportEmail" : null, 
        "Fax" : null, 
        "OtherPhone1" : null, 
        "OtherPhone2" : null, 
        "OtherEMail1" : null, 
        "OtherEMail2" : null, 
        "Remarks" : null
    }, 
    "CustomerContactPerson" : {
        "Salutation" : "", 
        "SalutationForLetters" : "", 
        "Title" : null, 
        "FirstName" : "Joe", 
        "LastName" : "Doe", 
        "MiddleName" : null, 
        "NickName" : null, 
        "Phone" : null, 
        "MobilePhone" : null, 
        "Email" : "[email protected]", 
        "LanguageCode" : "en"
    }, 
    "ProjectInventory" : [
        {
            "ProjectName" : "TestProject-2.2023", 
            "ProjectId" : "Test2022-4-34804485184053904178", 
            "ProjectTeamName" : "PT_TestProject-2.2023", 
            "ProjectTeamSpId" : "4d168ae2-907e-4fdf-8b1d-9d812f097820", 
            "ProjectOwnerId" : ObjectId("62623cd94c111e4e73c98e88"), 
            "ConfigList" : [

            ], 
            "ProjectStart" : ISODate("2022-04-22T05:27:54.184+0000"), 
            "ProjectEnd" : null, 
            "ObjState" : "Active"
        }
    ]
}

Now I like to get a list of CUSTOMER NUMBERS (using a projection) of all customers matching a specific List containing ProjectTeamSpIds in the ProjectInventory array. I prefer typesafe filters and projections using Builder classes.

I created the following code in C# (method in my data access layer)

    public List<string> GetCustomersOfProjectTeams(List<string> projectTeamSpIdList)
    {
        var collection = DexCloudDb.GetCollection<DexCustomer>(DexConstants.DexCustomerInventoryCollection);

        var filterBuilder = Builders<DexCustomer>.Filter;
        var projection = Builders<DexCustomer>.Projection.Expression(customer => customer.CustomerNumber);
        var filter = filterBuilder.ElemMatch(customer => customer.ProjectInventory,
            proj => projectTeamSpIdList.Any(z => proj.ProjectTeamSpId.Contains(z))); 
        try
        {
            var items = collection.Find(filter).Project(projection).ToList();
            return items;
        }            
        catch (Exception e)
        {
            // some exception handling code...
        }
    
    }

This code compiles, but a runtime I get the following exception:

Error: Unsupported filter: Any(value(System.Collections.Generic.List`1[System.String]).Where({document}{ProjectTeamSpId}.Contains({document}))).'"

Any idea what is wrong and how it can be correctly implemented?

The more general question is: How can I build a type safe filter in C# to query a collection contining an array of objects based on one (or more) elements/properties of that object.

Answer 1

OK, figured it out myself and the solution is pretty cool.

It seems to be possible to nest filters which means I can build a filter on the array of objects which is stored in the document and use this filter inside the filter for the collection. This looks then as follows:

var projection = Builders<DexCustomer>.Projection.Expression(customer => customer.CustomerNumber);
var projectFilter = Builders<DexProject>.Filter.In(p => p.ProjectTeamSpId, projectTeamSpIdList);
var customerFilter = Builders<DexCustomer>.Filter.ElemMatch(customer => customer.ProjectInventory, teamFilter);

var items = collection.Find(customerFilter).Project(projection).ToList();