Linq group outer joins projected on a viewmodel

Question

I have been scouting the internet for the past 2 days to find a solution to grouping the below linq query on BookId to no avail. The query works but I want to reoganise it so that It can group on BookId or BookTitle.

models

Book(BookId, Title, Author, ISBN, Location,  BookTypeId, StockLogValue) 
Booktype(BookTypeId, BookTypeName)
Stock(StockId, bookId, quantity, date_added)
Transact (transactionId, TransactionTypeId, BookId, Quantity, TransactDate)
TransactionType( TransactionTypeId, TransactionTypeName)

Controller

public ActionResult Report(int? year, int? month, int? BkId)
    {
        var query = ReportYrMn( year, month, BkId);
        return View(query);
    }
     public IEnumerable ReportYrMn(int? year, int? month, int? BkId)
    {
        var query =
            (from bk in db.Books
             join tr in db.Transacts.Where(a => a.TransactDate.Month == month && a.TransactDate.Year == year && a.TransactType.TransactTypeName == "sale") on bk.BookId equals tr.BookId into trs
             from x in trs.DefaultIfEmpty()
             join tr2 in db.Transacts.Where(a => a.TransactDate.Month == month && a.TransactDate.Year == year && a.TransactType.TransactTypeName == "gift") on bk.BookId equals tr2.BookId into trs2
             from x2 in trs2.DefaultIfEmpty()
             select new ReportViewModel { BookTitle = bk.BookTitle ,BookId = bk.BookId, StockLogValue=bksty.StockLogValue, SaleTotal = trs.Sum(c => c.TransactQty), GiftTotal = trs2.Sum(c => c.TransactQty), SalesCount = trs.Count(), GiftCount = trs2.Count() });
        return query.AsEnumerable(); 
    }

Thanks for any help

Answer 1

The immediate solution to your problem is to remove the from a in b.DefaultIfEmpty() lines. A join - into is the same as GroupJoin, which creates collections related to the left item, i.e. collections Transacs belonging to a book. That's exactly what you want here.

A subsequent from is equivalent to a SelectMany, which flattens these collections again, leaving you with a flat list of book-transact rows.

So this will do what you want:

var query =
    (from bk in db.Books
     join tr in db.Transacts
                  .Where(a => a.TransactDate.Month == month && a.TransactDate.Year == year && a.TransactType.TransactTypeName == "sale")
        on bk.BookId equals tr.BookId into trs
     join tr2 in db.Transacts
                   .Where(a => a.TransactDate.Month == month && a.TransactDate.Year == year && a.TransactType.TransactTypeName == "gift")
        on bk.BookId equals tr2.BookId into trs2
     select new ReportViewModel
            {
               BookTitle = bk.BookTitle,
               BookId = bk.BookId, 
               StockLogValue=bksty.StockLogValue, 
               SaleTotal = trs.Sum(c => c.TransactQty), 
               GiftTotal = trs2.Sum(c => c.TransactQty), 
               SalesCount = trs.Count(), 
               GiftCount = trs2.Count() 
            });

I asked about navigation properties, because nearly always they make queries easier to write, since you don't need the cluncky joins. In your case the difference isn't that big though. If Book would have a navigation property Transacts the query could look like:

var query =
    (from bk in db.Books
     let sales = bk.Transacts
                   .Where(a => a.TransactDate.Month == month && a.TransactDate.Year == year && a.TransactType.TransactTypeName == "sale")
     let gifts = bk.Transacts
                   .Where(a => a.TransactDate.Month == month && a.TransactDate.Year == year && a.TransactType.TransactTypeName == "gift")
     select new ReportViewModel
           {
               BookTitle = bk.BookTitle,
               BookId = bk.BookId, 
               StockLogValue=bksty.StockLogValue, 
               SaleTotal = sales.Sum(c => c.TransactQty), 
               GiftTotal = gifts.Sum(c => c.TransactQty), 
               SalesCount = sales.Count(), 
               GiftCount = gifts.Count() 
           });