How to use multiple Group By in Entity Framework C#?

Question

I have an SQL query written for multiple group by

select COUNT(Count) as Count, City,State,ZipCode from ZipSearchHistories Group By City,State,ZipCode

Now I need to change this SQL query into Entity Framework. What I tried so far is

var result = await repository.GetAllAsync();
      
            var data = from m in result
                       group m by m.City, m.State,m.ZipCode into g
                       let Count = g.Count()
                       orderby Count descending
                       select new { Count= Count,City = m.City,State = m.State, ZipCode = m.ZipCode}

but it didn't work. How can I solve this?

define "didn't work". did it throw an error? then share it. did it group your data differently from what you expected? then share what you expected and what happened. — Franz Gleichmann, Oct 07 '20 at 05:25
Could this answer your query https://stackoverflow.com/a/847097/7299782 — Anu Viswan, Oct 07 '20 at 05:25
For LINQ, you need to "group by" using a type, for example `.GroupBy (m => new { m.City, m.State,m.ZipCode})`. But when you ask a question, YOU SHOULD ALWAYS COPY/PASTE THE EXACT ERROR!, For future reference :) — paulsm4, Oct 07 '20 at 05:29
@BirazDahal then the logical step would've been to check in the manual _what_ the correct syntax might have been. googling for "linq group by syntax", for example, yielded [this page](https://learn.microsoft.com/en-gb/dotnet/csharp/linq/group-query-results) as first result. — Franz Gleichmann, Oct 07 '20 at 05:31

score 0 · Answer 1 · answered Oct 07 '20 at 06:36

So you have a sequence of ZipSearchHistories. Every ZipSearchHistory in this sequence has at least a ZipCode, a State and a City.

Some of these ZipSearchHistories have the same values for {ZipCode, State, City}. You want to count for every combination of {ZipCode, State, City} the number of ZipSearchHistories that have this combination.

You are right, for this you'll need one of the overloads of Queryable.GroupBy. I most often use the overload that has a parameter resultSelector. With this parameter you can specify the output per group.

var result = dbContext.ZipSearchHistories(

    // parameter keySelector: make groups with same values for {ZipCode, State, City}
    zipSearchHistory => new
    {
        ZipCode = zipSearchHistory.ZipCode, 
        State = zipSearchHistory.State,
        City = zipSearchHistory.City,
    },

    // parameter resultSelector: for every key {ZipCode, State, City},
    // and all ZipSearchHistories that have this {ZipCode, State, City} make one new:
    (key, zipSearchHistoriesWithThisKey) => new
    {
        ZipCode = key.ZipCode, 
        State = key.State,
        City = key.City,
        Count = zipSearchHistoriesWithThisKey.Count(),
    })

In words: make groups of ZipSearchHistories, that have the same value for {ZipCode, State, City}. From every group (where all elements have this {ZipCode, State, City}) make one new object, that has these {ZipCode, State, City} and the number of elements in the group.

How to use multiple Group By in Entity Framework C#?

1 Answers1