Group By on Join in LINQ

Question

I am working with:

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

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

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

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

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?

It seems that group by is not what you want, I guess each account code is unique, You want the attribute value for each attribute name and that is the tricky part. Curious to see an answer to this question. — Jonathan Applebaum, Dec 11 '20 at 07:36
@jonathana each Account Code is unique alright but there can be multiple line items corresponding to a particular Account Code(with different Attribute Values). So I was wondering if a 2 level of grouping can be done - first by Account Code and then by Attribute Value; not sure if that is the correct approach though — The Inquisitive Coder, Dec 11 '20 at 08:08
I do not know how much data you are processing, but it may be worth considering the possibility of reducing it as much as possible with a LINQ query and the rest to perform in C# logic, and if efficiency is required and can be sorted numerically then use binary searches — Jonathan Applebaum, Dec 11 '20 at 09:00
You should use the linq group keyword. You can see it in action [Here](https://stackoverflow.com/a/7325306/14800433) — Tommy, Dec 11 '20 at 10:08

Group By on Join in LINQ

0 Answers0