Dynamic LINQ GroupBy Multiple Columns
Solution 1
If you explicitly want to use the LINQ Dynamic Query Library then my answer isn't going to be what you want, but if you want your desired behaviour and you're happy to use regular LINQ then I think I can help.
Essentially I've created an EntryGrouper
class that handles the logic of grouping by the selected values in the dropdown lists and I've assumed that the variables section
, page
& module
hold those values. I've also assumed that ObjectContext.OmniturePageModules
is an enumerable of type Entry
.
So your LINQ query now becomes these two:
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
{
SeriesName = entryGroup.Key.SeriesName,
Week = entryGroup.Key.StartOfWeek,
Clicks = entryGroup.Sum(p => p.Clicks),
};
The first query is used to force a simple select query on the database and return only the records that you want to group. Generally group by
queries call the database multiple times so querying in this way is usually much faster.
The second query groups the results of the first query by creating instances of the EntryGrouper
class as the grouping key.
I've included a SeriesName
property in the EntryGrouper
class so that all of the grouping logic is neatly defined in one place.
Now, the EntryGrouper
class is quite large as, to allow grouping to work, it needs to have properties for StartOfWeek
, Section
, Page
& Module
, and contain overloads of the Equals
& GetHashCode
methods, and implement the IEquatable<Entry>
interface.
Here it is:
public class EntryGrouper : IEquatable<Entry>
{
private Entry _entry;
private string _section;
private string _page;
private string _module;
public EntryGrouper(Entry entry, string section, string page, string module)
{
_entry = entry;
_section = section;
_page = page;
_module = module;
}
public string SeriesName
{
get
{
return String.Format("{0}:{1}:{2}", this.Section, this.Page, this.Module);
}
}
public DateTime StartOfWeek
{
get
{
return _entry.StartOfWeek;
}
}
public string Section
{
get
{
if (_section == "Total" || _section == "All")
return _section;
return _entry.Section;
}
}
public string Page
{
get
{
if (_page == "Total" || _page == "All")
return _page;
return _entry.Page;
}
}
public string Module
{
get
{
if (_module == "Total" || _module == "All")
return _module;
return _entry.Module;
}
}
public override bool Equals(object other)
{
if (other is Entry)
return this.Equals((Entry)other);
return false;
}
public bool Equals(Entry other)
{
if (other == null)
return false;
if (!EqualityComparer<DateTime>.Default.Equals(this.StartOfWeek, other.StartOfWeek))
return false;
if (!EqualityComparer<string>.Default.Equals(this.Section, other.Section))
return false;
if (!EqualityComparer<string>.Default.Equals(this.Page, other.Page))
return false;
if (!EqualityComparer<string>.Default.Equals(this.Module, other.Module))
return false;
return true;
}
public override int GetHashCode()
{
var hash = 0;
hash ^= EqualityComparer<DateTime>.Default.GetHashCode(this.StartOfWeek);
hash ^= EqualityComparer<string>.Default.GetHashCode(this.Section);
hash ^= EqualityComparer<string>.Default.GetHashCode(this.Page);
hash ^= EqualityComparer<string>.Default.GetHashCode(this.Module);
return hash;
}
public override string ToString()
{
var template = "{{ StartOfWeek = {0}, Section = {1}, Page = {2}, Module = {3} }}";
return String.Format(template, this.StartOfWeek, this.Section, this.Page, this.Module);
}
}
The grouping logic of this class looks simply like this:
if (_page == "Total" || _page == "All")
return _page;
return _entry.Page;
If I have misunderstood how you the dropdown values turn grouping on and off then you should just need to change these methods, but the crux of this code is that when grouping is on it should return a group value based on the value in the entry and otherwise it should return a common value for all entries. If the value is common for all entries then it logically only creates a single group which is the same as not grouping at all.
If you have more dropdowns that you're grouping by then you need to add more properties to the EntryGrouper
class. Don't forget to add these new properties to the Equals
& GetHashCode
methods too.
This logic, therefore, represents the dynamic grouping that you wanted. Please let me know if I've helped or if you need more detail.
Enjoy!
Solution 2
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 )
} );
Daniel Coffman
Updated on July 19, 2022Comments
-
Daniel Coffman almost 2 years
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... =/
-
Daniel Coffman over 13 yearsThank you very much for your extensive answer. I'll try this tomorrow and let you know if it works for me -- a quick glance over it is encouraging.
-
Daniel Coffman over 13 yearsThis doesn't seem to group by StartOfWeek for some reason. I had to change the grouping code for each column to if( _section == "All" ) return _entry.Section; return _section;
-
Enigmativity over 13 years@
Daniel Coffman
- I don't know why it hasn't grouped byStartOfWeek
, it should have. I rechecked the code and theEquals
&GetHashCode
methods use theStartOfWeek
value. Give me a yell if you'd like me to look into it further. I had expected the grouping code for each column would probably need to be "tweaked" a bit for your needs.