I have an enum describing a certain sorting order for a post:
enum PostOrder
{
TitleAsc,
TitleDesc,
ScoreAsc,
ScoreDesc,
}
and an extension method to reuse the ordering logic:
static class IQueryableExtensions
{
public static IOrderedQueryable<Post> OrderByCommon(this IQueryable<Post> queryable, PostOrder orderBy)
=> orderBy switch
{
PostOrder.TitleAsc => queryable.OrderBy(x => x.Title),
PostOrder.TitleDesc => queryable.OrderByDescending(x => x.Title),
PostOrder.ScoreAsc => queryable.OrderBy(x => x.Score).ThenBy(x => x.Title),
PostOrder.ScoreDesc => queryable.OrderByDescending(x => x.Score).ThenBy(x => x.Title),
_ => throw new NotSupportedException(),
};
}
The extension method works when used in a normal context but fails here:
var input = PostOrder.ScoreDesc;
var dbContext = new QuestionContext();
var users = dbContext.Users
.Select(x => new
{
User = x,
Top3Posts = x.Posts.AsQueryable()
.OrderByCommon(input)
.Take(3)
.ToList()
}).ToList();
with this error:
The LINQ expression 'MaterializeCollectionNavigation(
Navigation: User.Posts,
subquery: NavigationExpansionExpression
Source: DbSet<Post>()
.Where(p => EF.Property<Nullable<int>>(u, "Id") != null && object.Equals(
objA: (object)EF.Property<Nullable<int>>(u, "Id"),
objB: (object)EF.Property<Nullable<int>>(p, "AuthorId")))
PendingSelector: p => NavigationTreeExpression
Value: EntityReference: Post
Expression: p
.Where(i => EF.Property<Nullable<int>>(NavigationTreeExpression
Value: EntityReference: User
Expression: u, "Id") != null && object.Equals(
objA: (object)EF.Property<Nullable<int>>(NavigationTreeExpression
Value: EntityReference: User
Expression: u, "Id"),
objB: (object)EF.Property<Nullable<int>>(i, "AuthorId")))
.AsQueryable()
.OrderByCommon(__input_0)
.Take(3)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
probably because it's being used in an Expression<> context.
How can I make it work there?
A reproducible project can be found in this repository.
This is well known problem with no general solution.
The general problem with expression tree translation is that it is based purely on knowledge - no methods are actually called, the known methods are identified by signature and translated according to their known semantic. That's why custom methods/properties/delegates cannot be translated.
The problem is usually solved by using some expression manipulation library. During my work with EF6/EF Core I've tried many - LinqKit, NeinLinq, AutoMapper, recently DelegateDecompiler. All they allow replacing (expanding) parts of the expression tree with their corresponding raw expressions like if you have written them manually.
The problem in this particular case is more complicate because in order to be translated, the custom method must actually be invoked. But how? Especially, what would be the IQueryble argument? Note that here
x.Posts.AsQueryable()
you don't have x instance, hence no Posts collection instance to call AsQueryable() and pass it to the custom method.
One possible solution is to call the method passing fake LINQ to Objects IQueryable, then finding and replacing it in the resulting query expression tree with the actual expression.
Following is the implementation of the above idea:
partial class IQueryableExtensions
{
public static IQueryable<T> Transform<T>(this IQueryable<T> source)
{
var expression = new QueryableMethodTransformer().Visit(source.Expression);
return expression == source.Expression ? source : source.Provider.CreateQuery<T>(expression);
}
class QueryableMethodTransformer : ExpressionVisitor
{
protected override Expression VisitMethodCall(MethodCallExpression node)
{
if (node.Method.DeclaringType == typeof(IQueryableExtensions) &&
node.Method.IsStatic &&
typeof(IQueryable).IsAssignableFrom(node.Method.ReturnType) &&
node.Arguments.Count > 1 &&
node.Arguments[0].Type.IsGenericType &&
node.Arguments[0].Type.GetGenericTypeDefinition() == typeof(IQueryable<>))
{
// Extract arguments
var args = new object[node.Arguments.Count];
int index = 1;
while (index < args.Length && TryExtractValue(Visit(node.Arguments[index]), out args[index]))
index++;
if (index == args.Length)
{
var source = node.Arguments[0];
var elementType = source.Type.GetGenericArguments()[0];
// Create fake queryable instance
var fakeSource = args[0] = EmptyQueryableMethod
.MakeGenericMethod(elementType)
.Invoke(null, null);
// Invoke the method with it
var result = (IQueryable)node.Method.Invoke(null, args);
// Replace it with the actual queryable expression
return new ConstValueReplacer
{
From = fakeSource,
To = source
}.Visit(result.Expression);
}
}
return base.VisitMethodCall(node);
}
static IQueryable<T> EmptyQueryable<T>() => Enumerable.Empty<T>().AsQueryable();
static readonly MethodInfo EmptyQueryableMethod = typeof(QueryableMethodTransformer)
.GetMethod(nameof(EmptyQueryable), BindingFlags.NonPublic | BindingFlags.Static);
static bool TryExtractValue(Expression source, out object value)
{
if (source is ConstantExpression constExpr)
{
value = constExpr.Value;
return true;
}
if (source is MemberExpression memberExpr && TryExtractValue(memberExpr.Expression, out var instance))
{
value = memberExpr.Member is FieldInfo field ? field.GetValue(instance) :
((PropertyInfo)memberExpr.Member).GetValue(instance);
return true;
}
value = null;
return source == null;
}
}
class ConstValueReplacer : ExpressionVisitor
{
public object From;
public Expression To;
protected override Expression VisitConstant(ConstantExpression node) =>
node.Value == From ? To : base.VisitConstant(node);
}
}
As one can see, it is not very generic because it has a lot of assumptions - finds a static method taking first IQueryable<T> argument and other arguments being evaluatable (constant values or field/properties of constant values, which is the case with closures) and performs the aforementioned action.
But it solves the particular issue. All you need is to call Transform at the end of you query (before materialization):
var users = dbContext.Users
.Select(x => new
{
User = x,
Top3Posts = x.Posts.AsQueryable()
.OrderByCommon(input)
.Take(3)
.ToList()
})
.Transform() // <--
.ToList();
Now, it's possible to avoid the need of Transform call by plugging the QueryableMethodTransformer into EF Core query translation pipeline, but it requires a lot of plumbing code just to call a single method. Note that it has to be plugged into query pretranslator, since IMethodCallTranslator cannot process IQueryable (and in general IEnumerable) arguments. If you are interested, my answer to EF Core queries all columns in SQL when mapping to object in Select shows how you could plug DelegateDecompiler into EF Core, the same code can literally be used to plug aby other (including the one presented here) custom expression visitor based preprocessor.