We're updating the issue view to help you get more done. 

Optimized support 'ordinary' group-by statements, i.e. group-by statements the select projection only contains the key or aggregate operators

Description

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?

Activity

Show:
Michael Ketting
February 9, 2016, 7:39 AM

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.

Michael Ketting
February 9, 2016, 3:17 PM
Edited

Adendum:

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.

Assignee

Unassigned

Reporter

User known

Labels

None

Components

Fix versions

Priority

Normal
Configure