how to group by multiple columns using linq

debugcn Published at Dev

rlcrews

I have a database table with a dataset that contains multiple rows of data as follows

ItemId               Code                             StatusId
-------------------- ---------------------------------------------
62224                NC0860000                             8
62225                NC0860000                             8
62226                NC0860000                             8
62227                NC0860200                             5
62228                NC0860000                             5
62229                NC0860000                             5
62230                NC0860000                             5

What I would like to accomplish is an output result as

NC0860000  8  3  (code, status, count)
NC0860000  5  3

I don't fully understand how grouping works in EF. I can get the key and a count of a single group using a query as:

var results = (from ssi in ctx.StageSubmitItems
                           join s in ctx.StageSubmissions on ssi.SubmissionId equals s.SubmissionId
                           where s.ContributorCode == contributorId
                           group ssi.SubmitItemId by ssi.AgencyCode into g
                           select new {AgencyCode = g.Key, Count = g.Count() }).ToList();

But I can't figure out how to group by code and then by StatusId and then produce a count of the total number of rows by status.

I'd appreciate any suggestions on where to look on how to accomplish this or what I am doing incorrectly in the query.

Timothy Walters

You can group by a new anon class as follows:

// I created a Foo class to show this working
var fooList = new List<Foo> {
    new Foo { ItemId = 62224, Code = "NC0860000", StatusId = 8 },
    new Foo { ItemId = 62225, Code = "NC0860000", StatusId = 8 },
    new Foo { ItemId = 62226, Code = "NC0860000", StatusId = 8 },
    new Foo { ItemId = 62227, Code = "NC0860200", StatusId = 5 },
    new Foo { ItemId = 62228, Code = "NC0860000", StatusId = 5 },
    new Foo { ItemId = 62229, Code = "NC0860000", StatusId = 5 },
    new Foo { ItemId = 62230, Code = "NC0860000", StatusId = 5 },
};

var results = (from ssi in fooList
    // here I choose each field I want to group by
    group ssi by new { ssi.Code, ssi.StatusId } into g
    select new { AgencyCode = g.Key.Code, Status = g.Key.StatusId, Count = g.Count() }
).ToList();

// LINQPad output command
results.Dump();

With the data provided, here is the output:

AgencyCode Status Count
NC0860000  8      3 
NC0860200  5      1 
NC0860000  5      3

I am guessing "NC0860200" is an error, but it is in your sample data so I included it.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-24

Comments

0 comments

From Dev