dotnet / dotnet/efcore

Support GROUPING SET, ROLLUP, CUBE with SQL GROUP BY

Open
#37,532 5 comments 0 reactions 0 assignees View on GitHub
area-groupby area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

### What problem are you trying to solve?

Now GROUP BY CUBE not supported. Now I use
```
var query = q.GroupBy(x =>
new Grouping()
{
Level1 = x.Level1,
Level2 = x.Level2,
})
.Select(x => new UniqueClientsRow
{
Level1 = x.Key.Level1,
Level2 = x.Key.Level2,
UniqueClients = x.Select(x => x.CardHolderAnonymousId).Distinct().Count()
});

var sqlQuery = query.ToQueryString();
// replace via regex GROUP BY to GROUP BY CUBE ()
var newSqlQuery=FixSqlString(sqlQuery);
var result = await dbContext.Database.SqlQueryRaw(newSqlQuery).ToArrayAsync();

with:
private string FixSqlString(string sql)
{
var r = new Regex(@"GROUP\s+BY\s+([^(\n]+(?:\([^)]+\)[^(\n]*)*)");
var m = r.Match(sql);
if (m.Success)
{
var groupBy = m.Groups[1].Value.Trim();
var replacedSql = sql.Replace(m.Value, "GROUP BY CUBE (" + groupBy + ")");
return replacedSql;
}
else
{
throw new InvalidOperationException();
}
}
```
This is very fragile solution, but I think that GROUP BY CUBE syntax can be supported with EF, because it works with this hack.

### Describe the solution you'd like

Proposed API variant A
```
var query = q.GroupBy(x =>
new Grouping()
{
Level1 = x.Level1,
Level2 = x.Level2,
}).Cube() // Cube added here
.Select(x => new UniqueClientsRow
{
Level1 = x.Key.Level1,
Level2 = x.Key.Level2,
UniqueClients = x.Select(x => x.CardHolderAnonymousId).Distinct().Count()
});
```

Proposed API variant B
```
var query = q.GroupByCube(x => // New GroupByCube method
new Grouping()
{
Level1 = x.Level1,
Level2 = x.Level2,
})
.Select(x => new UniqueClientsRow
{
Level1 = x.Key.Level1,
Level2 = x.Key.Level2,
UniqueClients = x.Select(x => x.CardHolderAnonymousId).Distinct().Count()
});
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.