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();
|
Comments
Post a Comment