This is because the call to ForUser() is made inside the expression tree created by the C # compiler when it sees the lambda that you pass to Select. Entity Framework tries to figure out how to convert this function to SQL, but cannot call the function for several reasons (for example, d.Protocols does not exist at the moment).
The simplest approach that works for such a case is for your helper to return the lambda criterion expression and then pass it to the .Where() method:
public static Expression<Func<Protocol, true>> ProtocolIsForUser(int userId) { return p => p.UserProtocols.Any(u => u.UserId == userId); }
...
var protocolCriteria = Helpers.ProtocolIsForUser(userId); var data = context.Programs .Select(d => new MyDataDto { ProgramId = d.ProgramId, ProgramName = d.ProgramName, ClientId = d.ClientId, Protocols = d.Protocols.Count(protocolCriteria) }) .ToList();
Additional Information
When you call the LINQ method outside the expression tree (for example, using context.Programs.Select(...) ), the Queryable.Select() extension method is actually called, and its implementation returns IQueryable<> , which represents the extension method called to the original IQueryable<> . This is where the implementation of Select is executed, for example:
public static IQueryable<TResult> Select<TSource,TResult>(this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector) { if (source == null) throw Error.ArgumentNull("source"); if (selector == null) throw Error.ArgumentNull("selector"); return source.Provider.CreateQuery<TResult>( Expression.Call( null, GetMethodInfo(Queryable.Select, source, selector), new Expression[] { source.Expression, Expression.Quote(selector) } )); }
When the requested provider needs to generate actual data from IQueryable<> , it parses the expression tree and tries to figure out how to interpret these method calls. Entity Framework has built-in knowledge of many LINQ-related functions , such as .Where() and .Select() , so it knows how to translate these method calls into SQL. However, he does not know what to do for the methods you write.
So why does it work?
var data = context.Programs.ForUser(userId);
The answer is that your ForUser method ForUser not implemented as the Select method described above: you do not add an expression to the request to represent the ForUser call. Instead, you return the result of a .Where() call. From the point of view of IQueryable<> it is as if Where() called directly, and the call to ForUser() never occurred.
You can prove this by capturing the Expression property on IQueryable<> :
Console.WriteLine(data.Expression.ToString());
... that will produce something like this:
Programs.Where(u => (u.UserId == value(Helpers<>c__DisplayClass1_0).userId))
There is no ForUser() call in this expression.
On the other hand, if you include the ForUser() call inside the expression tree as follows:
var data = context.Programs.Select(d => d.Protocols.ForUser(id));
... then the .ForUser() method is never called, so it never returns an IQueryable<> , which knows that the .Where() method received the call. Instead, the .ForUser() call is displayed in the expression tree for the request. The output of the expression tree will look something like this:
Programs.Select(d => d.Protocols.ForUser(value(Repository<>c__DisplayClass1_0).userId))
Entity Framework does not know what ForUser() . As much as possible, you could write ForUser() to do something that cannot be done in SQL. Therefore, it informs you that it is not supported.