Wednesday, April 13, 2016

Get latest order for each customer using LINQ to SQL

Given the task that my client requires me to retrieve all customers and their recent orders made for their report, the solution for this using LINQ is to join two tables namely Customers and Orders. Then the script retrieves the latest order code in the subquery with the condition that it should match with the customer id in the outer query. The subquery also applies arranging the orders made according to recent date and then select the latest using FirstOrDefault() which is the equivalent to Top 1.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
var results = (from order in _context.Orders
               join cus in _context.Customers
               on order.CustomerID equals cus.CustomerID
               where (order.OrderID == (
                  from subOrder in _context.Orders
                  where subOrder.CustomerID == order.CustomerID
                  orderby subOrder.OrderDate descending
                  select subOrder.OrderID).FirstOrDefault())
               select new
               {
                   ContactName = cus.ContactName,
                   OrderID = order.OrderID,
                   OrderDate = order.OrderDate
               }).OrderBy(e => e.ContactName).ToList();

0 comments:

Post a Comment