Group By on Join in LINQ

Viewed 138

I am working with:

(i) a table called Income which looks like this -

enter image description here

(ii) a table called Attribute which looks like this -

enter image description here

(iii) and a table called AccountCustomOrder which looks like this -

enter image description here

The output that I'm trying to achieve(similar to a PIVOT TABLE in sql) would look like something like this(grouped by AccountCode): enter image description here

Now, in order to achieve it with LINQ, I have tried this:

public List<AccountLevelData> GetGroupedAccountLevelData(long jobId){

var data = (from in in _dbContext.Set<Income>()
                          join at in _dbContext.Set<Attribute>()
                          on in.AttributeSetId equals ar.AttributeSetId
                          join co in _dbContext.Set<AccountCustomOrder>()
                          on in.AccountCode equals co.AccountCode
                          where in.JobId == jobId
                          select new AccountLevelData
                          {
                              AccountCode = in.AccountCode,
                              AccountDescription = co.AccountDescription,
                              AccountOrder = co.AccountOrder,
                              AccountAttributes = new AccountAttributes
                              {
                                  AttributeAValue= (at.AttributeValue == "Attribute A Value") ? in.FunctionalAmt : 0,
                                  AttributeBValue= (at.AttributeValue == "Attribute B Value") ? in.FunctionalAmt : 0
                              }
                          }).ToList()

return data;
    }

But I'm not sure how to group the joined data in order to achieve the desired data(grouped by AccountCode). Can anyone suggest?

0 Answers
Related