Support GROUPING SET, ROLLUP, CUBE with SQL GROUP BY
- 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
Assessment
This issue has not been assessed yet.