How to filter a query based on row number using LINQ with Entity Framework

Question

I have the following query in MySQL (version 8+)

SELECT MachineId, TotalProduction
FROM (
    SELECT MachineId,
           TotalProduction,
           ROW_NUMBER() OVER (PARTITION BY DATE(InsertedAt) ORDER BY InsertedAt DESC) AS rn
    FROM Machines
) AS a
WHERE rn = 1;

I want to translate this query to a LINQ query. I'm using Entity Framework Core 8.0.6.

I have already read this and this answers, but none of them are using the row number as a parameter to filter, only to select. How can I translate the SQL query to Entity Framework Core with LINQ, including filtering based on the row number?

Answer 1

You can select directly out of a grouped query using .First. This should in theory be translated to a ROW_NUMBER query.

var query =
    from m in db.Machines
    group by m.InsertedAt.Date into g
    select g.OrderByDescending(m2 => m2.InsertedAt).First();

Answer 2

var query = context.Machines
    .GroupBy(m => m.InsertedAt.Date)
    .Select(g => g.OrderByDescending(m => m.InsertedAt).FirstOrDefault())
    .Select(m => new 
    {
        m.MachineId,
        m.TotalProduction
    })
    .ToList();

Try this solution out