LINQ to SQL version of GROUP BY WITH ROLLUP

Question

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?

Answer 1

I got it! A generic GroupByWithRollup. It only groups by two columns, but could easily be extended to support more. I'll probably have another version that accepts three columns. The key classes/methods are Grouping<>, GroupByMany<>(), and GroupByWithRollup<>(). The SubTotal() and GrandTotal() methods are helpers when you actually use GroupByWithRollup<>(). Below is the code, followed by an example of how to use it.

/// <summary>
/// Represents an instance of an IGrouping<>.  Used by GroupByMany(), GroupByWithRollup(), and GrandTotal().
/// </summary>
public class Grouping<TKey, TElement> : IGrouping<TKey, TElement>
{
    public TKey Key { get; set; }
    public IEnumerable<TElement> Items { get; set; }

    public IEnumerator<TElement> GetEnumerator()
    {
        return Items.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return Items.GetEnumerator();
    }
}

public static class Extensions
{
    /// <summary>
    /// Groups by two columns.
    /// </summary>
    /// <typeparam name="TElement">Type of elements to group.</typeparam>
    /// <typeparam name="TKey1">Type of the first expression to group by.</typeparam>
    /// <typeparam name="TKey2">Type of the second expression to group by.</typeparam>
    /// <param name="orderedElements">Elements to group.</param>
    /// <param name="groupByKey1Expression">The first expression to group by.</param>
    /// <param name="groupByKey2Expression">The second expression to group by.</param>
    /// <param name="newElementExpression">An expression that returns a new TElement.</param>
    public static IQueryable<Grouping<TKey1, TElement>> GroupByMany<TElement, TKey1, TKey2>(this IOrderedQueryable<TElement> orderedElements,
        Func<TElement, TKey1> groupByKey1Expression,
        Func<TElement, TKey2> groupByKey2Expression,
        Func<IGrouping<TKey1, TElement>, IGrouping<TKey2, TElement>, TElement> newElementExpression
        )
    {
        // Group the items by Key1 and Key2
        return from element in orderedElements
               group element by groupByKey1Expression(element) into groupByKey1
               select new Grouping<TKey1, TElement>
               {
                   Key = groupByKey1.Key,
                   Items = from key1Item in groupByKey1
                           group key1Item by groupByKey2Expression(key1Item) into groupByKey2
                           select newElementExpression(groupByKey1, groupByKey2)
               };
    }

    /// <summary>
    /// Returns a List of TElement containing all elements of orderedElements as well as subTotals and a grand total.
    /// </summary>
    /// <typeparam name="TElement">Type of elements to group.</typeparam>
    /// <typeparam name="TKey1">Type of the first expression to group by.</typeparam>
    /// <typeparam name="TKey2">Type of the second expression to group by.</typeparam>
    /// <param name="orderedElements">Elements to group.</param>
    /// <param name="groupByKey1Expression">The first expression to group by.</param>
    /// <param name="groupByKey2Expression">The second expression to group by.</param>
    /// <param name="newElementExpression">An expression that returns a new TElement.</param>
    /// <param name="subTotalExpression">An expression that returns a new TElement that represents a subTotal.</param>
    /// <param name="totalExpression">An expression that returns a new TElement that represents a grand total.</param>
    public static List<TElement> GroupByWithRollup<TElement, TKey1, TKey2>(this IOrderedQueryable<TElement> orderedElements,
        Func<TElement, TKey1> groupByKey1Expression,
        Func<TElement, TKey2> groupByKey2Expression,
        Func<IGrouping<TKey1, TElement>, IGrouping<TKey2, TElement>, TElement> newElementExpression,
        Func<IGrouping<TKey1, TElement>, TElement> subTotalExpression,
        Func<IQueryable<Grouping<TKey1, TElement>>, TElement> totalExpression
        )
    {
        // Group the items by Key1 and Key2
        IQueryable<Grouping<TKey1, TElement>> groupedItems = orderedElements.GroupByMany(groupByKey1Expression, groupByKey2Expression, newElementExpression);

        // Create a new list the items, subtotals, and the grand total.
        List<TElement> results = new List<TElement>();
        foreach (Grouping<TKey1, TElement> item in groupedItems)
        {
            // Add items under current group
            results.AddRange(item);
            // Add subTotal for current group
            results.Add(subTotalExpression(item));
        }
        // Add grand total
        results.Add(totalExpression(groupedItems));

        return results;
    }

    /// <summary>
    /// Returns the subTotal sum of sumExpression.
    /// </summary>
    /// <param name="sumExpression">An expression that returns the value to sum.</param>
    public static int SubTotal<TKey, TElement>(this IGrouping<TKey, TElement> query, Func<TElement, int> sumExpression)
    {
        return query.Sum(group => sumExpression(group));
    }

    /// <summary>
    /// Returns the subTotal sum of sumExpression.
    /// </summary>
    /// <param name="sumExpression">An expression that returns the value to sum.</param>
    public static decimal SubTotal<TKey, TElement>(this IGrouping<TKey, TElement> query, Func<TElement, decimal> sumExpression)
    {
        return query.Sum(group => sumExpression(group));
    }

    /// <summary>
    /// Returns the grand total sum of sumExpression.
    /// </summary>
    /// <param name="sumExpression">An expression that returns the value to sum.</param>
    public static int GrandTotal<TKey, TElement>(this IQueryable<Grouping<TKey, TElement>> query, Func<TElement, int> sumExpression)
    {
        return query.Sum(group => group.Sum(innerGroup => sumExpression(innerGroup)));
    }

    /// <summary>
    /// Returns the grand total sum of sumExpression.
    /// </summary>
    /// <param name="sumExpression">An expression that returns the value to sum.</param>
    public static decimal GrandTotal<TKey, TElement>(this IQueryable<Grouping<TKey, TElement>> query, Func<TElement, decimal> sumExpression)
    {
        return query.Sum(group => group.Sum(innerGroup => sumExpression(innerGroup)));
    }

And an example of using 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();

        List<CustomObject> results = dataItems.OrderBy(item => item.City).ThenBy(item => item.Plan).GroupByWithRollup(
            item => item.City,
            item => item.Plan,
            (primaryGrouping, secondaryGrouping) => new CustomObject
            {
                City = primaryGrouping.Key,
                Plan = secondaryGrouping.Key,
                Count = secondaryGrouping.Count(),
                Charges = secondaryGrouping.Sum(item => item.Charges)
            },
            item => new CustomObject
            {
                City = item.Key,
                Plan = "All",
                Count = item.SubTotal(subItem => subItem.Count),
                Charges = item.SubTotal(subItem => subItem.Charges)
            },
            items => new CustomObject
            {
                City = "All",
                Plan = "All",
                Count = items.GrandTotal(subItem => subItem.Count),
                Charges = items.GrandTotal(subItem => subItem.Charges)
            }
            );
        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);
    }
}

Answer 2

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