Considering a table like this

How do you write a linq query to get this result? (in one query of course, and without any loop)

Or to specify it clearly, the closest date by Id

It's easy to write this in SQL but I can't find a satisfying way to do it in LINQ

CodePudding user response：

var result = table
    .GroupBy(t => t.Id)
    .Select(t => new { Id = t.Key, Date = t.Max(o => o.Date)})
    .ToList();

CodePudding user response：

This is the ways that I know, if you need all fields from table, not just Date.

EF 6 and EF Core 6

var query = 
    from c in db.Some
    group c by new { c.Id } into g
    select g.OrderByDescending(c => c.Date).First();

Other versions and probaly other providers

// make common variable because it may be query with filter
var data = db.Some;

var query = 
    from d in data.Select(d => new { d.Id }).Distinct()
    from c in data
        .Where(c => c.Id == d.Id)
        .OrderByDescending(c => c.Date)
        .Take(1)
    select c;

Fastest variant via linq2db

var query = 
    from c in db.Some
    select new 
    {
        c,
        RN = Sql.Ext.RowNumber().Over()
            .PartitionBy(c.Id)
            .OrderByDesc(c.Date)
            .ToValue()
    } into s
    where s.RN == 1
    select s.c;