动态LINQ组多列 [英] Dynamic LINQ GroupBy Multiple Columns
问题描述
我需要将以下LINQ查询转换为基于用户输入接受多个分组列的动态LINQ。基本上我有一堆应用分组的下拉列表，我不想枚举分组的每个组合。如果动态LINQ失败，我可能需要手动构造SQL查询，没有人想要这样做。
var grouping =（从ObjectContext.OmniturePageModules中的条目
其中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，//我想要能够告诉这个匿名类型
entry.Module，//哪些列组通过
entry.StartOfWeek //在运行时
}
into entryGroup
选择新
{
SeriesName = section +：+ entryGroup.Key。 Page +：+ entryGroup.Key.Module，
Week = entryGroup.Key.StartOfWeek，
Clicks = entryGroup.Sum（p => p.Clicks）
}）;
我不知道如何做到这一点，因为动态LINQ在你好世界之外完全没有文件！ 选择/ where / orderby案例。我只是想不出语法。
某些东西：（？）
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）&&
==Total|| module ==All|| entry.Module == module））
.GroupBy（new（StartOfWeek，Page，Module），it）
。选择（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的解决方案主要是。由于某种原因，它不希望通过datetimeStartOfWeek列进行分组 - 解决方法只是进行二次分组：
var entries =（来自ObjectContext.OmniturePageModules
中的条目）entry.StartOfWeek> = startDate
&&&&&& entry.StartOfWeek< = endDate
&&（section = =Total|| section ==All|| entry.Section == section）
&&（页==Total|| page ==All|| entry.Page = = page）
&&（module ==Total|| module ==All|| entry.Module == module）
select entry）.ToArray（）; //强制查询执行
var grouping =从条目中的条目
let grouper = new EntryGrouper（条目，部分，页面，模块）
由grouper组成条目组的条目组
选择新
{
entryGroup.Key.SeriesName，
entryGroup.Key.Date，
Clicks = entryGroup.Sum（p => p.Clicks），
};
var grouping2 =（从组
组中的组由新的{groups.SeriesName，groups.Date}转换为entryGroup
选择新
{
entryGroup.Key.SeriesName，
entryGroup.Key.Date，
Clicks = entryGroup.Sum（p => p.Clicks），
}）;
但这似乎严重降低了性能... = /
如果你明确地想要使用LINQ动态查询库，那么我的答案不会是你想要的，但是如果你想要你想要的行为，很高兴使用常规LINQ，那么我想我可以帮助。
本质上我创建了一个 EntryGrouper 类，处理下拉列表中所选值分组的逻辑，我假设变量部分，页 &安培; module 保留这些值。我也假定 ObjectContext.OmniturePageModules 是一个枚举类型为 Entry 。
所以你的LINQ查询现在变成这两个：
var entries =（从ObjectContext.OmniturePageModules中的条目
其中entry.StartOfWeek> = startDate
&&&&&&&&&&&& || entry.Section == section）
&&（页==Total|| page ==All|| entry.Page == page）
&&（ module ==Total|| module ==All|| entry.Module == module）
select entry）.ToArray（）; //强制查询执行
var grouping =从条目中的条目
let grouper = new EntryGrouper（条目，部分，页面，模块）
由grouper组成条目组的条目组
选择新
{
SeriesName = entryGroup.Key.SeriesName，
Week = entryGroup.Key.StartOfWeek，
Clicks = entryGroup.Sum（p => p.Clicks ），
};
第一个查询用于强制对数据库进行简单的选择查询，并仅返回您所记录的记录想组合通常 group by 查询多次调用数据库，这样查询通常会快得多。
第二个查询通过创建 EntryGrouper 类的实例作为分组键来对第一个查询的结果进行分组。
在 EntryGrouper 类中包含一个 SeriesName 属性，以便所有的分组逻辑在一个地方整齐地定义。 p>
现在， EntryGrouper 类是相当大的，为了允许分组工作，它需要具有 StartOfWeek ， Section ，页面& 模块，并包含等于& GetHashCode 方法，并实现 IEquatable< Entry> 界面。
这里是：
public class EntryGrouper：IEquatable< Entry>
{
private Entry _entry;
private string _section;
私人字符串_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（ （条目）除外）;
返回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;
返回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）;
返回哈希;
}
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）;
}
}
这个类的分组逻辑看起来像这样：
if（_page ==Total|| _page ==All）
return _page;
return _entry.Page;
如果我误解了您的下拉列表如何打开和关闭分组，那么您只需要更改这些方法，但这个代码的关键在于，当分组时，它应该根据条目中的值返回一个组值，否则应该返回所有条目的公用值。如果所有条目的值是通用的，那么它在逻辑上只会创建一个与所有条目不一致的组。
如果您有更多下拉列表然后分组，您需要向 EntryGrouper 类添加更多属性。不要忘记将这些新属性添加到等于& GetHashCode 方法。
因此，此逻辑表示您想要的动态分组。请让我知道，如果我有帮助，或者如果你需要更多的细节。
享受！
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... =/
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!
这篇关于动态LINQ组多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
