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.
- Linq Chapter 1 – Split string to list of strings.
- Linq Chapter 2 – Remove duplicates from list.
- Linq Chapter 3 – Linq Where Clause.
- Linq Chapter 4 – Linq Group by multiple clause.
- Linq Chapter 5 – Linq Inner Join query.
- Linq Chapter 6 – Linq Distinct list.
- 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.
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.