LINQ; how to get record with max date with join?

linq, max

Solution

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
            };

Problem

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 }; ```

Original source