Using EF Core 2.1 which does support Group By translation but not after I project the key value.
I have a query that needs to allow for a range of grouping types and a range of aggregate types.
Group By: Year, Year/Month, Year/Month/Day etc
Aggregate By: Avg, Sum, Min, Max etc.
I created two switch
statements, one for the grouping and one for the aggregation. My issue is that I am not able to defer execution and perform the grouping within SQL. If not, the resulting data set is quite large.
Is any of this a reasonable approach or should I just use a raw query?
This is deferred and grouped as desired but the key type is unique to year/month and I am not able to provide a general solution for the aggregates in the second switch
if I wanted to group by something different such as year/month/day.
var query1 = series
.GroupBy(k => new { k.Created.Year, k.Created.Month, });
var result1 = query1
.Select(i => new { i.Key, Value = i.Average(a => a.DataValue) });
As soon as you convert the key to a string, grouping occurs client-side:
var query2 = series
.GroupBy(k => k.Created.Year.ToString());
var result2 = query2
.Select(i => new { i.Key, Value = i.Average(a => a.DataValue) });
This too causes grouping to occur client side:
var query3 = series
.GroupBy(k => new { k.Created.Year, k.Created.Month, })
.Select(i => new
{
Key = $"{i.Key.Year}-{i.Key.Month}",
Values = i.ToList()
});
Any ideas how to accomplish my query? Ideally I need a common group key that groups server-side or a method of passing the aggregate based on a function in the query. Generating a string
based key seems to ensure that the grouping occurs client side.
The only way I was able to get SQL translation in EF Core 2.1 (and 2.2) was to group by composite anonymous type conditionally including the desired data parts:
bool includeYear = ...;
bool includeMonth = ...;
bool includeDay = ...;
bool includeHour = ...;
var query1 = series
.GroupBy(e => new
{
Year = includeYear ? e.CreatedDate.Year : 0,
Month = includeMonth ? e.CreatedDate.Month : 0,
Day = includeDay ? e.CreatedDate.Day : 0,
Hour = includeHour ? e.CreatedDate.Hour : 0
});
Weirdly enough, if I create a special class for group key with exactly the same properties as the anonymous type and change new { ... }
to new MyKeyType { ... }
, EF Core switches to client evaluation.
Shortly, GroupBy
(and not only) SQL translation is still unstable. Looks like we have to wait for 3.0 to get eventually improvements in that area.