LINQ Breaking changes in EF Core 3.0. Using expressions

Question

In my app I have some queries that use the same repeated logic:

var someThings = context.table1
  .where(SomeLogic)
  .ToList();

With EF Core 2.1 I could encapsulate this logic in a layer with all these expressions:

public static Expression<Func<MyObject, bool>> SomeLogic =>
            myObject => myObject.CreationDate.Date == DateTime.Now.Date
                    && (myObject.Whatever.HasValue || myObject.MoreWhatever);

Now I discovered this was being evaluated in memory, and that's bad.

If I do something like:

var someThings = context.table1
  .where(myObject => 
      myObject.CreationDate.Date == DateTime.Now.Date
      && (myObject.Whatever.HasValue || myObject.MoreWhatever))
  .ToList();

then the query is evaluated in the DB, but I am putting some logic in the wrong layer.

I tried to subsitute Expression with a function or any other tool, but I don't find a way to do it.

Is there a way to encapsulate the logic of a query in a layer as I was doing before, but preserving EF rules so that this query can still be evaluated in the DB?

Thanks.

Maybe pass an `IQueryable` to `SomeLogic` and return filtered `IQueryable
`? — Mat J, Nov 26 '19 at 11:17
The sample as is currently works in 3.0. Probably the real use case is different. — Ivan Stoev, Nov 26 '19 at 14:18
You aren't putting logic in the wrong layer. You are putting it where it should have been from the start. Why would a *query filter* be put somewhere else? It's not *logic*, it's a condition. Without LINQ, you'd put that in the SQL statement itself. Instead of encapsulating the "logic" you were pulling it into a higher layer, essentially putting data layer concerns into a higher layer — Panagiotis Kanavos, Nov 26 '19 at 16:39
You're doing it right in the first sample provided. Expressions are the way to go. Store them anywhere you want, reuse as much as you want. However, I am not sure `DateTime.Now.Date` is translatable to SQL out of the box, probably this was client evaluated in 2.1 which is no longer allowed. Rewriting the query is another topic. — joakimriedel, Dec 12 '19 at 10:13

Jan · Accepted Answer · 2019-11-26T14:06:08.397

2

Why you need a "real" expression and not just a Lambda is explained in this answer. The created Expression can be created anywhere and passed as a parameter to the function that executes the query.

This answer should guide the way you need to go. You only have to replace the two dummy expressions with the whatever.hasvalue...stuff

var param = Expression.Parameter(typeof(MyObject), nameof(MyObject));

//    myObject.CreationDate.Date == DateTime.Now.Date
Expression dateExpression = Expression.Equal(Expression.Constant(DateTime.Now),
    Expression.PropertyOrField(param, "CreationDate"));


var dummyExpression1 = Expression.Equal(Expression.Constant(1), Expression.Constant(1));
var dummyExpression2 = Expression.Equal(Expression.Constant(1), Expression.Constant(1));

//    && (myObject.Whatever.HasValue || myObject.MoreWhatever)
Expression orExpression = Expression.Or(dummyExpression1, dummyExpression2);


Expression allConditions = Expression.And(dateExpression, orExpression);

//myObject =>
Expression myExpression = Expression.Lambda<Func<MyObject, bool>>(allConditions, param);

var someThings = context.table1
    .where(myExpression)
    .ToList();

I had the most trouble with Expression.PropertyOrField. If you have nested structures you need to loop through the data structure and call Expression.PropertyOrField with the first parameter being the result from the previous call to Expression.PropertyOrField.

edited Nov 26 '19 at 14:06

answered Nov 26 '19 at 13:18

Jan

3,825
3
31
51

The amount of code here is a very, very strong indicator that this is the wrong way. – Panagiotis Kanavos Nov 26 '19 at 16:42
Hi Panagiotis, i use comparable code in a expression builder class with a datagrid with dynamic columns. The where condition is defined by user interactions. Of course if you only use it as a one of, this is a bit of an overkill. – Jan Nov 26 '19 at 23:22
@PanagiotisKanavos: In my case I have 7 different `SomeLogic`s and some of them are used in up to 10 different queries. It's good to have these conditions centralized somewhere. If at some point I want to change one `SomeLogic`, it makes no sense to go all around the 10 queries to change it. How would you do it in that case? @Jan: I didn't have time yet to try this solution. As Panagiotis mentioned, I see too much code for a solution I think it should be simpler, but if I don't see any simpler way, I'll try this one and I'll give my feedback. Thank you both for your answers! – xavier Nov 27 '19 at 14:18
This is a valid way to construct an Expression, but unnecessarily convoluted since you can easily construct it the way @xavier writes in the question. – joakimriedel Dec 12 '19 at 10:16

LINQ Breaking changes in EF Core 3.0. Using expressions

1 Answers1