LINQ Expression to get member value and can be translated to SQL?

Question

I have a complex query where certain DateTime members of a model entity are being used multiple times, but not always the same members. I'd like to know if I can put the "getting" of the member in an Expression, so I don't have to repeat that piece of code.

A simplified version of my current code looks like this:

var getTargetDate = ((Expression<Func<Order, DateTime>>)(o => o.OrderDate)).Compile();
if (delivery)
    getTargetDate = ((Expression<Func<Order, DateTime>>)(o => o.DeliveryDate)).Compile();

return orders.Where(o => getTargetDate(o) >= fromDate && getTargetDate(o) < toDate);

This code compiles, but gives me the runtime Exception:

Exception thrown: 'System.NotSupportedException' in System.Data.Linq.dll

Additional information: Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

Is there a way to put the "getting" of the DateTime in a variable or method that can be translated to SQL?

Not out of the box. The simplest is to use some expression composition helper library like [LINQKit](https://github.com/scottksmith95/LINQKit). See https://stackoverflow.com/questions/29348825/how-to-move-parts-of-a-linq-query-to-an-reusable-linq-expression-tree — Ivan Stoev, Aug 16 '17 at 10:55
Interesting. Does that mean that the Microsoft canonical route to solve this issue is simply duplicate the code, or use something else entirely? I find it annoying that such an issue would not arise if I were to use, say, plain text SQL queries. — Protector one, Aug 16 '17 at 10:59
Unfortunately yes. Query translators do not understand custom methods and also cannot translate code invocation expressions (what basically the delegate is after you compile your expression). And there is no syntax to embed expressions inside expression trees. — Ivan Stoev, Aug 16 '17 at 11:05

score 1 · Answer 1 · answered Aug 16 '17 at 12:18

This does not literally answer the question, but it does provide a way around the code duplication posed in it.
Put the desired member and the entity itself in an anonymous object, perform the relevant part of the original query on the member, then select out the original entity:

orders = orders.Select(o => new { o,
    TargetDate = delivery ? o.DeliveryDate : o.OrderDate
})
.Where(o => o.TargetDate >= fromDate && o.TargetDate < toDate)
.Select(o => o.o);

LINQ Expression to get member value and can be translated to SQL?

1 Answers1