Implementing LINQ to Entity on many to one and one to many relationship

Question

I'm tring to implement LINQ to Entity using Include extension.

I have the following scheme:

Here is my first LINQ (it works properly):

   var query = ctx1.Order_Details.Include("Order").Select( o => new  
             { o.OrderID,
               o.ProductID,
               o.Order.OrderDate,
               o.Order.OrderNumber
              });

Here is my second LINQ(doesn't work):

My question is why when I'm implementing Linq on "many to one" relationship LINQ works properly and when I'm trying to implement LINQ "inside out" (i.e. one to many) it doesn't work?

Answer 1

The first query works because there is just one related order for each order detail - you are looking at the relationship from the "n" perspective (the order details).

In the second query you are starting with the "1" in the 1:n relationship between Order and Order_Detail and you are trying to get one unit price - but there is a collection (the "n") of related united prices and quantities. Your current approach would only work if there is just one related Order_Detail for each Order.

If you wanted to grab the related unit price and quantities collections in your anonymous type you could do something like this:

 var query2 = ctx1.Order.Include("Order_Details").Select( o => new  
             { o.OrderID,
               o.CustomerID,
               UnitPrices = o.Order_Details.Select( od => od.UnitPrice),
               Quantities = o.Order_Details.Select( od => od.Quantity)
              });