How to use a Func in an expression with Linq to Entity Framework?

Question

I am trying to write a linq to entity extension method that takes a Func to select a property Id and compare it against a list of ids.

Classes

public class A
{
    public int AId { get; set; }
}

public class B
{
    public int BId { get; set; }
}

Extension Method

public static IQueryable<T> WithId<T>(this IQueryable<T> entities,
    Func<T, int> selector, IList<int> ids)
    {
        Expression<Func<T, bool>> expression = x => ids.Contains(selector(x));
        return entities.Where(expression); // error here (when evaluated)
    }

Calling Method

var ids = new List<int> { 1, 2, 3 };
DbContext.EntityAs.WithId(e => e.AId, ids);
DbContext.EntityBs.WithId(e => e.BId, ids);

The problem I am experiencing is that it is trying to Invoke the function which is not allowed in Entity Framework.

How can I use a property selector (Func) to evaluate the query?

The scope of code you can invoke in a EF query is limited by the fact it still needs to be translated in SQL. In your case EF doesn't know how to translate an IList automatically. — Sten Petrov, Sep 27 '13 at 13:59
I am not sure you are correct with that. DbContext.EntityAs.Where(e => ids.Contains(e.Id)) is translated by EF correctly. I'm just trying to make a re-usable function so I can define which property to select on. — David, Sep 27 '13 at 14:01
Because EF knows how to do `select x where x in (1,2,3)` in the case of enumerable or `select x where x in (select y)` in the case of another entity relationship. In your case EF would need to compile something like `select x where x in (select y where F(y) in (F(1),F(2),...))`. While it's possible to do this manually EF just doesn't support the case *yet* — Sten Petrov, Sep 27 '13 at 14:06
It should just evaluate to select x where F(y) in (1,2,3) where F(y) would be evaluated to be x.AId or x.BId? Is there any way to build this up manually in an expression tree? — David, Sep 27 '13 at 14:15

score 20 · Accepted Answer · edited May 23 '17 at 12:16

20

You'll have to pass an Expression<Func<T, int>> instead of an Func<T, int> and build up the complete expression yourself. This will do the trick:

public static IQueryable<T> WithId<T>(this IQueryable<T> entities,
    Expression<Func<T, int>> propertySelector, ICollection<int> ids)
{
    var property =
        (PropertyInfo)((MemberExpression)propertySelector.Body).Member;

    ParameterExpression parameter = Expression.Parameter(typeof(T));

    var expression = Expression.Lambda<Func<T, bool>>(
        Expression.Call(
            Expression.Constant(ids),
            typeof(ICollection<int>).GetMethod("Contains"), 
            Expression.Property(parameter, property)), 
        parameter);

    return entities.Where(expression);
}

When you try to keep your code DRY when working with your O/RM, you will often have to fiddle with expression trees. Here's another fun example.

edited May 23 '17 at 12:16

Community

1
1

answered Sep 27 '13 at 14:30

Steven

166,672
24
332
435

Fantastic. I was experimenting how to build the expression tree from http://blogs.msdn.com/b/miah/archive/2009/02/06/dynamic-expression-trees.aspx and http://stackoverflow.com/questions/820896/listobject-contains-expression-tree but couldn't figure out how to build the Collection/List contains. Thankyou! – David Sep 27 '13 at 14:38
4

@DavidLiddle: I'll let you in on a little secret: I simply write the LINQ query, compile and open up Reflector to see what the C# compiler generates. You can also see this info in the debugger, but Reflector is much easier. – Steven Sep 27 '13 at 14:41
Could you give an example of "simply write the LINQ query". Using ILSpy I just see the exact LINQ query I wrote! – David Sep 27 '13 at 14:55
@DavidLiddle: I'm not familiar with ILSpy, but with Reflector I can select the 'Optimization' for the code decompilation. I turned this option down from ".NET 4.0" to ".NET 2.0" to see how the expression was constructed (since Reflector is smart enough to reconstruct the LINQ query). ILSpy might have a similar feature. – Steven Sep 27 '13 at 15:40
How could you use a child property in the propertySelector i.e. x => x.Child.Id ? I tried creating a parameter of the child type and using this when calling Expression.Call but it was unable to evaluate the expression correctly. Ideally I just want the sql output as WHERE x.Child.Id in (1,2,3) – David Sep 30 '13 at 10:37
@DavidLiddle: Take a look at [this blog post](http://www.cuttingedge.it/blogs/steven/pivot/entry.php?id=37). I think that answers your question. – Steven Sep 30 '13 at 12:10
@Steven This is `WithId()` but how would I change it to `WithoutId()` / `NotWithId()`? I'm not sure where to place the negation operation? – h bob Sep 12 '16 at 10:21
1

@hbob wrap the expression in an `Expression.Not`. – Steven Sep 12 '16 at 10:25

How to use a Func in an expression with Linq to Entity Framework?

1 Answers1

Linked