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?

Mode

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. – Desiredesirea

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. – Mode

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 – Desiredesirea

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? – Mode

Answer 1

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.

Crystie

Fantastic. I was experimenting how to build the expression tree from blogs.msdn.com/b/miah/archive/2009/02/06/… and #821396 but couldn't figure out how to build the Collection/List contains. Thankyou! – Mode

@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. – Crystie

Could you give an example of "simply write the LINQ query". Using ILSpy I just see the exact LINQ query I wrote! – Mode

@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. – Crystie

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) – Mode

@DavidLiddle: Take a look at this blog post. I think that answers your question. – Crystie

@Crystie This is WithId() but how would I change it to WithoutId() / NotWithId()? I'm not sure where to place the negation operation? – Psia

@hbob wrap the expression in an Expression.Not. – Crystie