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.

Solution 1

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

Solution 2

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

Solution 3

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")});

Solution 4

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; }
    }

Solution 5

     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();

        }
    }

View more solutions

Comments

[Jacob]

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.

[Naor]

@Aducci: Thanks. Can you example how can I get IEnumerable of the group items?

[Enigmativity]

@Jacob - Anonymous types are immutable classes with properly overriden GetHashCode & Equals methods. They where designed for exactly this kind of use case.

[Enigmativity]

@Naor - GroupBy returns an IEnumerable<IGrouping<TKey, TSource>> which is essentially an IEnumerable<IEnumerable<TSource>> with a Key property on the inner enumerable. Does that help you to get the "IEnumerable" of the group items?

[Joao Paulo]

If my 'source' variable is a Dictionary Collection this not work. Suggestions?

[Konstantin]

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.

[Brandon Barkley]

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: stackoverflow.com/questions/17968469/…

[Antoine Pelletier]

The new {} keyword seems to be the new golden hammer of Entity with C#, nice work.

[ErroneousFatality]

This won't work on a query, and the question is about performing GroupBy on multiple columns inside a query.