How to AND multiple query filters together in a loop dynamically? MongoDB C#

Question

This is my first project ever working with MongoDB and NoSQL so I know I might be making a lot of rookie mistakes here. OK, first I will explain the problem statement that I am working on. Here is one document of a product in my collection in JSON

{
    "_id" : ObjectId("58b19bb67425e833c4ca4a65"),
    "features" : [ 
        {
            "feature" : "Performance",
            "rating" : -1
        }, 
        {
            "feature" : "Speed",
            "rating" : -2
        }, 
        {
            "feature" : "Durability",
            "rating" : -1
        }
    ],
    "brand" : "EVGA",
    "stars" : 4,
    "productId" : "B01H0WU884",
    "productName" : "EVGA GeForce GTX 1070 SC GAMING ACX 30 8GB GDDR5 LED DX12 OSD Support PXOC Graphics Card 08G-P4-6173-KR",
    "productDescription" : "The EVGA GeForce GTX 1070 featuring EVGA ACX 3.0 cooling has arrived. This new graphics card features NVIDIA's new \"Pascal\" graphics processor which is the most advanced gaming GPU ever created.",
    "productCategory" : "Hardware",
    "productPrice" : "17000",
    "createdAt" : ISODate("2017-02-25T14:59:02.816Z"),
    "comments" : []
}

And the class Product in C#

public class Product
{

    public ObjectId id { get; set; }
    public string productId { get; set; }
    public string productName { get; set; }
    public string productDescription { get; set; }
    public string productCategory { get; set; }
    public string productPrice { get; set; }

    public DateTime createdAt { get; set; }

    public List<ProductFeature> features;
    public List<string> comments { get; set; }
    public string brand;
    public int stars;
}

And class ProductFeature

public class ProductFeature
{
    public string feature { get; set; }
    public int rating { get; set; }
}

And here is one document of a Category in my collection in JSON

{
    "_id" : ObjectId("58b199627425e81e7c56eff1"),
    "Features" : [ 
        "Performance", 
        "Speed", 
        "Durability"
    ],
    "Name" : "Hardware"
}

And class Category in C#

public class Category
{
    public List<string> Features { get; set; }
    public string Name { get; set; }
    public ObjectId id { get; set; }
}

Now, here's what I need to do. I have a List<Category> list called interests. I need to select the products from the collection which are from the categories that I have in my list of categories, where each product I get must have at least one feature with rating > 0 and that feature also exists in the list of features attached with each category in the list of categories.

I know it seems complicated and that's why I need help. The following code, although incorrect, will help you understand a little bit better. I need to know how to make this piece of code work. I have commented exactly on which lines I am having issues.

    var query = null; // Error: Can't initialize 'var' object to null
    for (int i=0;i<interests.Count;i++)
    {
        var q1 = Query<Controller.Product>.Where(p => p.productCategory == interests[i].Name);
        var q2 = null; // Error: Can't initialize 'var' object to null
        for (int j=0;j<interests[i].Features.Count;j++)
        {
            // Exception on this next line
            var q3 = Query<Controller.Product>.Where(p => p.features.All(f => f.feature == interests[i].Features[j] && f.rating > 0)); 
            q2 = Query.Or(q2, q3);
        }
        query = Query.Or(query, Query.And(q1, q2));

    }
    var result = collection.Find(query).SetLimit(20);

Here is the Exception that I am getting

Unsupported where clause: Enumerable.All(p.features, (ProductFeature f) => ((f.feature == "Performance") && (f.rating > 0))).

If I put Any instead of All in the filter definition, the code starts to work but I need All to work. Any help is appreciated.

Answer 1

Based on example you will need below query.

db.product.find({
    "productCategory": "Hardware",
    "features": {
        "$elemMatch": {
            "feature": {
                "$in": ["Performance",
                    "Speed",
                    "Durability"
                ]
            },
            "rating": {
                "$gt": 5
            }
        }
    }
})

Below is simplified C# code for getting products for all categories

var pQuery = Query.Empty;
for (int i = 0; i < interests.Count; i++) {
    var cQuery = Query.And(
        Query<Product>.EQ(u => u.productCategory, interests[i].Name),
        Query<Product>.ElemMatch(f => f.features, e => Query.And(
            Query<ProductFeature>.In(u => u.feature, interests[i].Features),
            Query<ProductFeature>.GT(u => u.rating, 5))));
    pQuery = Query.Or(pQuery, cQuery);
}
var result = collection.Find(pQuery).SetLimit(20);
}