Custom LINQ GroupBy helper erroring with "This function can only be invoked from LINQ to Entities."

Question

I'm attempting to make a GroupBy helper overload to simplify a common reporting case: Grouping by date (day, week, month, year, etc.)

Here's my code:

IEnumerable<TResult> GroupByDate<TSource, TResult>(IEnumerable<TSource> source,
    DateTime startDate,
    DateTime endDate,
    ReportDateType dateType,
    Func<TSource, System.DateTime?> keySelector,
    Func<ReportDate, IEnumerable<TSource>, TResult> resultSelector) where TResult: ReportRow, new()
{
    var results = from x in source
                    group x by
                    (dateType == ReportDateType.Day ? new ReportDate() { Year = keySelector(x).Value.Year, Month = keySelector(x).Value.Month, Week = SqlFunctions.DatePart("week", keySelector(x)) ?? 0, Day = keySelector(x).Value.Day } :
                    dateType == ReportDateType.Week ? new ReportDate() { Year = keySelector(x).Value.Year, Month = keySelector(x).Value.Month, Week = SqlFunctions.DatePart("week", keySelector(x)) ?? 0, Day = 0 } :
                    dateType == ReportDateType.Month ? new ReportDate() { Year = keySelector(x).Value.Year, Month = keySelector(x).Value.Month, Week = 0, Day = 0 } :
                    new ReportDate() { Year = keySelector(x).Value.Year, Month = 0, Week = 0, Day = 0 }) into g
                    orderby g.Key
                    select resultSelector(g.Key, g);

    var resultsGap = new List<TResult>();
    var currentDate = startDate;
    while (currentDate <= endDate)
    {
        var reportRow = (TResult)Activator.CreateInstance(typeof(TResult));
        if (dateType == ReportDateType.Day)
        {
            reportRow.Date = new ReportDate() { Year = currentDate.Year, Month = currentDate.Month, Week = currentDate.GetIso8601WeekOfYear(), Day = currentDate.Day };
            currentDate = currentDate.AddDays(1);
        }
        else if (dateType == ReportDateType.Week)
        {
            reportRow.Date = new ReportDate() { Year = currentDate.Year, Month = currentDate.Month, Week = currentDate.GetIso8601WeekOfYear() };
            currentDate = currentDate.AddDays(7);
        }
        else if (dateType == ReportDateType.Month)
        {
            reportRow.Date = new ReportDate() { Year = currentDate.Year, Month = currentDate.Month };
            currentDate = currentDate.AddMonths(1);
        }
        else if (dateType == ReportDateType.Year)
        {
            reportRow.Date = new ReportDate() { Year = currentDate.Year };
            currentDate = currentDate.AddYears(1);
        }
        resultsGap.Add(reportRow);
    }

    return results.ToList().Union(resultsGap, new ReportRowComparer<TResult>()).OrderBy(o => o.Date);
}

Sample use case:

var results = GroupByDate(db.Orders, startDate.Value, endDate.Value, ReportDateType.Week, k => k.DateOrdered,
    (k, g) => new RevenueReportRow() { Date = k, Revenue = g.Sum(i => i.Total), Cost = g.Sum(i => i.Cost), Profit = g.Sum(i => i.Total) - g.Sum(i => i.Cost) })

