Archive for the ‘LINQ’ Category
Select latest record from recordset using SQL and LINQ
Problem : How to select latest order from a list of order collection based on date using LINQ and SQL
SQL Table Order
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();