MongoDB with C# - query with dynamically generated predicate

Question

I am having trouble querying a MongoDB database using a predicate which is dynamically generated.

The document structue i am trying to query on is:

{ 
    "_id" : 3121 , 
    "Active" : true , 
    "CategoryId" : 1 , 
    "Crci" : "IH" , 
    "CultureId" :  null  , 
    "DateUpdated" : { 
            "$date" : 1381916923120
    } , 
    "Description" : "National Careers Service: Actuary" , 
    "Keywords" : "" , 
    "MaxLevel" :  null  , 
    "MinLevel" :  null  , 
    "PhoneNumber" : "                    " , 
    "Priority" : 1 , 
    "Title" : "National Careers Service: Actuary" , 
    "WebUrl" : "https://nationalcareersservice.direct.gov.uk/advice/planning/jobprofiles/Pages/actuary.aspx" , 
    "CareerCultureExternalResources" : [ 
        { 
                "CareerId" : 5 , 
                "CultureId" : 1 , 
                "DisplayOrder" : 1 , 
                "ExternalResourceId" : 3121 , 
                "Vgs" :  null 
        }
    ] , 
    "SubjectExternalResources" : [ ] , 
    "LifestyleCategories" :  null
}

And I am trying to query using at predicate which is generated a runtime E.g.:

Expression<Func<ExternalResourceView, bool>> predicate = predicateBuilder.True<ExternalResourceView>();
if (request.CultureId != null && request.CareerId != null) {
    predicate = predicate.And(er => er.CareerCultureExternalResources.Any(ccer => ccer.CareerId == request.CareerId.Value && ccer.CultureId == request.CultureId.Value));
} 
else if (request.SubjectAreaId != null && request.SubjectLevel != null && request.CultureId != null)
{
    predicate = predicate.And(er => er.SubjectExternalResources.Any(ser => ser.SubjectAreaId == request.CareerId && ser.CultureId == request.CultureId));
}
IEnumerable<ExternalResourceView> matchingExternalResources = _externalResourceLibrary.AsQueryable().Where(predicate)

When I try and execute the query I get the error: "Unsupported where clause: <InvocationExpression>."

I am unable to find if the Linq to Mongo is able to generate a query from such an expression or not (I think it should).

So am I just trying to do something that the C# Mongo driver doesn't support or am I not generating the predicate right? I have seen from another question that what I'm trying to do should be possible (in theory at least).

I am using the official 10gen driver ver 1.8.3

@RaphaëlAlthaus I completely forgot about AsExpandable()! And it did solve the problem. If you add this as an answer I will accept it — Anduril, Jan 30 '14 at 16:41

score 4 · Accepted Answer · answered Jan 30 '14 at 16:43

4

Not sure it's a solution for mongodb, but could you try to use AsExpandable() coming from linqkit (made by the same guy as PredicateBuilder).

LinqKit (and more info about AsExpandable() and relation with PredicateBuilder) can be found here

answered Jan 30 '14 at 16:43

Raphaël Althaus

59,727
6
96
122

1

I can confirm it works for MongoDb. If you have a predicate in the form `Expression> predicate;` Then you can query a mongo collection in C# by `collection.AsQueryable().AsExpandable().Where(predicate);` – Anduril Jan 31 '14 at 08:54
yes Expand does the trick for AndAlso expressions https://github.com/vip32/Naos/blob/f768336d666f3c11edf7ed2c051c0702ad0ff596/src/Infrastructure.MongoDb/Repositories/Extensions/WhereExpression.cs#L19 – vip32 Sep 25 '19 at 18:01

score 1 · Answer 2 · answered Jan 30 '14 at 16:35

Use the MongoDB Query builder instead of translating the predicate twice:

using MongoDB.Driver.Builders;
List<IMongoQuery> queries = new List<IMongoQuery>();
if (request.CultureId != null && request.CareerId != null) {
  queries.Add(
    Query<Person>.ElemMatch<IDependent>(p => p.Dependents,
      q => Query.And(
        q.EQ(x => x.CareerId, request.CareerId),
        q.EQ(x => x.CultureId, request.CultureId))));
}
// ... etc
// Final Query: 
var query = Query.And(queries);

I changed the query to use an $elemMatch, because I have the feeling you want to match both careerId and cultureId on the same array element. If that assumption is wrong, adjust the query accordingly.

Thank you, you're right that we are doing extra work essentially translating the predicate twice - but we want to stick with linq rather than tying our querying to mongo in case we wish to switch in the future — Anduril, Jan 30 '14 at 16:39

MongoDB with C# - query with dynamically generated predicate

2 Answers2

Linked