GROUP BY WITH ROLLUP 的 LINQ to SQL 版本 [英] LINQ to SQL version of GROUP BY WITH ROLLUP

问题描述

我正在尝试将一些旧的 SQL 重写为 LINQ to SQL.我有一个带有 GROUP BY WITH ROLLUP 的 sproc，但我不确定 LINQ 等效项是什么.LINQ 有一个 GroupBy，但它看起来不支持 ROLLUP.

我试图得到的结果的一个简化示例是这样的:

<前>+-----------+---------------+------------+|城市 |服务计划 |客户数量 |+-----------+---------------+------------+|西雅图 |计划A |10 ||西雅图 |计划B |5 ||西雅图 |全部 |15 ||波特兰 |计划A |20 ||波特兰 |计划C |10 ||波特兰 |全部 |30 ||全部 |全部 |45 |+-----------+---------------+------------+

关于如何使用 LINQ to SQL 获得这些结果的任何想法?

解决方案

我想出了一个更简单的解决方案.我试图让它变得比它需要的更复杂.而不是需要 3-5 个类/方法，我只需要一种方法.

基本上，您自己进行排序和分组，然后调用 WithRollup() 以获取带有小计和总计的项目的 List<>.我不知道如何在 SQL 端生成小计和总计，所以这些都是用 LINQ to Objects 完成的.代码如下:

////<摘要>///将小计添加到项目列表中，以及整个列表的总计.///</总结>///<param name="elements">在调用 WithRollup 之前自己分组和/或排序.</param>///<param name="primaryKeyOfElement">给定一个 TElement，返回您想要小计的属性.</param>///<param name="calculateSubTotalElement">给定一组元素，返回一个表示小计的 TElement.</param>///<param name="grandTotalElement">表示总计的 TElement.</param>公共静态列表WithRollup(这个IEnumerable元素，Func<TElement, TKey>主键元素，Func<IGrouping<TKey, TElement>, TElement>计算子总元素，TElementgrandTotalElement){//创建一个包含项目、小计和总计的新列表.列表结果 = 新列表();var lookup = elements.ToLookup(primaryKeyOfElement);foreach(查找中的var组){//添加当前组中的项目结果.AddRange(组)；//为当前组添加 subTotal结果.添加(calculateSubTotalElement(group));}//添加总计结果.Add(grandTotalElement);返回结果；}

以及如何使用它的示例:

class 程序{静态无效主(字符串 [] args){IQueryable数据项 = (新[]{new CustomObject { City = "Seattle", Plan = "Plan B", Charges = 20 },new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },new CustomObject { City = "Seattle", Plan = "Plan B", Charges = 20 },new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },new CustomObject { City = "Portland", Plan = "Plan A", Charges = 10 },new CustomObject { City = "Portland", Plan = "Plan A", Charges = 10 },new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 },new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 },new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 }}).AsQueryable();IQueryableorderedElements = 来自 dataItems 中的项目orderby item.City, item.Plan按 new { item.City, item.Plan } 将 item 分组选择新的自定义对象{城市 = grouping.Key.City,计划 = grouping.Key.Plan，费用 = grouping.Sum(item => item.Charges),计数 = grouping.Count()};列表<自定义对象>结果 =orderedElements.WithRollup(项目 =>item.City,组 =>新的自定义对象{城市 = group.Key,计划 = "全部",费用 = group.Sum(item => item.Charges),Count = group.Sum(item => item.Count)},新的自定义对象{城市 = "全部",计划 = "全部",费用 =orderedElements.Sum(item => item.Charges),计数 =orderedElements.Sum(item => item.Count)});foreach (var 结果在结果中)Console.WriteLine(result);Console.Read();}}类自定义对象{公共字符串城市{得到；放;}公共字符串计划{得到；放;}公共 int 计数 { 得到；放;}公共十进制费用{得到；放;}公共覆盖字符串 ToS​​tring(){return String.Format("{0} - {1} ({2} - {3})", City, Plan, Count, Charges);}}

I'm trying to rewrite some old SQL into LINQ to SQL. I have a sproc with a GROUP BY WITH ROLLUP but I'm not sure what the LINQ equivalent would be. LINQ has a GroupBy but it doesn't look like it supports ROLLUP.

