How to Select Only the Records with the Highest Date in Linq

How to select only the records with the highest date in LINQ

If you just want the last date for each account, you'd use this:

var q = from n in table
        group n by n.AccountId into g
        select new {AccountId = g.Key, Date = g.Max(t=>t.Date)};

If you want the whole record:

var q = from n in table
        group n by n.AccountId into g
        select g.OrderByDescending(t=>t.Date).FirstOrDefault();

LINQ to select a column with max date from a group

var result = from d in data
    group d by d.Name into g
    select new { Name = g.Key, MaxDate = g.Max(s => s.StartTime) }

Or if you do not like query syntax then:

data.GroupBy(i => i.Name).Select(g => new
{
    Name = g.Key,
    MaxDate = g.Max(row => row.StartTime)
});

Linq Get Latest record for a set of Values by its latest date entered

If you want the latest for each record, you can use a GroupBy with a Select:

Context.Table
    .Where(x => x.Col1 == col1 && vals.Contains(x.Col2))
    .GroupBy(x => x.Col2)
    // order by descending on the group
    // and then take the first
    .Select(grp => grp.OrderByDescending(x => x.DateEntered).First())
    .ToList();

Get latest record and group with highest date - LINQ

class Program
{
    static void Main(string[] args)
    {
        List<Billing> Billings = new List<Billing>()
        {
            new Billing()
            {
                AccountID = 1234, DateOfBill = new DateTime(2017,07,12), Group = "A"

            },
            new Billing()
            {
                AccountID = 1234, DateOfBill = new DateTime(2017,07,16), Group = "B"

            },
            new Billing()
            {
                AccountID = 1234, DateOfBill = new DateTime(2017,07,31), Group = "C"

            },
            new Billing()
            {
                AccountID = 1235, DateOfBill = new DateTime(2017,07,31), Group = "A"

            },
            new Billing()
            {
                AccountID = 1236, DateOfBill = new DateTime(2017,07,31), Group = "B"

            }               
        };

        var LatestAccount = from n in Billings
                            where (n.Group == "A" || n.Group == "B" || n.Group == "C")
                            group n by new { n.AccountID } into g
                            select g.Where(d => d.DateOfBill == g.Max(m => m.DateOfBill)).Select(x => new { AccountId = g.Key.AccountID, Group = x.Group, DateOfBill = x.DateOfBill }).FirstOrDefault();

        foreach (var item in LatestAccount)
        {
            Console.WriteLine("AccountID: " + item.AccountId + "  Date of Bill: " + item.DateOfBill + "  Group: "+ item.Group);
        }
        Console.ReadLine();
    }
}
class  Billing
{
    public int AccountID { get; set; }
    public string Group { get; set; }
    public DateTime DateOfBill { get; set; }
}

Is below what you want?
If you show me the output you want, I can modify my answer.

max date record in LINQ

Starting from .NET 6 MaxBy LINQ method is available.

var result = items.MaxBy(i => i.Date);

---

Prior to .NET 6:

O(n log n):

var result = items.OrderByDescending(i => i.Date).First();

O(n) – but iterates over the sequence twice:

var max = items.Max(i => i.Date);
var result = items.First(i => i.Date == max);

Or you can use MoreLINQ which has MaxBy method which is O(n)

Get the row with the max(DateTime) LINQ

Sort by date and take the first

public async Task<Message> GetLastMessageByUnit(int unitId)
{
  return await context.Message
    .Where(x => x.Unit == unitId)
    .OrderByDescending(x => x.DateTime)
    .FirstOrDefaultAsync();
}  

