LINQ to SQL - group by day / week / month

Question

I am already scratching my head a bit over this. Say I'm making an extension method to group the list of items by date, I want to change the possible grouping so that the results can be grouped by day, week or month.

I came up with below, but I keep getting the following error:

Method 'System.Object DynamicInvoke (System.Object [])' does not support SQL translation

Method:

public static IQueryable<IGrouping<MonthDateGroup, T>> GroupByDate<T>( this IQueryable<T> items, DateGroupFrequency grouping, Expression<Func<T, DateTime?>> func) { var selector = func.Compile(); IQueryable<IGrouping<MonthDateGroup, T>> grouped = null; if (grouping == DateGroupFrequency.Daily) { grouped = from a in items let date = selector(a).Value group a by new MonthDateGroup { Day = date.Day, Month = date.Month, Year = date.Year } into g select g; } //Rest of groupings... 

I assume that using Func in the query causes an error, is it possible to create such code?

PS I'm still trying to wrap my head around Func and expression :)

Answer 1

If the group syntax is not:

 group a by new MonthDateGroup { a.Day = date.Day, a.Month = date.Month, a.Year = date.Year } 

Answer 2

Your problem is that the query itself cannot be launched in SQL. You may need to convert your elements to IEnumerable before using it. Note that this will cause the data to be loaded into memory from the SQL server, so you should leave it as late as possible in the LINQ chain.

You can either change the parameter of your elements as IEnumerable, not IQueryable, or if that doesn't work, create a variable in your method (at the top)

 var itemsEnumerable = items.AsEnumerable(); 

You will also have to change the return type from IQueryable to IEnumerable