Select all customers and their last order Linq to SQL

Question

hei,

Using Linq to SQL (ala NorthWind database for example): How to select all customers and the latest order for each of them. The customers who have not placed order should be also in the result. The last order could be by ID (ID is incremental) or Timestamp (DateTime field).

similar to this http://stackoverflow.com/questions/331367/sql-statement-help-select-latest-order-for-each-customer but done in LINQ.

thanks

Answer 1

Assuming there's a foreign-key relationship between Customers & Orders, something like this sghould work:

from c in db.Customers
select new 
       { 
         Customer = c,
         LastOrder = c.Orders.OrderByDescending(o=>o.Timestamp).First();
       };