..and if you really need it to be in a list, add it to one (but I'd change the return type of the method) because as Johnathan Barclay has pointed out in the comments, it doesn't make sense to name a method as something that returns one item, but have it return multiple

You can't use Max, because that will just return the highest datetime (and throw away the rest of the info) similar to how SELECT MAX(datetime) FROM t WHERE UnitId = 123 would. What you're looking for is the LINQ equivalent of SELECT TOP 1 * FROM t WHERE UnitID = 123 ORDER BY DateTime DESC.

The MoreLinq library has a MaxBy extension which can do a similar thing but you should be careful as to whether what it does can be translated to work in SQL.. If you're working with local data, for example, MaxBy may offer a useful performance boost because there's no point sorting a load of data only to take one item, when a "loop over it keeping track of the highest X and its associated Y you've seen" will suffice

Select the record with highest date and INCLUDE relevant tables with it - LINQ

I would suggest you use the following code:

_dbContext.EmployeeContracts
   .Where(x => x.SuperVisorId == 1)
   .GroupBy(x => x.EmployeeId)
   .Select(x=>x.OrderByDescending(y=>y.StartDate).FirstOrDefault())
   .Include(x => x.Employee)
   .Include(x => x.Department)
   .ToList();

The trick is the only retrieve the relevant record from the grouping. Then each record from each group is exactly the employeeContract you want to look at. Notice also that the .Include() statements are moved to the back. This is because the grouping changes the Queryable structure and removes the included navigation properties, so you'll have to Include once you have the structure you desire.

Select values with max date for each ID

If you want the entire row with the highest date for each Id, you can use the following code (written with LinqPad). If you just want the Id, you can use @BurnsBA's answer, as it will be slightly more efficient.

void Main()
{
    var data = new List<Record>
    {
        new Record(){Id=1, Value=1, Date=new DateTime(2017,1,1)},
        new Record(){Id=1, Value=2, Date=new DateTime(2017,2,1)},
        new Record(){Id=1, Value=3, Date=new DateTime(2017,3,1)},
        new Record(){Id=2, Value=5, Date=new DateTime(2017,1,1)},
        new Record(){Id=2, Value=6, Date=new DateTime(2017,2,1)},
    };

    var query = data.GroupBy(d => d.Id)
                    .SelectMany(g => g.OrderByDescending(d => d.Date)
                                      .Take(1));
    query.Dump();
}

public class Record
{
    public int Id { get; set; }
    public int Value { get; set; }
    public DateTime Date { get; set; }
}

Results:

First it groups by Id, then sorts the items within the group by Date in descending order, and returns the first one, SelectMany then flattens the list.

Linq get max date, return list of records with max date

You can just group by your criteria and then select the latest from each group.

Using query syntax:

var recordsInDb = (from record in context.records
                   where record.Year == year & record.Class == class_
                   group record by new { record.Year, record.Class, record.RangeMin, record.RangeMax } into rg
                   select (
                        from record in rg
                        orderby record.EffectiveDate
                        select record
                   ).Last()
                  )
                  .ToList();

I think it is a little easier to follow using Fluent/lambda syntax since the Last requires it anyway:

var ans = context.records.Where(r => r.Year == year && r.Class == class_)
                         .GroupBy(r => new { r.Year, r.Class, r.RangeMin, r.RangeMax })
                         .Select(rg => rg.OrderBy(r => r.EffectiveDate).Last())
                         .ToList();

How to select a record and only it's children with the highest date in LINQ to SQL

You could try this one:

In query syntax

var allPersons = from p in this.ObjectContext.Person
                 join f in this.ObjectContext.FriendVisit
                 on p.IdPerson equals f.IdPerson
                 group by new { p, f }
                 select new 
                 {
                     Name = p.Name;
                     Duration = f.OrderBy(x=>x.TimeOfVisit).Last().Duration
                 };

or in fluent syntax

var allPersons = this.ObjectContext.Person.Join(this.ObjectContext.FriendVisit,
                                                x=>x.IdPerson,
                                                y=>y.IdPerson
                                                (x,y)=> new { x, y }
                                         ).GroupBy(z=>z)
                                          .Select(x=> new 
                                           {
                                               Name = p.Name;
                                               Duration = f.OrderBy(y=>y.TimeOfVisit)
                                                           .Last()
                                                           .Duration
                                           });

---

Related Topics