I want to write a Linq query resulting in an ordinary and very simple group by SQL statement
My query looks like
This produces a NotSupportedException.
If I rewrite my query to this (as suggested from the exception message)
the resulting SQL is very overloaded
Is there any possibility to simplify the SQL?
You can override SqlGeneratingOuterSelectExpressionVisitor and take the implementation for VisitSqlGroupingSelect from SqlGeneratingSelectExpressionVisitor. That gives you the result you'd like to see but you probably want to add a couple of guard conditions that once again result in a throw, e.g. SqlGroupingExpression.Key must be unique. Otherwise you might end up with weird results.
The problem with the 'ordinary' group-by is that there is only a very specific subset can be readily supported and you have to detect whether the result could contain multiple entries for a single key. This in turn means verifying that the grouping's key is unique, which can only be done easily for non-nested statements. There's probably a bunch of other stuff I'm not thinking about right now, too, that needs to be considered.
Alternativly, the second query certainly carries a bit of overhead that could be optimized in cases where the the second part (i.e. "from b in mygroup select b") isn't adding anything valuable to the query. This optimization might quite possibly be done in the SqlPreparationQueryModelVisitor.
There might be a difference in the Linq syntax /feature set between C# and VB.NET. In C#, if you write
you get a sequence of groupings as a result instead of a flat list. So, that's not what you normally want in the simple case. Therefor, you write
without the second from-clause. There's still an extra subselect in there but that's not going to hurt you in SQL Server.
Interestingly enough, in VB.NET, the short group-by looks to have the same result type (without the grouping) as the full version and there is no way to skip the second from clause like so:
That's interseting and would certainly warrent closer examination to figure out if there's just a VB.NET optimization issue burried somewhere since it would certainly be preferable to be able to write VB.NET statements just as well as C# statements.
In general, while the generated SQL is a tad wordy, it is semantically correct and shouldn't hurt you in terms of performance. In contrast, the join-optimization does have enough impact to be a measurable improvement if you have many joins in the query that are the result of a relation property.