How can I do this in LINQ?
select
*
from customer c
left join order o on o.CustomerID = c.CustomerID
where o.OrderDate = (select MAX(OrderDate) from order where CustomerID = o.CustomerID )
not worried about dups as there will always only be one order per day.
I got as far as the left join in LINQ, but not sure how or where to put the subquery in.
var query = from customer in clist
from order in olist
.Where(o => o.CustomerID == customer.CustomerID)
select new {
customer.CustomerID,
customer.Name,
customer.Address,
Product = order != null ? order.Product : string.Empty
};
FINAL SOLUTION:
var query = from customer in clist
from order in olist
.Where(o => o.CustomerID == customer.CustomerID && o.OrderDate ==
olist.Where(o1 => o1.CustomerID == customer.CustomerID).Max(o1 => o1.OrderDate)
)
select new {
customer.CustomerID,
customer.Name,
customer.Address,
order.Product,
order.OrderDate
};
Another solution without any lambdas
var query = from customer in clist
from order in olist
where order.CustomerID == customer.CustomerID && order.OrderDate ==
(from o in olist
where o.CustomerID == customer.CustomerID
select o.OrderDate).Max()
select new {
customer.CustomerID,
customer.Name,
customer.Address,
order.Product,
order.OrderDate
};
MaxBy(p => p. Age);
In a LINQ query expression, join operations are performed on object collections. Object collections cannot be "joined" in exactly the same way as two relational tables. In LINQ, explicit join clauses are only required when two source sequences are not tied by any relationship.
One commonly used feature of Language-Integrated Query (LINQ) is the facility to combine two sequences of related data using joins. The standard join operation provides an inner join but with a minor modification can be changed to give a left outer join.
This is more or less a literal translation
var query = from customer in clist
from order in olist
.Where(o => o.CustomerID == customer.CustomerID &&
o.OrderDate == olist
.Where(o => o.CustomerID == customer.CustomerID)
.Select(o => o.OrderDate).Max())
select new {
customer.CustomerID,
customer.Name,
customer.Address,
Product = order != null ? order.Product : string.Empty
};
but I would rewrite to
var query = from customer in clist
from order in olist
.Where(o => o.CustomerID == customer.CustomerID)
.OrderByDescending(o => o.OrderDate).Take(1)
select new {
customer.CustomerID,
customer.Name,
customer.Address,
Product = order != null ? order.Product : string.Empty
};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With