EF LINQ query master-details into new model

Question

There are lots of EF master-detail questions, I've looked through most of them. I can't find any that address the syntax approach I'm using which include transformation of the EF query results into a new business model, but I'm sure there is a way.

Here is the business model to emit from the DAL (header properties not shown)

  public class Order : OrderHeader, IOrder
  {
        public virtual List<OrderDetail> OrderDetails { get; set; } = new List<OrderDetail>();
  }

Here is the query reduced to show the problem I'm facing which is loading the collection of OrderDetails at the same time of pulling the orders. I could run another query after loading the headers but that sounds very slow and clunky.

public async Task<List<Business.Models.Order>> GetSimplifyOrders()
    {
        var query = (from n in _context.SalesOrderHeaders
                join c in _context.Customers on n.CustomerId equals c.CustomerId
                join d in _context.SalesOrderDetails on n.SalesOrderId equals d.SalesOrderId
                     select new Business.Models.Order
                     {
                         SalesOrderNumber = n.SalesOrderNumber,
                         Customer = new Business.Models.Customer() 
                                { CompanyName = c.CompanyName, 
                                    FullName = $"{c.FirstName} {c.LastName}",
                                    EmailAddress = c.EmailAddress},

                         **OrderDetails = ??**

                     }).AsNoTracking()
                       .ToList<Business.Models.Order>();
        return query;
    }

Can someone help me with the specific syntax to load the details inline as shown above?

Answer 1

Rather than doing a join at the top level, you can use another internal LINQ query to populate a collection inside of your model.

from n in _context.SalesOrderHeaders
join c in _context.Customers on n.CustomerId equals c.CustomerId
select new Business.Models.Order
{
    SalesOrderNumber = n.SalesOrderNumber,
    Customer = new Business.Models.Customer()
    {
        CompanyName = c.CompanyName,
        FullName = $"{c.FirstName} {c.LastName}",
        EmailAddress = c.EmailAddress
    },

    OrderDetails = 
        (from d in _context.SalesOrderDetails
        where n.SalesOrderId == d.SalesOrderId
        select new Business.Models.OrderDetail()
        {
            ...
        })
        .ToList()
}