Justin Mathew Blog

My Innovations in .Net

Archive for the ‘LINQ’ Category

Select latest record from recordset using SQL and LINQ

leave a comment »


Problem : How to select latest order from a list of order collection based on date using LINQ and SQL

SQL Table Order

null

SQL Query

select T1.id, T1.quantity from [order] T1
inner join (select ID, Max(created) as latest from [order] group by ID) T2
ON (T1.ID = T2.ID and T1.created = T2.latest)

LINQ

class Order
{
public int ID { get; set; }
public int Quantity { get; set; }
public string Created { get; set; }
}

List<Order> Orders = new List<Order>();
Orders.Add(new Order() { ID = 1, Quantity = 10, Created = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") });
Thread.Sleep(1);
Orders.Add(new Order() { ID = 1, Quantity = 10, Created = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") });
Thread.Sleep(1);
Orders.Add(new Order() { ID = 2, Quantity = 12, Created = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") });
Thread.Sleep(1);
Orders.Add(new Order() { ID = 1, Quantity = 15, Created = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") });

List FinalOrders = (from a in Orders group a by a.ID into latest
join b in Orders on new { ID = latest.Key, dt = latest.Max(itm => itm.Created) } equals new { ID = b.ID, dt = b.Created }
select new Order {ID = b.ID, Quantity = b.Quantity}).ToList();

Written by Justin

January 21, 2014 at 11:51 pm

Posted in LINQ, Sql Server