Linq Group By Multiple Columns

Linq Group By Multiple Columns

Linq Group by Multiple Columns. Linq group by It’s a very simple example of Linq Group by clause. We are selecting rows from Orders table and grouping orders by shipment city fields. It also shows count of shipment cities. Linq Group by multiple fields or columns are explained here with C#.net and VB.net examples.

  1. Linq Chapter 1 – Split string to list of strings.
  2. Linq Chapter 2 – Remove duplicates from list.
  3. Linq Chapter 3 – Linq Where Clause.
  4. Linq Chapter 4 – Linq Group by multiple clause.
  5. Linq Chapter 5 – Linq Inner Join query.
  6. Linq Chapter 6 – Linq Distinct list.
  7. Linq Chapter 7 – Linq OfType Method.
C#.net Linq Group by example.
from orderList in Orders
group orderList by new { orderList.ShipCity } into orderGroup
select new { City = orderGroup.Key.ShipCity , CityCount = orderGroup.Key.ShipCity.Count()}

VB.net simple group by example.

From orderGroup In From orderList In OrdersGroup orderList By New From { _
	orderList.ShipCity _
}New With { _
	Key .City = orderGroup.Key.ShipCity, _
	Key .CityCount = orderGroup.Key.ShipCity.Count() _
}

Group by Extension Method example.

Orders
   .GroupBy (
      orderList => 
         new  
         {
            ShipCity = orderList.ShipCity
         }
   )
   .Select (
      orderGroup => 
         new  
         {
            City = orderGroup.Key.ShipCity, 
            CityCount = orderGroup.Key.ShipCity.Count ()
         }
   )

SQL statement generated by above Linq Group by example.

SELECT COUNT(*) AS [CityCount], [t0].[ShipCity] AS [City]
FROM [Orders] AS [t0]
GROUP BY [t0].[ShipCity]

Linq Group by Multiple Columns.

Grouping the rows by multiple columns is achieved by adding one more column in group by Linq expression.

from orderList in Orders

group orderList by new { orderList.ShipCity, orderList.ShipVia } into orderGroup

select new { City = orderGroup.Key.ShipCity 
,ShipVia= orderGroup.Key.ShipVia
, CityCount = orderGroup.Key.ShipCity.Count()}

Extension method for Group by multiple column.

Orders
   .GroupBy (
      orderList => 
         new  
         {
            ShipCity = orderList.ShipCity, 
            ShipVia = orderList.ShipVia
         }
   )
   .Select (
      orderGroup => 
         new  
         {
            City = orderGroup.Key.ShipCity, 
            ShipVia = orderGroup.Key.ShipVia, 
            CityCount = orderGroup.Key.ShipCity.Count ()
         }
   )

Linq Group by Count.

There is no Having clause in Linq. However, we can achieve the results by Where extension in Linq. Let's see example of Group by count where Ship City count is more than 2.

from orderList in Orders

group orderList by new { orderList.ShipCity, orderList.ShipVia } into orderGroup

where orderGroup.Key.ShipCity.Count() > 2

select new { City = orderGroup.Key.ShipCity 
,ShipVia= orderGroup.Key.ShipVia
, CityCount = orderGroup.Key.ShipCity.Count()}

Vb.net Linq Group by multiple columns.

From orderGroup In From orderList In OrdersGroup orderList By New From { _
	orderList.ShipCity, _
	orderList.ShipVia _
}New With { _
	Key .City = orderGroup.Key.ShipCity, _
	Key .ShipVia = orderGroup.Key.ShipVia, _
	Key .CityCount = orderGroup.Key.ShipCity.Count() _
}

Extension Method Group By Multiple Columns.

Orders
   .GroupBy (
      orderList => 
         new  
         {
            ShipCity = orderList.ShipCity, 
            ShipVia = orderList.ShipVia
         }
   )
   .Where (orderGroup => (orderGroup.Key.ShipCity.Count () > 2))
   .Select (
      orderGroup => 
         new  
         {
            City = orderGroup.Key.ShipCity, 
            ShipVia = orderGroup.Key.ShipVia, 
            CityCount = orderGroup.Key.ShipCity.Count ()
         }
   )

Linq Group by multiple columns and order by Count example.

from orderList in Orders

group orderList by new { orderList.ShipCity, orderList.ShipVia } into orderGroup

orderby orderGroup.Key.ShipCity.Count() descending

select new { City = orderGroup.Key.ShipCity 
,ShipVia= orderGroup.Key.ShipVia
, CityCount = orderGroup.Key.ShipCity.Count()}

Output.

Linq Group by Multiple Columns

Extension method Group by multiple columns and order by descending.
Orders
   .GroupBy (
      orderList => 
         new  
         {
            ShipCity = orderList.ShipCity, 
            ShipVia = orderList.ShipVia
         }
   )
   .OrderByDescending (orderGroup => orderGroup.Key.ShipCity.Count ())
   .Select (
      orderGroup => 
         new  
         {
            City = orderGroup.Key.ShipCity, 
            ShipVia = orderGroup.Key.ShipVia, 
            CityCount = orderGroup.Key.ShipCity.Count ()
         }
   )

Error: A query body must end with a select clause or a group clause. It happens when you forgot to end Linq statements with select or Group by clause.

Tags:

How to select multiple columns in Linq- C#.net VB.net codeLinq Inner JoinLinq Get Duplicate Count Of Words From StringLinq Split String To ListLearn Linq Step By StepLinq Distinct ListLinq Union and intersect ExampleC# ArrayList Search using Linq Extension OfType MethodLinq ToDictionary ExtensionLinq Where ClauseLinq to Sql

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.