How to select records of user's history of items, where the date is latest for the items in LINQ to entities lambda?

Question

Imagine table History:

public class History
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public int ItemId { get; set; }
    public DateTime Date { get; set; }
}

The data looks like this:

1|10|4|2009-01-20 
2|11|2|2009-01-22
3|10|4|2009-01-28 // same userId and itemId as id=1, but newer date
4|11|2|2009-01-26 // same userId and itemId as id=2, but newer date
5|10|8|2009-01-14 // is already the latest date, no duplicate userId, and ItemId combination
6|12|9|2009-01-11 // is already the latest date, no duplicate userId, and ItemId combination

should become:

3|10|4|2009-01-28
4|11|2|2009-01-26
5|10|8|2009-01-14
6|12|9|2009-01-11

How can I do this using Linq lambda to objects expressions?

It is similar to this question, but you also need to check where UserId and ItemId are the same.

Answer 1

Try following :

            DataTable dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("UserId", typeof(int));
            dt.Columns.Add("ItemId", typeof(int));
            dt.Columns.Add("DAte", typeof(DateTime));

            dt.Rows.Add(new object[] { 1,10,4, DateTime.Parse("2009-01-20")}); 
            dt.Rows.Add(new object[] { 2,11,2, DateTime.Parse("2009-01-22")}); 
            dt.Rows.Add(new object[] { 3,10,4, DateTime.Parse("2009-01-28")}); 
            dt.Rows.Add(new object[] { 4,11,2, DateTime.Parse("2009-01-26")}); 
            dt.Rows.Add(new object[] { 5,10,8, DateTime.Parse("2009-01-14")}); 
            dt.Rows.Add(new object[] { 6,12,9, DateTime.Parse("2009-01-11")});

            DataTable dt2 = dt.AsEnumerable()
                .OrderByDescending(x => x.Field<DateTime>("Date"))
                .GroupBy(x => new { userId = x.Field<int>("UserId"), itemId = x.Field<int>("ItemId") })
                .Select(x => x.First())
                .CopyToDataTable();

Answer 2

Try this with Linq:

 var grouped = history.GroupBy(h => new {
      h.UserId,
      h.ItemId
 });

then

var results = grouped.Select(h => new {
      Id = h.OrderByDescending(x => x.Date).First().Id;
      UserId = h.Key.UserId,
      ItemId = h.Key.ItemId,
      Date = h.OrderByDescending(x => x.Date).First().Date;
}).ToList();