How do I get the most recent entry by condition in EF Core?

Question

I have a table with the following structure (and sample data):

Identifier	UseDate	PartId
a123	05/01/2000	237
a123	05/01/2000	4656
a123	01/01/2000	2134
a124	04/01/2000	5234
a124	01/01/2000	2890

I need to get the most recent entry of every (non-unique) identifier, but at most one per identifier.

The SQL-Query (MariaDB) that seems to fulfill my problem is the following:

SELECT a.Identifier, a.MaxDate, b.PartId, b.UseDate 
FROM
(SELECT Identifier, MAX(UseDate) AS MaxDate FROM MyTable GROUP BY Identifier) a
LEFT JOIN MyTable b ON a.Identifier = b.Identifier
WHERE a.MaxDate = b.UseDate GROUP BY a.Identifier;

However I need this to work with C# and EF Core (Pomelo.EntitiFrameworkCore.MySql 5.0.3), my attempts have been:

var q1 = db.MyTable
            .GroupBy(t => t.Identifier)
            .Select(t => new { Identifier = t.Key, MaxDate = t.Max(x => x.UseDate) });

return new ObjectResult(db.MyTable
    .Join(
        q1,
        t1 => t1.Identifier,
        t2 => t2.Identifier,
        (t1, t2) => new { Identifier = t2.Identifier, PartId = t1.PartId, MaxDate = t1.MaxDate, UseDate = t1.UseDate })
    .Where(t => t.UseDate == q1.First(x => x.Identifier == t.Identifier).MaxDate)
    .GroupBy(t => t.Identifier)
    .ToList()
);

and

return new ObjectResult(db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(t => t.OrderByDescending(x => x.UseDate).FirstOrDefault())
    .ToList()
);

The first one throws this error:

System.InvalidOperationException: "Unable to translate the given 'GroupBy' pattern. Call 'AsEnumerable' before 'GroupBy' to evaluate it client-side."

The second one essentially yields the same, just complaining about the LINQ expression instead of the GroupBy.

I want to avoid using raw SQL, but how do I correctly (and hopefully efficiently) implement this?

Answer 1

There are many ways to write such query in LINQ, with most of them being able to be translated by EF Core 5/6+.

The straightforward approach once you have defined a subquery for the necessary grouping and aggregates is to join it to the data table, but not with join operator - instead, use row limiting correlated subquery (SelectMany with Where and Take), e.g.

var query = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(t => new { Identifier = t.Key, MaxDate = t.Max(x => x.UseDate) })
    .SelectMany(g => db.MyTable
        .Where(t => t.Identifier == g.Identifier && t.UseDate == g.MaxDate)
        .Take(1));

If the ordering field is unique per each other key value (i.e. in your case if UseDate is unique per each unique Identifier value), you can use directly Join operator (since lo limiting is needed), e.g.

var query = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(t => new { Identifier = t.Key, MaxDate = t.Max(x => x.UseDate) });
    .Join(db.MyTable,
        g => new { g.Identifier, UseDate = g.MaxDate },
        t => new { t.Identifier, t.UseDate },
        (g, t) => t);

or directly apply Max based Where condition to the data table:

var query = db.MyTable
    .Where(t => t.UseDate == db.MyTable
        .Where(t2 => t2.Identifier == t.Identifier)
        .Max(t2 => t2.UseDate)
    );

Finally, the "standard" LINQ way of getting top 1 item per group.

For EF Core 6.0+:

var query = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(g => g
        .OrderByDescending(t => t.UseDate)
        .First());

For EF Core 5.0 the grouping result set inside the query must be emulated:

var query = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(g => db.MyTable
        .Where(t => t.Identifier == g.Key)
        .OrderByDescending(t => t.UseDate)
        .First());