Dynamic predicate building in EF5

Question

Ok,

I have to build a predicate to select an unknown number of columns from a known entity in EF5 AND filter by an unknown number of columns ONE of which will always be a name from a child collection.

So this is what I need to end up with

var q = db.Set<Entity>()
          .Where(e => e.Code.Contains("q") || e.Translations.FirstOrDefault(t => t.Culture.ID == "whatever").Description.Contains("q"))
          .Select(e => new
                       {
                           e.ID,
                           e.Code,
                           Name = e.Translations.FirstOrDefault(t => t.Culture.ID == "whatever").Description
                       });

but I'm not sure how to approach building the predicate for the expression

e.Translations.FirstOrDefault(t => t.Culture.ID == "whatever").Description.Contains("q")

The columns to select and the fields to filter on are supplied as an array of strings, and in this instance the filter is always contains.

I'm familiar with building predicates, that's not the issue, it's more I've never had to look into child collections before and I'm at a total "WTF" point of the day :-)

Any and all pushes in the right direction would be most appriciated.

score 2 · Accepted Answer · edited May 23 '17 at 12:18

One approach might be to use DynamicLinq. See http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

If you look at the third query down, you should be able to do something like:

 e.Translations
.Where("Culture.ID = 'whatever'") //Conditions defined as string as per example
.FirstOrDefault() //etc.

Regarding select an unknown number of columns from a known entity in EF5 AND filter by an unknown number of columns, you could use whatever expression is required to build your string prior to using it. For the 'contains' clause it is a bit more complex - please see the methods described here - How to write String.Contains in Dynamic Linq

I haven't actually tested this as I don't have access to EF5, but it looks like there are EF5 based questions for DynamicLinq (so it should work) - https://stackoverflow.com/questions/9929396/entity-framework-5-0-performance-with-dyanmic-linq

Also, I should point out that I would prefer to use PredicateBuilder for this purpose, but I believe you will be unable to without knowing the fieldnames in advance/using some sort of reflection (see Using Dynamic Column Names in a Linq Query). It is also worth acknowledging this answer (https://stackoverflow.com/a/2497335/201648) which suggests the above approach.

Also, it is possible to setup conditional rules for fields using lambdas as described here: http://www.codeproject.com/Articles/28580/LINQ-and-Dynamic-Predicate-Construction-at-Runtime

// use tryparse to make sure we don't run a bogus query.
if (cbxUseEmployeeID.Checked &&
    int.TryParse(filterEmployeeId.Text, out emplId) &&
    emplId > 0) {

    // here's how simple it is to add a condition to the query.
    // Still not executing yet, just building a tree.
    predicate = predicate.And(e => e.EmployeeID == emplId);
}

This would still require you to know the possible set of fields as far as I can see (I still think its cool and worth a mention).

Dynamic predicate building in EF5

1 Answers1

Linked