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

Linq to entities? Please show (essential parts of) the classes, because you should try to do this with navigation properties in stead of joins. — Gert Arnold, Aug 02 '15 at 18:29
Why don't you have navigation properties, like `Book.StockLogs`? — Gert Arnold, Aug 02 '15 at 19:05
The truth is I have not thought of that : I have created navigation Id for books in stocklogs now will that make it simpler to eliminate the outer joins? — Diin, Aug 02 '15 at 19:35
I have even made StockLogValue a direct property for Books no need to reference another table. Because what I am thinking is the raw stock updates are stored in the stocks table and then the year and month is appended to the existing stocklogvalue of a particular book — Diin, Aug 02 '15 at 19:45

score 2 · Accepted Answer · edited May 23 '17 at 10:26

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() 
           });

Linq group outer joins projected on a viewmodel

1 Answers1