LINQ to Entities does not support extension method?

Question

I have two extension methods like this

  public static IQueryable<T> CurrentVersion(this IQueryable<T> queryable, DateTime date)
  {
        return queryable.Where(p => p.CreationDate>date);
  }

  public static IEnumerable<T> CurrentVersion(this IEnumerable<T> queryable, DateTime date)
  {
        return queryable.Where(p => p.CreationDate>date);
  }

My model is

public class Group {
    ..
    ICollection<GroupMembers> GroupMembers { get; set; }
}

When I use from extension method in this query everything is ok

 var q =  Db.Groups.CurrentVersion();
 var result = q.ToList();

But when I use it in flowing query I get an error

 var q = Db.Groups.SelectMany(p => p.GroupMembers.AsQueryable().CurrentVersion(date));

OR

 var q = Db.Groups.SelectMany(p => p.GroupMembers.AsEnumerable().CurrentVersion(date));

 var result = q.ToList();    // Here I get error

Error:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[..](System.Linq.IQueryable1[..., System.DateTime)' method, and this method cannot be translated into a store expression.

Now I have two questions:

1. I googled this error and found many problems same as my question in stackoverflow. All answers were "Linq to Entities can't convert this extension method to a SQL query". Now I would be grateful if somebody help me to know, why does my first query not raise any errors?

2. How can I change my extension method which can be recognized by Linq-to-Entities?

Answer 1

Linq query needs to be translated to sql. When you have your CurrentVersion extension called inline like this:

Db.Groups.CurrentVersion();

then EF just invokes CurrentVersion method, gets resulting IQueryable object and transforms in to query. On the other hand in case of that query:

var q = Db.Groups.SelectMany(p => p.GroupMembers.AsQueryable().CurrentVersion(date));

The inner expression in SelectMany may not be ever invoked in code! It is meant to be translated to sql. So it is treated as Expression object and it is then parsed, but in your case it contains Invoke Expression as you are invoking method but this cannot be translated to sql from obvious reason. So from within SelectMany lambda parameer you cannot invoke any method, you have to provide there proper Expression. The most valuable thing provided by CurrentVersion method is filtering expression. Change your method like this:

public static Expression<T, bool> CurrentVersion( DateTime date)
{
    return p => p.CreationDate > date;
}

An use it like this:

var q =  Db.Groups.Where(ExpressionsHelper.CurrentVersion(date));
...
Expression<T, bool> filterExpression = ExpressionsHelper.CurrentVersion(date);
Db.Groups.SelectMany(p => p.GroupMembers.AsQueryable().Where(filterExpression));

If you whish you may stil have your extension method sharing filtering logic with the new method:

public static IQueryable<T> CurrentVersion(this IQueryable<T> queryable, DateTime date)
{
    return queryable.Where(ExpressionsHelper.CurrentVersion(date));
}