CODEWITHSUNDEEP
c#entity-frameworklinqentity-framework-coreef-core-2.1
andleer
andleer

Reputation: 22578

EF Core Group By with Deferred Execution and SQL side grouping

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.

Upvotes: 3

Views: 1185

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205759

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.

Upvotes: 4

Related Questions