Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

linq

max

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
            };
like image 667
mfc Avatar asked May 08 '12 03:05

mfc


People also ask

How do you select the record with the highest date in LINQ?

MaxBy(p => p. Age);

Can we use join in LINQ C#?

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.

Is LINQ join inner or outer?

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.


1 Answers

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
            };
like image 111
Albin Sunnanbo Avatar answered Sep 21 '22 15:09

Albin Sunnanbo