Entity Framework Left Join on aggregate query

Question

I have an EF query that contains a number of aggregate sub queries (Count, Sum and Max). There are two problems.

1. I need the joins on the sub queries to be left joins so that records are returned even if there are no results in the aggregate sub queries. At present, records are only returned if all sub queries return records.

2. The resulting list of WholesaleCustomerAndAggregateOrders objects that are returned contain a Contact object that needs to also include Addresses and Counties. I added Include(c => c.Addresses.Select(a => a.Country)) to the query but the Contact objects don't contain any Address objects.

Any assistance with either issue would be appreciated. Full query below.

var month1Date = DateTime.Today.AddMonths(-1);
var month3Date = DateTime.Today.AddMonths(-3);
var month6Date = DateTime.Today.AddMonths(-6);
var month12Date = DateTime.Today.AddMonths(-12);

var db = GetNewContext();
var qry = from c in db.Contacts
                      .Include(c => c.Addresses.Select(a => a.Country))
          join orderCount in
              (
                  from o in db.WholesaleOrders
                  group o by o.ContactId into g
                  select new
                  {
                      ContactId = g.Key,
                      TotalOrders = g.Count()
                  }
              ) on c.Id equals orderCount.ContactId
          join month1Value in
              (
                  from o in db.WholesaleOrders
                  where o.OrderDate >= month1Date
                  group o by o.ContactId into g
                  select new
                  {
                      ContactId = g.Key,
                      TotalValue = g.Sum(r => r.LineItems.Sum(l => l.QuantityOrdered * l.Price))
                  }
              ) on c.Id equals month1Value.ContactId
          join month3Value in
              (
                  from o in db.WholesaleOrders
                  where o.OrderDate >= month3Date
                  group o by o.ContactId into g
                  select new
                  {
                      ContactId = g.Key,
                      TotalValue = g.Sum(r => r.LineItems.Sum(l => l.QuantityOrdered * l.Price))
                  }
              ) on c.Id equals month3Value.ContactId
          join month6Value in
              (
                  from o in db.WholesaleOrders
                  where o.OrderDate >= month6Date
                  group o by o.ContactId into g
                  select new
                  {
                      ContactId = g.Key,
                      TotalValue = g.Sum(r => r.LineItems.Sum(l => l.QuantityOrdered * l.Price))
                  }
              ) on c.Id equals month6Value.ContactId 
          join month12Value in
              (
                  from o in db.WholesaleOrders
                  where o.OrderDate >= month12Date
                  group o by o.ContactId into g
                  select new
                  {
                      ContactId = g.Key,
                      TotalValue = g.Sum(r => r.LineItems.Sum(l => l.QuantityOrdered * l.Price))
                  }
              ) on c.Id equals month12Value.ContactId
          join month12Quantity in
              (
                  from o in db.WholesaleOrders
                  where o.OrderDate >= month12Date
                  group o by o.ContactId into g
                  select new
                  {
                      ContactId = g.Key,
                      OrderCount = g.Count()
                  }
              ) on c.Id equals month12Quantity.ContactId
          join lastOrderDate in
              (
                  from o in db.WholesaleOrders
                  group o by o.ContactId into g
                  select new
                  {
                      ContactId = g.Key,
                      LastOrderDate = g.Max(r => r.OrderDate)
                  }
              ) on c.Id equals lastOrderDate.ContactId
          select new WholesaleCustomerAndAggregateOrders
          {
              Contact = c,
              TotalOrders = orderCount.TotalOrders,
              Month1Value = month1Value.TotalValue,
              Month3Value = month3Value.TotalValue,
              Month6Value = month6Value.TotalValue,
              Month12Value = month12Value.TotalValue,
              Month12OrderCount = month12Quantity.OrderCount,
              LastOrderDate = lastOrderDate.LastOrderDate
          };

return await qry.ToListAsync();

Answer 1

How about this:

db.WholesaleOrders
    .GroupBy(o => o.ContactId)
    .Select(a => new { 
        a.Key, 
        TotalOrders = a.Count(),
        LastOrderDate = a.Max(r => r.OrderDate),
        Month1Value = a.Where(b => b.OrderDate >= month1Date).Sum(r => r.LineItems.Sum(l => l.QuantityOrdered * l.Price),
        Month3Value = a.Where(b => b.OrderDate >= month3Date).Sum(r => r.LineItems.Sum(l => l.QuantityOrdered * l.Price),
        Month6Value = a.Where(b => b.OrderDate >= month6Date).Sum(r => r.LineItems.Sum(l => l.QuantityOrdered * l.Price),
        Month12Value = a.Where(b => b.OrderDate >= month12Date).Sum(r => r.LineItems.Sum(l => l.QuantityOrdered * l.Price)
        }).ToListAsync();

UPDATE: Add another property to projection:

Addresses = db.Addresses.Where(ad => ad.ContactId == a.Key);

where db is your context, and ad.ContactId is FK of the contact in Address table.

For this to work, multipleactiveresultsets property of the connection must be set to True.