Nested SQL in LINQ - Get Lastest Record For a Group

The following example shows how to create nested groups in a LINQ query expression and get the lastest record for a group.


public class Class1
{
    public int ID { get; set; }
    public string LastSeen { get; set; }
    public int Asset { get; set; }
}

List<Class1> list = new List<Class1>() 
{
    new Class1() { ID = 1, LastSeen = "2020-05-10", Asset = 1 },
    new Class1() { ID = 2, LastSeen = "2020-06-10", Asset = 1 },
    new Class1() { ID = 3, LastSeen = "2020-07-10", Asset = 2 },
    new Class1() { ID = 4, LastSeen = "2020-08-10", Asset = 2 }
};

1st way


var temp = from k in 
               (
                    from l in list 
                    group l by l.Asset into lg
                    orderby lg.Key
                    select new { Asset = lg.Key, LastSeen = lg.Max( x => x.LastSeen), lglist = lg }
               )
           select new { klist = k, ID = k.lglist.Where(x => x.LastSeen == k.LastSeen).Single().ID };
foreach (var group in temp)
{
    Console.WriteLine("Asset {0} {1} {2}:", group.klist.Asset, group.klist.LastSeen, group.ID);
}

2nd way


var temp = from l in list
           group l by l.Asset into lg
           orderby lg.Key
           select new { Asset = lg.Key, LastSeen = lg.Max(x => x.LastSeen), ID = lg.Where(x => x.LastSeen == lg.Max(y => y.LastSeen)).Single().ID };
foreach (var group in temp)
{
    Console.WriteLine("Asset {0} {1} {2}:", group.Asset, group.LastSeen, group.ID);
}


Complete code according to 1st way


using System;
using System.Linq;
using System.Collections.Generic;

					
public class Program
{
    public static void Main()
    {
        List<Class1> list = new List<Class1>()
        {
            new Class1() { ID = 1, LastSeen = "2020-05-10", Asset = 1 },
            new Class1() { ID = 2, LastSeen = "2020-06-10", Asset = 1 },
            new Class1() { ID = 3, LastSeen = "2020-07-10", Asset = 2 },
            new Class1() { ID = 4, LastSeen = "2020-08-10", Asset = 2 }
        };

        var temp = from k in
                        (
                            from l in list
                            group l by l.Asset into lg
                            orderby lg.Key
                            select new { Asset = lg.Key, LastSeen = lg.Max(x => x.LastSeen), lglist = lg }
                        )
                    select new { klist = k, ID = k.lglist.Where(x => x.LastSeen == k.LastSeen).Single().ID };

        foreach (var group in temp)
        {
            Console.WriteLine("Asset {0} {1} {2}:", group.klist.Asset, group.klist.LastSeen, group.ID);
        }
    }
}

public class Class1
{
    public int ID { get; set; }
    public string LastSeen { get; set; }
    public int Asset { get; set; }
}

Output :


nested_sql_in_linq_1.PNG