C# – Dynamic LINQ GroupBy Multiple Columns

cdynamic-linqentity-frameworklinqnet

I need to translate the following LINQ query to Dynamic LINQ that accepts several grouping columns based on user input. Basically I have a bunch of dropdownlists that apply groupings and I don't want to enumerate every combination of groupings. If Dynamic LINQ fails, I may have to construct a SQL query manually, and nobody wants that.

var grouping = ( from entry in ObjectContext.OmniturePageModules
    where entry.StartOfWeek >= startDate && entry.StartOfWeek <= endDate &&
        ( section == "Total" || section == "All" || entry.Section == section ) &&
        ( page == "Total" || page == "All" || entry.Page == page ) &&
        ( module == "Total" || module == "All" || entry.Module == module ) 
    group entry by new
    {
        entry.Page, // I want to be able to tell this anonymous type
        entry.Module, // which columns to group by
        entry.StartOfWeek // at runtime
    }
    into entryGroup
    select new
    {
        SeriesName = section + ":" + entryGroup.Key.Page + ":" + entryGroup.Key.Module,
        Week = entryGroup.Key.StartOfWeek,
        Clicks = entryGroup.Sum( p => p.Clicks )
    } );

I have no clue how to do this as Dynamic LINQ is totally undocumented outside of the "hello world!" select/where/orderby cases. I just can't figure out the syntax.

Something like:(?)

var grouping = ObjectContext.OmniturePageModules.Where(entry => entry.StartOfWeek >= startDate && entry.StartOfWeek <= endDate &&
                                           ( section == "Total" || section == "All" || entry.Section == section ) &&
                                           ( page == "Total" || page == "All" || entry.Page == page ) &&
                                           ( module == "Total" || module == "All" || entry.Module == module ))
                                           .GroupBy("new (StartOfWeek,Page,Module)", "it")
                                           .Select("new (Sum(Clicks) as Clicks, SeriesName = section + key.Page + Key.Module, Week = it.Key.StartOfWeek)");

I'm using the DynamicQueryable class in System.Linq.Dynamic. See: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Follow-up:
Enigmativity's solution worked mostly. For some reason it doesn't want to group by the datetime "StartOfWeek" column — workaround is just to do a secondary grouping:

var entries = ( from entry in ObjectContext.OmniturePageModules
                            where entry.StartOfWeek >= startDate
                                && entry.StartOfWeek <= endDate
                                && ( section == "Total" || section == "All" || entry.Section == section )
                                && ( page == "Total" || page == "All" || entry.Page == page )
                                && ( module == "Total" || module == "All" || entry.Module == module )
                            select entry ).ToArray(); // Force query execution

            var grouping = from entry in entries
                            let grouper = new EntryGrouper( entry, section, page, module )
                            group entry by grouper into entryGroup
                            select new
                            {
                                entryGroup.Key.SeriesName,
                                entryGroup.Key.Date, 
                                Clicks = entryGroup.Sum( p => p.Clicks ),
                            };

            var grouping2 = (from groups in grouping
                            group groups by new {groups.SeriesName, groups.Date } into entryGroup
                            select new
                            {
                               entryGroup.Key.SeriesName,
                               entryGroup.Key.Date,
                               Clicks = entryGroup.Sum( p => p.Clicks ),
                            } );

but this seems to seriously degrade performance… =/

Best Answer

Here it is in Dynamic LINQ -- of course you build the GroupBy and Select strings at runtime:

var double_grouping = ( ObjectContext.OmniturePageModules.Where( entry => entry.StartOfWeek >= startDate
                     && entry.StartOfWeek <= endDate
                     && ( section == "Total" || section == "All" || entry.Section == section )
                     && ( page == "Total" || page == "All" || entry.Page == page )
                     && ( module == "Total" || module == "All" || entry.Module == module ) )
                     .GroupBy( "new ( it.Section, it.Page, it.StartOfWeek )", "it" ) )
                     .Select( "new ( Sum(Clicks) as Clicks, Key.Section as SeriesSection, Key.Page as SeriesPage, Key.StartOfWeek as Week )" );

And here is the normal LINQ way that escaped me until a coworker pointed it out -- this is basically Enigmativity's solution without the grouper class:

var grouping = ( from entry in ObjectContext.OmniturePageModules
    where entry.StartOfWeek >= startDate && entry.StartOfWeek <= endDate &&
        ( section == "Total" || section == "All" || entry.Section == section ) &&
        ( page == "Total" || page == "All" || entry.Page == page ) &&
        ( module == "Total" || module == "All" || entry.Module == module )
    group entry by new
    {
        Section = section == "All" ? entry.Section : section,
        Page = page == "All" ? entry.Page : page,
        Module = module == "All" ? entry.Module : module,
        entry.StartOfWeek
    }
        into entryGroup
        select new
        {
            SeriesName =
            entryGroup.Key.Section + ":" + entryGroup.Key.Page + ":" + entryGroup.Key.Module,
            Week = entryGroup.Key.StartOfWeek,
            Clicks = entryGroup.Sum( p => p.Clicks )
        } );