CODEWITHSUNDEEP
c#linq-to-sqlgroup-byfunc
Hux
Hux

Reputation: 3122

LINQ to SQL - Group By Day/Week/Month

I have been scratching my head over this one for a while now. Say I make an extension method, to group a 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 the below, but I keep getting the following error :

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL

The 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'm assuming the use of the Func within the Query is causing the error, is it possible to produce code like this?

P.S. I'm still trying to wrap my head around Func's, and Expressions :)

Upvotes: 4

Views: 4132

Answers (2)

Matthew Steeples
Matthew Steeples

Reputation: 8058

Your problem looks like the query itself cannot be executed into SQL. You may need to convert your items to an IEnumerable before using it. Please note that this will cause the data to be loaded in to memory from SQL server so should be left as late as possible in your LINQ chain.

You could either change your items parameter to be an IEnumerable rather than IQueryable or if that doesn't work create a variable in your method (near the top)

var itemsEnumerable = items.AsEnumerable();

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

Upvotes: 2

Refugee
Refugee

Reputation: 483

Should the group syntax not be:

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

Upvotes: 3

Related Questions