in LINQ to SQL how would i create an outer left join to the first element of the right table?

Question

Hi, I have two tables Orders and Products where Orders has a ProductID as a forgin key, I would like to select all products, if a product has orders i would like to select the one with the highest distance field.

thanks,

Answer 1

Hi totem,

Your question seems a bit unclear. However, I am assuming that your orders table has a 'distance' column. You would like to select all products with the order that has the highest distance value.

var products = from p in db.Products
               select new
               {
                   ProductID = p.ProductID,
                   ProductName = p.ProductName,
                   HighestDistanceOrder = p.Orders.OrderByDescending(o => o.Distance).FirstOrDefault()
               };

If you wanted the value of highest distance and not the entire order, then

var products = from p in db.Products
               select new
               {
                   ProductID = p.ProductID,
                   ProductName = p.ProductName,
                   HighestDistance = p.Orders.Max(o => o.Distance)
               };

Hope that helps.

Matrich