EF Core Group By with Deferred Execution and SQL side grouping

c# ef-core-2.1 entity-framework entity-framework-core linq

Question

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.

1
3
12/10/2018 2:14:51 AM

Accepted Answer

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.

4
12/9/2018 8:27:53 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow