How to use MongoDB's Query and QueryBuilder in c# foreach loop?

Question

I am trying to query my collections but I'm not sure how to do an "append" of sorts to Query.And()

Here is my domain model to create the Item document:

public class Item
{
    public ObjectId Id { get; set; }
    public string ItemTypeTemplate { get; set; }
    public string UsernameOwner { get; set; }

    public IList<ItemAttribute> Attributes { get; set; }
}

The IList<ItemAttribute> collection changes depending on ItemTypeTemplate (some sort of a lookup key to a pre-determined list of an item's attributes)

Here is a sample of an Item document:

{
    "_id" : ObjectId("5130f9a677e23b11503fee72"),
    "ItemTypeTemplate" : "Tablet Screens", 
         //can be other types like "Batteries", etc.
         //which would change the attributes list and values
    "UsernameOwner" : "user032186511",
     "Attributes" : [{
         "AttributeName" : "Screen Size",
         "AttributeValue" : "10.1"
     }, {
         "AttributeName" : "Pixel Density",
         "AttributeValue" : "340"
     }]
}

THE PROBLEM

Given the "dynamic" nature of IList<ItemAttribute>, I cannot manually specify the additional query conditions for AttributeName and AttributeValue so I thought of using a loop to build the query:

QueryBuilder<Item> qbAttributes = new QueryBuilder<Item>();

foreach (var attribute in item.Attributes)
{
    qbAttributes.And(
        Query.EQ("Attributes.AttributeName", attribute.AttributeName),
        Query.EQ("Attributes.AttributeValue", attribute.AttributeValue),
    );
}

var query = Query.And(
    Query.EQ("TemplateId", item.TemplateId),
    Query.NE("UsernameOwner", item.UsernameOwner)
);

return DBContext.GetCollection<Item>("Items").Find(query).AsQueryable();

How do I "append" qbAttributes to query? I tried qbAttributes.And(query); but .Find(query) errors out with an invalid argument.

I need something that would be like:

var query = Query.And(
    Query.EQ("ItemTypeTemplate", item.ItemTypeTemplate),       //Tablet Screens
    Query.NE("UsernameOwner", item.UsernameOwner)              //current user

    // this part is generated by the loop

    Query.EQ("Attributes.AttributeName", "Screen Size"),
    Query.EQ("Attributes.AttributeValue", "10.1"),

    Query.EQ("Attributes.AttributeName", "Pixel Density"),
    Query.EQ("Attributes.AttributeValue", "340")
);

Solution

While untested (as I don't have a scenario similar to yours to test with), you should be able to just add the various and conditions to a collection (that implements IEnumerable) like this, and then pass it to the And method of the QueryBuilder instance:

var andList = new List<IMongoQuery>();

foreach (var attribute in item.Attributes)
{
    andList.Add(Query.EQ("Attributes.AttributeName", attribute.AttributeName));
    andList.Add(Query.EQ("Attributes.AttributeValue", attribute.AttributeValue));
}

andList.Add(Query.EQ("TemplateId", item.TemplateId));
andList.Add(Query.NE("UsernameOwner", item.UsernameOwner));

var query = new QueryBuilder<Item>();
query.And(andList);
// do something with query ...

The above code should be the equivalent to performing an $and on all of the specified conditions.

Comments

[Nuk Nuk San]

Watch out for array elements, and look at Watch out for array elements, and look at docs.mongodb.org/manual/reference/projection/elemMatch . using 2 "and" terms on an array does not mean that attr name and attr value occur within the same element.