Group by with multiple columns using lambda

Question

How can I group by with multiple columns using lambda?

I saw examples of how to do it using linq to entities, but I am looking for lambda form.

Aducci · Accepted Answer · 2014-11-25T14:41:35.907

356

var query = source.GroupBy(x => new { x.Column1, x.Column2 });

edited Nov 25 '14 at 14:41

answered Aug 04 '11 at 02:11

Aducci

26,101
8
63
67

Would this actually work? I would think that the equality test for each object you're grouping by would fail since they're objects and not structs. – Jacob Aug 04 '11 at 02:17
@Aducci: Thanks. Can you example how can I get IEnumerable of the group items? – Naor Aug 04 '11 at 02:22
6

@Jacob - Anonymous types are immutable classes with properly overriden `GetHashCode` & `Equals` methods. They where designed for exactly this kind of use case. – Enigmativity Aug 04 '11 at 02:57
6

@Naor - `GroupBy` returns an `IEnumerable>` which is essentially an `IEnumerable>` with a `Key` property on the inner enumerable. Does that help you to get the "IEnumerable" of the group items? – Enigmativity Aug 04 '11 at 03:00
If my 'source' variable is a Dictionary Collection this not work. Suggestions? – Joao Paulo Oct 15 '15 at 13:32
1

The `new {}` keyword seems to be the `new` golden hammer of Entity with C#, nice work. – Antoine Pelletier Jan 03 '19 at 19:33

score 15 · Answer 2 · answered May 11 '20 at 15:53

I came up with a mix of defining a class like David's answer, but not requiring a Where class to go with it. It looks something like:

var resultsGroupings = resultsRecords.GroupBy(r => new { r.IdObj1, r.IdObj2, r.IdObj3})
                                    .Select(r => new ResultGrouping {
                                        IdObj1= r.Key.IdObj1,
                                        IdObj2= r.Key.IdObj2,
                                        IdObj3= r.Key.IdObj3,
                                        Results = r.ToArray(),
                                        Count = r.Count()
                                    });



private class ResultGrouping
        {
            public short IdObj1{ get; set; }
            public short IdObj2{ get; set; }
            public int IdObj3{ get; set; }

            public ResultCsvImport[] Results { get; set; }
            public int Count { get; set; }
        }

Where resultRecords is my initial list I'm grouping, and its a List<ResultCsvImport>. Note that the idea here to is that, I'm grouping by 3 columns, IdObj1 and IdObj2 and IdObj3

This won't work on a query, and the question is about performing GroupBy on multiple columns inside a query. — ErroneousFatality, Apr 22 '22 at 14:00

score 4 · Answer 3 · answered Apr 19 '13 at 13:51

4

if your table is like this

rowId     col1    col2    col3    col4
 1          a       e       12       2
 2          b       f       42       5
 3          a       e       32       2
 4          b       f       44       5


var grouped = myTable.AsEnumerable().GroupBy(r=> new {pp1 =  r.Field<int>("col1"), pp2 = r.Field<int>("col2")});

answered Apr 19 '13 at 13:51

Jhankar Mahbub

9,746
10
49
52

15

It is very important to note that AsEnumerable will bring the entire table into memory before grouping it. That definitely matters on some tables. See this answer for more insight: https://stackoverflow.com/questions/17968469/whats-the-differences-between-tolist-asenumerable-asqueryable/17996264#17996264 – Brandon Barkley Apr 19 '18 at 16:32

score 3 · Answer 4 · edited May 23 '17 at 11:47

3

Further to aduchis answer above - if you then need to filter based on those group by keys, you can define a class to wrap the many keys.

return customers.GroupBy(a => new CustomerGroupingKey(a.Country, a.Gender))
                .Where(a => a.Key.Country == "Ireland" && a.Key.Gender == "M")
                .SelectMany(a => a)
                .ToList();

Where CustomerGroupingKey takes the group keys:

    private class CustomerGroupingKey
    {
        public CustomerGroupingKey(string country, string gender)
        {
            Country = country;
            Gender = gender;
        }

        public string Country { get; }

        public string Gender { get; }
    }

edited May 23 '17 at 11:47

Community

1
1

answered Nov 16 '16 at 06:16

David McEleney

3,397
1
26
32

2

Probably will save someone some time: it's better to use default constructions with object initializaters. The approach in the sample code above will **not** be treated by ORMs like EF Core well. – Konstantin Apr 06 '17 at 19:13
Strange. I am using .NET 6 now. But using something like `.GroupBy(a => new CustomerGroupingKey(a.Country, a.Gender))` would not work. But when I use anonymous class like `.GroupBy( a => new { a.Country, a.Gender}`, the group function works. – Lam Le Nov 16 '22 at 12:55

score 3 · Answer 5 · answered Aug 02 '17 at 09:48

     class Element
        {
            public string Company;        
            public string TypeOfInvestment;
            public decimal Worth;
        }

   class Program
    {
        static void Main(string[] args)
        {
         List<Element> elements = new List<Element>()
            {
                new Element { Company = "JPMORGAN CHASE",TypeOfInvestment = "Stocks", Worth = 96983 },
                new Element { Company = "AMER TOWER CORP",TypeOfInvestment = "Securities", Worth = 17141 },
                new Element { Company = "ORACLE CORP",TypeOfInvestment = "Assets", Worth = 59372 },
                new Element { Company = "PEPSICO INC",TypeOfInvestment = "Assets", Worth = 26516 },
                new Element { Company = "PROCTER & GAMBL",TypeOfInvestment = "Stocks", Worth = 387050 },
                new Element { Company = "QUASLCOMM INC",TypeOfInvestment = "Bonds", Worth = 196811 },
                new Element { Company = "UTD TECHS CORP",TypeOfInvestment = "Bonds", Worth = 257429 },
                new Element { Company = "WELLS FARGO-NEW",TypeOfInvestment = "Bank Account", Worth = 106600 },
                new Element { Company = "FEDEX CORP",TypeOfInvestment = "Stocks", Worth = 103955 },
                new Element { Company = "CVS CAREMARK CP",TypeOfInvestment = "Securities", Worth = 171048 },
            };

            //Group by on multiple column in LINQ (Query Method)
            var query = from e in elements
                        group e by new{e.TypeOfInvestment,e.Company} into eg
                        select new {eg.Key.TypeOfInvestment, eg.Key.Company, Points = eg.Sum(rl => rl.Worth)};



            foreach (var item in query)
            {
                Console.WriteLine(item.TypeOfInvestment.PadRight(20) + " " + item.Points.ToString());
            }


            //Group by on multiple column in LINQ (Lambda Method)
            var CompanyDetails =elements.GroupBy(s => new { s.Company, s.TypeOfInvestment})
                               .Select(g =>
                                            new
                                            {
                                                company = g.Key.Company,
                                                TypeOfInvestment = g.Key.TypeOfInvestment,            
                                                Balance = g.Sum(x => Math.Round(Convert.ToDecimal(x.Worth), 2)),
                                            }
                                      );
            foreach (var item in CompanyDetails)
            {
                Console.WriteLine(item.TypeOfInvestment.PadRight(20) + " " + item.Balance.ToString());
            }
            Console.ReadLine();

        }
    }

Group by with multiple columns using lambda

5 Answers5

Linked