我需要将以下LINQ查询转换为动态LINQ查询,以便根据用户输入接受多个分组列。基本上,我有一堆应用分组的下拉列表,我不想枚举每个分组的组合。如果动态LINQ失败,我可能不得不手动构造SQL查询,而没有人希望那样。
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 )
} );
我完全不知道如何做到这一点,因为除了“hello world!”之类的select/where/orderby情况外,动态LINQ在外部完全没有文档记录。我只是无法弄清语法。
类似这样:(?)
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)");
我正在使用System.Linq.Dynamic中的DynamicQueryable类。请参见:http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
跟进:Enigmativity的解决方案在大部分情况下都有效。但由于某种原因,它不想按datetime“StartOfWeek”列进行分组--解决方法只是进行第二次分组:
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 ),
} );
但这似乎严重影响性能... =/
Daniel Coffman
- 我不知道为什么它没有按StartOfWeek
进行分组,它应该有的。我重新检查了代码,Equals
和GetHashCode
方法使用了StartOfWeek
值。如果你想让我进一步调查,请告诉我。我预计每个列的分组代码可能需要针对你的需求进行一些“微调”。 - Enigmativity