Master/details query with details in a list object-CodePudding

In my Sql database I have tables Orders and Details following this model, where Details.order is a foreign key to Orders.id:

public class Order {
  public int id { get; set;}
}

public class Detail {
  public int id { get; set;}
  public int order { get; set; }
  public int quantity { get; set; }
}

I am able to form a join query to return order-details in a flat list, for example:

from order in db.Orders
  join detail in db.Details on order.id equals detail.order
  select new { order = order.id, detail = detail }

The result is flat in that if there are 10 Details items, the result will have 10 rows.

How do I construct a Linq query to return the result similar to the class OrderDetail?

public class OrderDetail {
  public int orderId { get; set;}
  public List<Detail> details { get; set;}
}

where all the Details items of an Orders are in one list as a property of an OrderDetail object.

CodePudding user response：

Instead of creating an anonymous object, you should use your OrderDetails model for selecting records.

You can consider the following example:

var result = db.Orders
               .Select(i => new OrderDetails {
                 orderId = i.id,
                 details = i.details.ToList()  // this will be the ICollection of Details table
               }).ToList();

CodePudding user response：

You should do group join here. You can try the following code here:

  from order in db.Orders
  join detail in db.Details on order.id equals detail.order into OrderDetailsObj 
  from detail.DefaultIfEmpty()
  select new { orderId = order.id, 
                      details = from od in OrderDetailsObj 
                                     select od.detail
              }