如何对0..n关系进行linq主明细查询？

Question

给定一个典型的DB结构的Orders有零个或多个OrderLines，而OrderLine只有一个Product，我该如何编写一个LINQ查询来表达这一点呢？

输出将是

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

我尝试了这个查询，但没有得到没有行的订单

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

Answer 1

Here is an article，它似乎解释了如何准确地实现您正在尝试做的事情。

public static void OuterJoinSimpleExample()
{
    var customers = new List<Customer>() { 
        new Customer {Key = 1, Name = "Gottshall" },
        new Customer {Key = 2, Name = "Valdes" },
        new Customer {Key = 3, Name = "Gauwain" },
        new Customer {Key = 4, Name = "Deane" },
        new Customer {Key = 5, Name = "Zeeman" } 
    };

    var orders = new List<Order>() {
        new Order {Key = 1, OrderNumber = "Order 1" },
        new Order {Key = 1, OrderNumber = "Order 2" },
        new Order {Key = 4, OrderNumber = "Order 3" },
        new Order {Key = 4, OrderNumber = "Order 4" },
        new Order {Key = 5, OrderNumber = "Order 5" },
    };

    var q = from c in customers
            join o in orders on c.Key equals o.Key into outer
            from o in outer.DefaultIfEmpty()
            select new { 
                Name = c.Name, 
                OrderNumber = ((o == null) ? "(no orders)" : o.OrderNumber) 
            };

    foreach (var i in q) {
        Console.WriteLine("Customer: {0}  Order Number: {1}", 
            i.Name.PadRight(11, ' '), i.OrderNumber);
    }

    Console.ReadLine();
}

Answer 2

这是工作查询(使用上面链接的示例构建)：

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