A simplified example of the results I'm trying to get would be something like this:

+-----------+---------------+--------------------+
|   City    |  ServicePlan  |  NumberOfCustomers |
+-----------+---------------+--------------------+
| Seattle   |  Plan A       |                 10 |
| Seattle   |  Plan B       |                  5 |
| Seattle   |  All          |                 15 |
| Portland  |  Plan A       |                 20 |
| Portland  |  Plan C       |                 10 |
| Portland  |  All          |                 30 |
| All       |  All          |                 45 |
+-----------+---------------+--------------------+

Any ideas on how I could get these results using LINQ to SQL?

解决方案

I figured out a much simpler solution. I was trying to make it way more complicated than it needed to be. Rather than needing 3-5 classes/methods I only need one method.

Basically, you do your sorting and grouping yourself and then call WithRollup() to get a List<> of the items with sub-totals and a grand total. I couldn't figure out how to generate the sub-totals and grand total on the SQL side so those are done with LINQ to Objects. Here's the code:

/// <summary>
/// Adds sub-totals to a list of items, along with a grand total for the whole list.
/// </summary>
/// <param name="elements">Group and/or sort this yourself before calling WithRollup.</param>
/// <param name="primaryKeyOfElement">Given a TElement, return the property that you want sub-totals for.</param>
/// <param name="calculateSubTotalElement">Given a group of elements, return a TElement that represents the sub-total.</param>
/// <param name="grandTotalElement">A TElement that represents the grand total.</param>
public static List<TElement> WithRollup<TElement, TKey>(this IEnumerable<TElement> elements,
    Func<TElement, TKey> primaryKeyOfElement,
    Func<IGrouping<TKey, TElement>, TElement> calculateSubTotalElement,
    TElement grandTotalElement)
{
    // Create a new list the items, subtotals, and the grand total.
    List<TElement> results = new List<TElement>();
    var lookup = elements.ToLookup(primaryKeyOfElement);
    foreach (var group in lookup)
    {
        // Add items in the current group
        results.AddRange(group);
        // Add subTotal for current group
        results.Add(calculateSubTotalElement(group));
    }
    // Add grand total
    results.Add(grandTotalElement);

    return results;
}

And an example of how to use it:

class Program
{
    static void Main(string[] args)
    {
        IQueryable<CustomObject> dataItems = (new[]
        {
            new CustomObject { City = "Seattle", Plan = "Plan B", Charges = 20 },
            new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },
            new CustomObject { City = "Seattle", Plan = "Plan B", Charges = 20 },
            new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },
            new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },
            new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },
            new CustomObject { City = "Portland", Plan = "Plan A", Charges = 10 },
            new CustomObject { City = "Portland", Plan = "Plan A", Charges = 10 },
            new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 },
            new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 },
            new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 }
        }).AsQueryable();

        IQueryable<CustomObject> orderedElements = from item in dataItems
                                                   orderby item.City, item.Plan
                                                   group item by new { item.City, item.Plan } into grouping
                                                   select new CustomObject
                                                   {
                                                       City = grouping.Key.City,
                                                       Plan = grouping.Key.Plan,
                                                       Charges = grouping.Sum(item => item.Charges),
                                                       Count = grouping.Count()
                                                   };

        List<CustomObject> results = orderedElements.WithRollup(
            item => item.City,
            group => new CustomObject
            {
                City = group.Key,
                Plan = "All",
                Charges = group.Sum(item => item.Charges),
                Count = group.Sum(item => item.Count)
            },
            new CustomObject
            {
                City = "All",
                Plan = "All",
                Charges = orderedElements.Sum(item => item.Charges),
                Count = orderedElements.Sum(item => item.Count)
            });

        foreach (var result in results)
            Console.WriteLine(result);

        Console.Read();
    }
}

class CustomObject
{
    public string City { get; set; }
    public string Plan { get; set; }
    public int Count { get; set; }
    public decimal Charges { get; set; }

    public override string ToString()
    {
        return String.Format("{0} - {1} ({2} - {3})", City, Plan, Count, Charges);
    }
}

这篇关于GROUP BY WITH ROLLUP 的 LINQ to SQL 版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！