LINQ GroupBy with a dynamic group of columns

Question

I have a table like this:

 variety category  quantity
----------------------------------------------
  rg      pm         10
  gs      pm          5
  rg      com         8

I want to make a GroupBy based on these bool parameters:

• IncludeVariety
• IncludeCategory

eg:

IncludeVariety = true;
IncludeCategory = true;

would return this:

 variety category  quantity
----------------------------------------------
  rg      pm         10
  gs      pm          5
  rg      com         8

and this:

IncludeVariety = true;
IncludeCategory = false;

would return this:

  variety category  quantity
----------------------------------------------
    rg      -         18
    gs      -          5

and this:

IncludeVariety = false;
IncludeCategory = true;

would return this:

  variety category  quantity
----------------------------------------------
     -      pm         15
     -      com         8

You get the idea...

Question: How can I achieve this with LINQ?

Important: I've reduced the problem to two bool variables (IncludeVariety and IncludeCategory) but in reality I will be having more columns (say five)

I can't figure out how to generate the query dynamically (the .GroupBy and the .Select):

 rows.GroupBy(r => new { r.Variety, r.Category })
 .Select(g => new 
  {
        Variety = g.Key.Variety,
        Category = g.Key.Category,
        Quantity = g.Sum(a => a.Quantity),
  });

 rows.GroupBy(r => new { r.Category })
 .Select(g => new 
  {
        Variety = new {},
        Category = g.Key.Category,
        Quantity = g.Sum(a => a.Quantity),
  });

 rows.GroupBy(r => new { r.Variety })
 .Select(g => new 
  {
        Variety = g.Key.Variety,
        Category = new {},
        Quantity = g.Sum(a => a.Quantity),
  });

A similar thing I've done in the past is concatenate Where's, like:

  var query = ...

  if (foo) {
       query = query.Where(...)
  }

  if (bar) {
       query = query.Where(...)
  }

  var result = query.Select(...)

Can I do something like that here?

Answer 1

In any case someone still needs grouping by dynamic columns without Dynamic LINQ and with type safety. You can feed this IQueryable extension method an anonymous object containing all properties that you might ever want to group by (with matching type!) and a list of property names that you want to use for this group call. In the first overload I use the anonymous object to get it's constructor and properties. I use those to build the group by expression dynamically. In the second overload I use the anonymous object just for type inference of TKey, which unfortunately there is no way around in C# as it has limited type alias abilities.
Does only work for nullable properties like this, can probably easily be extended for non nullables but can't be bothered right now

public static IQueryable<IGrouping<TKey, TElement>> GroupByProps<TElement, TKey>(this IQueryable<TElement> self, TKey model, params string[] propNames)
{
    var modelType = model.GetType();
    var props = modelType.GetProperties();
    var modelCtor = modelType.GetConstructor(props.Select(t => t.PropertyType).ToArray());

    return self.GroupByProps(model, modelCtor, props, propNames);
}

public static IQueryable<IGrouping<TKey, TElement>> GroupByProps<TElement, TKey>(this IQueryable<TElement> self, TKey model, ConstructorInfo modelCtor, PropertyInfo[] props, params string[] propNames)
{
    var parameter = Expression.Parameter(typeof(TElement), "r");
    var propExpressions = props
        .Select(p =>
        {
            Expression value;

            if (propNames.Contains(p.Name))
                value = Expression.PropertyOrField(parameter, p.Name);
            else
                value = Expression.Convert(Expression.Constant(null, typeof(object)), p.PropertyType);

            return value;
        })
        .ToArray();

    var n = Expression.New(
        modelCtor,
        propExpressions,
        props
    );

    var expr = Expression.Lambda<Func<TElement, TKey>>(n, parameter);
    return self.GroupBy(expr);
}

I implemented two overloads in case you want to cache the constructor and properties to avoid reflection each time it's called. Use like this:

//Class with properties that you want to group by
class Record
{
    public string Test { get; set; }
    public int? Hallo { get; set; }
    public DateTime? Prop { get; set; }

    public string PropertyWhichYouNeverWantToGroupBy { get; set; }
}

//usage

IQueryable<Record> queryable = ...; //the queryable

var grouped = queryable.GroupByProps(new
{
    Test = (string)null,        //put all properties that you might want to group by here
    Hallo = (int?)null,
    Prop = (DateTime?)null
}, nameof(Record.Test), nameof(Record.Prop));   //This will group by Test and Prop but not by Hallo

//Or to cache constructor and props
var anonymous = new
{
    Test = (string)null,        //put all properties that you might want to group by here
    Hallo = (int?)null,
    Prop = (DateTime?)null
};
var type = anonymous.GetType();
var constructor = type.GetConstructor(new[]
{
    typeof(string),             //Put all property types of your anonymous object here 
    typeof(int?),
    typeof(DateTime?)
});
var props = type.GetProperties();
//You need to keep constructor and props and maybe anonymous 
//Then call without reflection overhead
queryable.GroupByProps(anonymous, constructor, props, nameof(Record.Test), nameof(Record.Prop));

The anonymous object that you will receive as TKey will have only the Keys that you used for grouping (namely in this example "Test" and "Prop") populated, the other ones will be null.

Answer 2

If you need this to be truly dynamic, use Scott Gu's Dynamic LINQ library.

You just need to figure out what columns to include in your result and group by them.

public static IQueryable GroupByColumns(this IQueryable source,
    bool includeVariety = false,
    bool includeCategory = false)
{
    var columns = new List<string>();
    if (includeVariety) columns.Add("Variety");
    if (includeCategory) columns.Add("Category");
    return source.GroupBy($"new({String.Join(",", columns)})", "it");
}

Then you could just group them.

var query = rows.GroupByColumns(includeVariety: true, includeCategory: true);

Answer 3

var results=items
  .Select(i=>
    new {
      variety=includevariety?t.variety:null,
      category=includecategory?t.category:null,
      ...
    })
  .GroupBy(g=>
    new { variety, category, ... }, g=>g.quantity)
  .Select(i=>new {
    variety=i.Key.variety,
    category=i.Key.category,
    ...
    quantity=i.Sum()
  });

shortened:

var results=items
  .GroupBy(g=>
    new {
      variety=includevariety?t.variety:null,
      category=includecategory?t.category:null,
      ... 
    }, g=>g.quantity)
  .Select(i=>new {
    variety=i.Key.variety,
    category=i.Key.category,
    ...
    quantity=i.Sum()
  });