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

user_11614170 0
Viewed 116

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?

entity-framework-core sql c#
1 Answers

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());
user_52025630
Related