How to left join to another left join

Question

I have a table that holds stock items. These stock items have the following properties

public class StockItem
{
    public int BrandId { get; set; }
    public int ModelId { get; set; }
    public decimal Price { get; set; }
    public int StoreId { get; set; }
}

Where StoreId can be either 1 (Store A) or 2 (Store B)

I want to group these items to get the following result

BrandId
ModelId
Count in Store A
count in Store B

I have used group by clause before but not in this way. How should I write the linQ statement to accomplish my task?

To summarize I have the following records

Id   BrandId   ModelId   Price   StoreId
=========================================
1       1         1       11        1
2       1         1       11        1
3       1         2       12        1
4       1         2       12        2
5       1         1       11        2

and trying to get the following result

BrandId   ModelId   CountInStoreA   CountInStoreB
=================================================
    1        1            2               1
    1        2            1               1

I think you can find more info suitable to your question here: http://stackoverflow.com/questions/448203/linq-to-sql-using-group-by-and-countdistinct — Matheno, Apr 08 '15 at 10:54

score 3 · Accepted Answer · answered Apr 08 '15 at 10:58

3

var result = from item in db.Items
          group item by new { item.BrandId, item.ModelId, item.TotalPrice }
          into gr
          select new 
          {
              gr.Key.BrandId,
              gr.Key.ModelId,
              gr.Key.TotalPrice,
              CountA = gr.Count(it => it.StoreId == 1),
              CountB = gr.Count(it => it.StoreId == 2),
          }

answered Apr 08 '15 at 10:58

Thank you very much for your help. But what if I were using multiple tables with inner and outer joins and I needed to group with parameters from multiple tables/objects at the same time? – user4591106 Apr 08 '15 at 12:35
@user4591106 Please, update your question in explain in details your scenario. – Apr 08 '15 at 14:07
Working more on the subject and making extensive search I have found a solution for the next question. But I appreciate your intention on further help. Thank you very much. – user4591106 Apr 08 '15 at 14:14

How to left join to another left join

1 Answers1