How to make linq master detail query for 0..n relationship?

Question

Given a classic DB structure of Orders has zero or more OrderLines and OrderLine has exactly one Product, how do I write a linq query to express this?

The output would be

OrderNumber - OrderLine - Product Name
Order-1       null        null // (this order has no lines)
Order-2       1           Red widget

I tried this query but is not getting the orders with no lines

var model = (from po in Orders
             from line in po.OrderLines
                select new
                {
                    OrderNumber = po.Id,
                    OrderLine = line.LineNumber,
                    ProductName = line.Product.ProductDescription,
                }
             )

I think that the 2nd from is limiting the query to only those that have OrderLines, but I dont know another way to express it. LINQ is very non-obvious if you ask me!

Answer 1

Here is an article which appears to explain how to achieve exactly what you are trying to do.

Answer 2

This is the working query (built using the example linked above):

var model = (from po in orders
             join line in orderLines // note this is another var, it isn't po.Lines
             on po.Id equals line.OrderId into g
             from line in g.DefaultIfEmpty()
            select new
            {
                OrderNumber = po.Id,
                OrderLine = line == null ? 0 : line.LineNumber,
                ProductName = line == null ? string.Empty : line.Product.ProductDescription,
            }
         )

Answer 3

var model =
  from po in Orders 
  from line in po.OrderLines.DefaultIfEmpty()
  select new
  { 
    OrderNumber = po.Id, 
    OrderLine = line != null ?
      (int?)line.LineNumber : null, 
    ProductName = line != null ?
       line.Product.ProductDescription : null 
  } ;