CODEWITHSUNDEEP
entity-frameworklinq-to-entitiesmax
markpsmith
markpsmith

Reputation: 4918

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

Upvotes: 1

Views: 712

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

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)

Upvotes: 2

Related Questions