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