Search code examples
entity-frameworklinq-to-entitiesmax

LINQ match on latest date from 2 columns

Give this Linq query:

GetData.Where(p => p.LastActionA >= startDate && p.LastActionA <= endDate)

How could I change it so I can match on the highest value from 2 date columns?

ie something like GetData.Where(MAX(p.LastActionA, p.LastActionB) >= startDate

The closest equivalent SQL I can get uses a subquery, something like this:

SELECT ID,
  (SELECT MAX(v)  FROM (VALUES (LastActionA), (LastActionB)) AS value(v)) as MaxDate
FROM Data
Solution

  • For just 2 dates, you could use the C# conditional operator (works in both latest EF6 and EF Core):

    .Where(p => (p.LastActionA > p.LastActionB ? p.LastActionA : p.LastActionB) >= startDate)

    The following works for 2 and more dates, but only in EF6 (generates worse SQL). Actually it works in EF Core too, but causes client evaluation, which is bad:

    .Where(p => new [] { p.LastActionA, p.LastActionB }.Max() >= startDate)