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

Answer 2

@Ecyrb, hello from five years later!

I'm only vaguely familiar with LINQ to SQL above and beyond standard LINQ (to objects). However, since you do have a "LINQ" tag separate from your "LINQ-2-SQL" tag, because you seem to be primarily interested in the results (as opposed to registering changes with the database), and because this is the only real relevant resource that came up when I googled in search for a LINQ equivalent of SQL Server's "Rollup" grouping function, I'll offer my own alternative solution for anyone today with a similar need.

Basically, my approach is to create a ".GroupBy().ThenBy()" chainable syntax similar to the ".OrderBy().ThenBy()" syntax. My extension expects a collection of IGrouping objects--the result you get from running ".GroupBy()"--as its source. It then takes the collection and ungroups them to get back at the original object before grouping. Finally, it re-groups the data according to the new grouping function, producing another set of IGrouping objects, and adds the newly grouped objects into the set of source objects.

public static class mySampleExtensions {

    public static IEnumerable<IGrouping<TKey, TSource>> ThenBy<TSource, TKey> (     
        this IEnumerable<IGrouping<TKey, TSource>> source,
        Func<TSource, TKey> keySelector) {

        var unGroup = source.SelectMany(sm=> sm).Distinct(); // thank you flq at http://stackoverflow.com/questions/462879/convert-listlistt-into-listt-in-c-sharp
        var reGroup = unGroup.GroupBy(keySelector);

        return source.Concat(reGroup);}

}

You can use the method to match SQL server's rollup logic by putting constant values into the appropriate area of the ".ThenBy()" function. I prefer to use the null value because it is the most flexible constant for casting. Casting is important because the function you use in both .GroupBy() and .ThenBy() must result in the same object type. Using the "dataItems" variable you created in your first response on Aug 31 '09, it would look like this:

var rollItUp = dataItems
    .GroupBy(g=> new {g.City, g.Plan})
        .ThenBy(g=> new {g.City, Plan = (string) null})
        .ThenBy(g=> new {City = (string) null, Plan = (string) null})
    .Select(s=> new CustomObject {
        City = s.Key.City, 
        Plan = s.Key.Plan, 
        Count = s.Count(),
        Charges = s.Sum(a=> a.Charges)})
    .OrderBy(o=> o.City) // This line optional
        .ThenBy(o=> o.Plan); // This line optional

You could replace the nulls in the ".ThenBy()" logics with "all", as you desire.

You could potentially emulate SQL Server's grouping sets, and maybe cube, with the help of ".ThenBy()". Also, ".ThenBy()" is working fine for me, and I don't forsee any issues with the name being equivalent to the ".ThenBy()" of the ".OrderBy()" method since they have different signatures, but if there's trouble, you may want to consider naming it ".ThenGroupBy()" to distinguish.

As mentioned, I don't use Linq-to-SQL, but I do use F#'s type provider system, which I understand uses Linq-to-SQL under the hood in many respects. So I tried my extension on such an object from my F# project, and it works as I expected. Though I have absolutely no idea if this means anything interesting or not in this regard.