I've run into the exception:

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=This function can only be invoked from LINQ to Entities.
  Source=EntityFramework.SqlServer
  StackTrace:
       at System.Data.Entity.SqlServer.SqlFunctions.DatePart(String datePartArg, Nullable`1 date)

The code above worked when it wasn't a helper method, and it works when I replace Week = SqlFunctions.DatePart("week", keySelector(x)) ?? 0 with Week = 0, but the totals are wrong...

I've found a similar question that addresses an issue with creating a helper with a predicate parameter. Unfortunately I wasn't able to figure out how to apply the same approach with specifying the group by value.

Edit

For posterity, I've completed the helper, and it is available here: https://gist.github.com/albertbori/4944fd0e78534782cae2

Answer 1

I'm really close

No, you are far far away.

The code above worked when it wasn't a helper method

This is because the helper method code is not equivalent to the original code. The main difference is that the original code was operating on IQueryable<Source> while the helper method code - on IEnumerable<TSoure>. This is a fundamental change of both building block primitives and the execution behavior. The former operates on Expression<Func<.., the later - on Func<... The former builds and executes database query, the later does everything in memory. Some functions are available only for the former and some only for the later. Shortly, they are not freely interchangeable.

So, in order to make them equivalent, your helper must operate on IQueryable<TSource>, which in turn will require parameters to be Expression<... type.

Now, the first thing you should ask yourself is whether helper method is worth enough. Working with expressions is not so easy as with funcs, it requires a good knowledge of the System.Linq.Expressions. Also, since the expressions must be created by code, you cannot use LINQ query syntax anymore, so you need a good knowledge of the Queryable methods and their mappings to the LINQ syntax constructs.

With all that being said, and assuming it's worth, let see how we can solve the concrete issue.

(1) source argument type becomes IQueryable<TSource>

(2) keySelector argument type becomes Expression<Func<TSource, DateTime>>. Note that I do not use DateTime?, the way you wrote the code implies that the property type is DateTime, and you make it nullable only to be able to call SqlFunctions.DatePart. To make the life easier, we need to get a pure property accessor, we can do the necessary conversions when needed.

(3) resultSelector argument type becomes Expression<Func<IGrouping<ReportDate, TSource>, TResult>>

(4) Now the tricky part - the group by. We need an expression that converts keySelector to Expression<Func<TSource, ReportDate>>. Here the coding finally starts:

static Expression<Func<TSource, ReportDate>> ToReportDate<TSource>(Expression<Func<TSource, DateTime>> keySelector, ReportDateType dateType)
{
    var source = keySelector.Parameters[0];
    var member = keySelector.Body;
    var year = Expression.Property(member, "Year");
    var month = dateType == ReportDateType.Day || dateType == ReportDateType.Week || dateType == ReportDateType.Month ? (Expression)
        Expression.Property(member, "Month") :
        Expression.Constant(0);
    var week = dateType == ReportDateType.Day || dateType == ReportDateType.Week ? (Expression)
        Expression.Convert(Expression.Call(typeof(SqlFunctions), "DatePart", null,
        Expression.Constant("week"), Expression.Convert(member, typeof(DateTime?))
        ), typeof(int)) :
        Expression.Constant(0);
    var day = dateType == ReportDateType.Day ? (Expression)
        Expression.Property(member, "Day") :
        Expression.Constant(0);
    var dateSelector = Expression.Lambda<Func<TSource, ReportDate>>(
        Expression.MemberInit(
            Expression.New(typeof(ReportDate)),
            Expression.Bind(typeof(ReportDate).GetProperty("Year"), year),
            Expression.Bind(typeof(ReportDate).GetProperty("Month"), month),
            Expression.Bind(typeof(ReportDate).GetProperty("Week"), week),
            Expression.Bind(typeof(ReportDate).GetProperty("Day"), day)
        ), source);
    return dateSelector;
}

If you don't understand the above code, read again the "is worth" and "knowledge required" section.

Finally, the helper method becomes like this

static IEnumerable<TResult> GroupByDate<TSource, TResult>
(
    IQueryable<TSource> source,
    DateTime startDate,
    DateTime endDate,
    ReportDateType dateType,
    Expression<Func<TSource, DateTime>> keySelector,
    Expression<Func<IGrouping<ReportDate, TSource>, TResult>> resultSelector
)
where TResult : ReportRow, new()
{
    var results = source
        .GroupBy(ToReportDate(keySelector, dateType))
        .OrderBy(g => g.Key)
        .Select(resultSelector);
    // the rest of the code
}

And the sample usage

var results = GroupByDate(db.Orders, startDate.Value, endDate.Value, ReportDateType.Week, k => k.DateOrdered,
    g => new RevenueReportRow() { Date = g.Key, Revenue = g.Sum(i => i.Total), Cost = g.Sum(i => i.Cost), Profit = g.Sum(i => i.Total) - g.Sum(i => i.Cost) });

Answer 2

Because you declared your input and output as IEnumerable<T> calling the method brings you out of context of Entity Framework and into LINQ to Objects world, where your query runs in memory. In that in-memory query you can't use SqlFunctions anymore.

You could change your input and output from IEnumerable<T> to IQueryable<T> and from Func<T> to Expresion<Func<T>> to make it part of your Entity Framework query, but your method uses stuff that EF will not be able to translate into proper SQL query (Activator.CreateInstance, while loop and more), so it will not work either. You would just get a different error.

You have two ways to fix your problem now:

• stop using SqlFunctions and make it a proper LINQ to Objects in-memory query
• change your input/output parameters to use IQueryable<T>and Expression<> instead of IEnumerable<T> andFunc<>` and make it possible for EF to translate your logic into SQL.

Be aware, that if you go with first option and do

var results = GroupByDate(db.Orders, startDate.Value, endDate.Value, ReportDateType.Week, k => k.DateOrdered,
    (k, g) => new RevenueReportRow() { Date = k, Revenue = g.Sum(i => i.Total), Cost = g.Sum(i => i.Cost), Profit = g.Sum(i => i.Total) - g.Sum(i => i.Cost) })

it will bring the entire Orders table into the memory of your application, and run your grouping as LINQ to Objects query. That's in pretty much 100% cases not what you want.