linq 有没有with rollup语法？

[witeem]

本人最近在用LINQ代替SQL做一个项目，遇到了with rollup的问题。求大神不吝赐教。
原SQL语句：

SELECT [One_project],[Two_project],[Three_project],[XM_ID],[TR_Year],SUM (S_TR) AS S_TRCount,SUM (ZY_TR) AS ZY_TRCount,SUM (HY_TR) AS HY_TRCount,SUM (QT_TR) AS QT_TRCount,SUM(TR_SUM) AS sum_TRCount FROM [input] WHERE OneHeadID = '7' GROUP BY One_project,Two_project,Three_project,XM_ID,TR_Year WITH rollup HAVING XM_ID IS NOT NULL

[娃都会打酱油了]

默认没有……你可以在Group的结果上进行相关Sum

var query = from l in list
group l by new {A=l.A,B=l.B} into g
select g;
var query2 = from g in query
group g by g.Key.A into x
select x;
//然后query和query2合并结果

[q107770540]

You can use this extension function:

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

Here is the Sample：

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

[witeem]

引用 2 楼 q107770540 的回复:

You can use this extension function:

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

Here is the Sample：

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

这个我也查到了，到时我用不到LINQ TO EF上?

[devmiao]

直接调用sql。大猫的办法如果用于sql，需要用存储过程。