Why Linq "where" expression after Select gets evaluated locally when created through a generic method?
c# entity-framework-core linq
Question
I'm implementing Specification pattern with generics and trying to dynamically apply criteria to projected simple (unmapped) versions of mapped entities. In general, it works fine, but Linq evaluates the expression locally as soon as I add Select and apply Where after it.
The exact same Linq expression yields correct SQL query, if I build it as a local variable and pass to the same Where.
Here's the simplified relevant code snippet:
public interface ISomeable
{
string Some { get; set; }
}
public static Expression<Func<T, bool>> GetCriteria<T>() where T : class, ISomeable
{ return e => (e.Some == "Hello"); }
...
Expression<Func<MySimpleEntity, bool>> someCriteria = e => (e.Some == "Hello");
Expression<Func<MySimpleEntity, bool>> someCriteria2 = GetCriteria<MySimpleEntity>();
var query = db.Entities
.Select(s => new MySimpleEntity { Id = s.Id, Some = s.Some });
// if this Select is removed and MySimpleEntity in both expressions replaced with MyFullEntity,
// the issue disappears
// this succeeds
var filteredQueryResults = query.Where(someCriteria).ToList();
// at this point, someCriteria2 is set to the same e => (e.Some == "Hello");
// this fails: why is it evaluated locally and not in SQL? <-----
filteredQueryResults = query.Where(someCriteria2).ToList();
// results in a warning:
/*
* 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning:
* The LINQ expression 'where (new MySimpleEntity() {Id = [s].Id, Some = [s].Some}.Some == "Hello")'
* could not be translated and will be evaluated locally.'.
*/
How do I make it generate correct SQL instead of local evaluation for someCriteria2?
I suspect I need some kind of casting, but not sure where. Both someCriteria and someCriteria2 look exactly the same in the debugger, so I have no idea why Linq is treating them differently.
I have created a minimal .Net Core Console app to reproduce the case. The full gist is here:
https://gist.github.com/progmars/eeec32a533dbd2e1f85e551db1bc53f8
NuGet dependencies: Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.6" Microsoft.Extensions.Logging" Version="2.2.0" Microsoft.Extensions.Logging.Console" Version="2.2.0"
Some explanations:
It is not related to the fact that the same query is executed twice. If I comment out the first query.Where(someCriteria).ToList() the second call with someCriteria2 still fails to generate valid SQL. However, if I replace someCriteria2 with someCriteria for the second query and let it run, I get two exact valid SQL queries in the console. So, it's all related to generics of someCriteria2 and Select projection - for some reason, Linq doesn't treat both variables the same, even if compiler (and debugger watch) thinks they are the same exact type.
Accepted Answer
The problem is similar to The LINQ expression could not be translated for base property and How to use inherited properties in EF Core expressions?, but in this case both the DeclaringType and ReflectedType of the MemberInfo point to ISomeable interface rather than the actual class.
Again this somehow is confusing EF Core in the Select scenario. I've checked the latest EF Core 3.0 preview and it also doesn't work. You might consider posting it to their issue tracker.
The only workaround I could offer so far is to postprocess the expression with custom ExpressionVisitor and bind the member accessors to the actual class. Something like this:
public static partial class ExpressionUtils
{
public static Expression<T> FixMemberAccess<T>(this Expression<T> source)
{
var body = new MemberAccessFixer().Visit(source.Body);
if (body == source.Body) return source;
return source.Update(body, source.Parameters);
}
class MemberAccessFixer : ExpressionVisitor
{
protected override Expression VisitMember(MemberExpression node)
{
if (node.Expression != null && node.Expression.Type != node.Member.DeclaringType)
{
var member = node.Expression.Type.GetMember(node.Member.Name).Single();
if (member.ReflectedType != member.DeclaringType)
member = member.DeclaringType.GetMember(member.Name).Single();
return Expression.MakeMemberAccess(node.Expression, member);
}
return base.VisitMember(node);
}
}
}
and now
var someCriteria2 = GetCriteria<MySimpleEntity>().FixMemberAccess();
will produce the exact expression as the working compile time someCriteria expression and no client evaluation.
Note: You still need the class constraint in order to avoid the casting issue from your previous question and to make this workaround work.
Popular Answer
I think the problem with your code is
GetCriteria<MySimpleEntity>();
linq cannot translate that directly to sql or doesn't have a direct translation. If you want to use it. Execute ToList() then add .Where(someCriteria2).ToList();. In watcher it sees/evaluate it as the same. But in the query itself, generating the sql doesn't seem to work that way.
I have also experienced that in my DateTime extension methods even in converting it to string in my Where i had to execute it outside of my linq query and add it
var dateUtc = DateTime.UtcNow.ExtensionMethod();
...Where(x => x.Date >= dateUtc)
or I execute FirstorDefault, First, ToList() first before my select and/or where
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow