How to group by multiple generic linq expressions

Question

I'm trying to use Linq expressions to construct a query, and am stuck trying to group by multiple columns. Say I have a basic collection:

IEnumerable<Row> collection = new Row[]
{
    new Row() { Col1 = "a", Col2="x" },
    new Row() { Col1 = "a", Col2="x" },
    new Row() { Col1 = "a", Col2="y" },
};

I know you can group these using lambda expressions:

foreach (var grp in collection.GroupBy(item => new { item.Col1, item.Col2 }))
{
    Debug.Write("Grouping by " + grp.Key.Col1 + " and " + grp.Key.Col2 + ": ");
    Debug.WriteLine(grp.Count() + " rows");
}

This groups correctly as you can see:

Grouping by a and x: 2 rows
Grouping by a and y: 1 rows

But now, say I receive a collection of selectors to group against, that is passed to me as a parameter in my method, and that the entity type is generic:

void doLinq<T>(params Expression<Func<T,object>>[] selectors)
{
    // linq stuff
}

Whoever's invoking the method would call like this:

doLinq<Row>(entity=>entity.Col1, entity=>entity.Col2);

How would I construct the group-by expression?

foreach (var grp in collection.GroupBy(
      item => new { 
          // selectors??
      }))
{
    // grp.Key. ??
}

Edit

I updated above to hopefully clarify why I need the set of selectors.

Edit #2

Made the entity type in doLinq generic.

Answer 1

You should look at Dynamic Linq: http://blogs.msdn.com/b/mitsu/archive/2008/02/07/linq-groupbymany-dynamically.aspx

Answer 2

Well, I'll assume you use linq-to-sql or something similar, so you need expression trees. If not there might be other possibilities.

Possible solutions I can see:

• dynamic linq

see Vladimir Perevalovs answer.

• constructing the whole groupby expression-tree manually

see http://msdn.microsoft.com/en-us/library/bb882637.aspx

• ugly workaround

Well, that's my departement :)

untested code:

 void doLinq(params string[] selectors) // checking two expressions for equality is messy, so I used strings
     foreach (var grp in collection.GroupBy(
          item => new { 
              Col1 = (selectors.Contains("Col1") ? item.Col1 : String.Empty),
              Col2 = (selectors.Contains("Col2") ? item.Col2 : String.Empty)
              // need to add a line for each column :(
          }))
     {
          string[] grouping = (new string[]{grp.Key.Col1, grp.Key.Col2 /*, ...*/ }).Where(s=>!s.IsNullOrEmpty()).ToArray();
          Debug.Write("Grouping by " + String.Join(" and ", grouping)+ ": ");
          Debug.WriteLine(grp.Count() + " rows");
     }
 }

Answer 3

I have extremely limited knowledge of linq-to-sql, but is it really important what's inside GroupBy? Because if it is not, you can roll out your own keySelector. Anyway, I tried it with both Sql Server CE and Sql Server Express and this seems to work:

using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq.Expressions;

namespace ConsoleApplication1 {
    class Props {
        public List<object> list = new List<object>();
        public override bool Equals(object obj) {
            return Enumerable.SequenceEqual(list, (obj as Props).list);
        }
        public override int GetHashCode() {
            return list.Select(o => o.GetHashCode()).Aggregate((i1, i2) => i1 ^ i2);
        }
    }
    class Program {
        static void Main(string[] args) {
            Lol db = new Lol(@"Data Source=.\SQLExpress;Initial Catalog=Lol;Integrated Security=true");
            db.Log = Console.Out;
            doLinq(db.Test, row => row.Col1, row => row.Col2);
            Console.ReadLine();
        }
        static void doLinq<T>(Table<T> table, params Func<T, object>[] selectors) where T : class {
            Func<T, Props> selector = item => {
                var props = new Props();
                foreach (var sel in selectors) props.list.Add(sel(item));
                return props;
            };
            foreach (var grp in table.GroupBy(selector)) {
                Console.Write("Grouping by " + string.Join(", ", grp.Key.list) + ": ");
                Console.WriteLine(grp.Count() + " rows");
            }
        }
    }
}

Lol database has one table "Test" with three rows. The output is this:

SELECT [t0].[Col1], [t0].[Col2]
FROM [dbo].[Test] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

Grouping by a, x: 2 rows
Grouping by a, y: 1 rows

I checked the queries, and it seems that linq-to-sql is smart enough not to generate sql for groupBy when it can't, so it will iterate through all rows of the table and then group them on the client.

Edit: minor additions for completion's sake, and the connection string now assumes Sql Server Express.

Answer 4

The solution worked for me. It involves two parts:

• create a grouping object (which I implemented inelegantly as object[]) given the row value and the set of selectors. This involves a lambda expression that compiles and invokes each selector on the row item.
• implement IEquality for the grouping object type (in my case that's IEqualityComparer).

First part

foreach (System.Linq.IGrouping<object[], T> g in collection.GroupBy(
    new Func<T, object[]>(
        item => selectors.Select(sel => sel.Compile().Invoke(item)).ToArray()
    ),
    new ColumnComparer()
)
{ ... }

Second Part

public class ColumnComparer : IEqualityComparer<object[]>
{
    public bool Equals(object[] x, object[] y)
    {
        return Enumerable.SequenceEqual(x, y);
    }

    public int GetHashCode(object[] obj)
    {
        return (string.Join("", obj.ToArray())).GetHashCode();
    }
}

This works for basic Linq, and Linq for the MySql connector. Which other Linq providers, and which expression types this works for is a whole other question ...