Grouping with LINQ

CrimRepo

Hello!
I was unpleasantly surprised when I tried to use GroupBy operator in my LINQ query.
In his pocket reference on LINQ Joe is describing the GroupBy operator for LINQ2SQL and tells that it's so fine and it steps away from traditional SQL giving a possibilty not to select the grouping fields. But he's keeping strangely quiet of the fact that it emits multiple SQL queries, which is the crucial fact! It happens even when I select only the grouping fields. I need it to emit just 1 query. How to do this?

kimbonics

Is the query really slower? Only then should you really care honestly. You're not using linq2sql to code generate a SQL project are you?

jamesmanning

Can you give more details your query? Or make a simple table that reproduces the issue? I only see 1 SQL query when I do something like:

from row in this.SomeTable
group row by row.SomeColumn into rowGroup
select rowGroup.Key

JoeAlbahari

Right - there's only one SQL query if you select just the keys and aggregations. There are more examples in the built-in samples section of LINQPad:

from p in Purchases
group p.Price by p.Date.Year into salesByYear
select new
{
	Year       = salesByYear.Key,
	TotalValue = salesByYear.Sum()
}

If you *do* want to select all the individual groups (something not possible with SQL) you can do without round-tripping. The trick is to do the grouping on the client instead:

from p in Purchases.Where (p => p.Description.StartsWith ("A")).AsEnumerable()
group p by p.Date.Year 

This is just as efficient (in fact more efficient) than grouping on the server. Be sure, though, to filter *before* calling .AsEnumerable(), otherwise you'll pull all the rows from the database table onto the client, which is *not* efficient.

CrimRepo

You're right. I was trying to define the element selector after the group keyword. When I rewrote it using into, it all got normal.
But tell me please another thing. You say that grouping on the client is more efficient. But isn't the DB engine optimized at maximum degree to perform all the operations in the most effecient way?