动态LINQ GROUPBY多列 [英] Dynamic LINQ GroupBy Multiple Columns

问题描述

我需要翻译下面的LINQ查询，以动态的LINQ接受基于用户输入几个分组列。基本上我有一堆dropdownlists应用于分组，我不想一一列举集团的每个组合。如果动态LINQ失败，我可能需要手动构造一个SQL查询，没有人希望这样。

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 )
    } );

我不知道如何做到这一点的动态LINQ是完全靠外的无证世界，你好！选择/在哪里/排序依据的情况下。我只是想不通的语法。

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.

喜欢的东西：（？）

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类。参见：<一href="http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx">http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

追问： Enigmativity的解决方案工作的大多的。出于某种原因，它并不希望集团的日期时间StartOfWeek列 - 解决方法就是做一个二次分组：

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... =/

推荐答案

如果要明确使用LINQ动态查询库那么我的回答是不会是你想要的，但如果你希望你的需要的行为，你再乐于使用常规的LINQ的话，我想我可以提供帮助。

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.

基本上我已经创建了一个 EntryGrouper 类处理分组的逻辑所选择的值在下拉列表中，我已经假定变量部分，页＆放大器; 模块持有这些价值观。我也认为 ObjectContext.OmniturePageModules 是一个类型的枚举输入。

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.

所以，你的LINQ查询现在变成两个：

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.

第二个查询组第一次查询的结果通过创建 EntryGrouper 类的实例作为分组键。

The second query groups the results of the first query by creating instances of the EntryGrouper class as the grouping key.

我已经包含了 SeriesName 属性在 EntryGrouper 类，以便所有的分组逻辑被整齐地定义在一个地方。

I've included a SeriesName property in the EntryGrouper class so that all of the grouping logic is neatly defined in one place.

现在，在 EntryGrouper 类是相当大的，允许分组的工作，它需要有 StartOfWeek ，科，页＆放大器; 模块，并且包含的​​等于＆放大器过载; GetHash code 方法，实施 IEquatable＆LT;进入＆GT; 接口

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.

这是：

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;

如果我误解你怎么下拉值转分组开和关，那么你应该只需要改变这些方法，但这种code的症结在于分组时，它应该返回基于一组值该条目中的值，并以其他方式它应该返回给所有条目具有共同的值。如果该值是常见的所有条目，然后它在逻辑上仅创建这是一样的不分组在所有单个组

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.

如果你有，你那时分组，你需要更多的属性添加到 EntryGrouper 类的更多的下拉列表中。不要忘了这些新的属性添加到等于＆放大器; GetHash code 方法了。

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.

这个逻辑，因此，再presents你想要的动态组。请让我知道，如果我已经帮助或者如果您需要更多的细节。

This logic, therefore, represents the dynamic grouping that you wanted. Please let me know if I've helped or if you need more detail.

享受！

这篇关于动态LINQ GROUPBY多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！