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?

Solution 1

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

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

Comments

[sports]

You are a genius! I will try this and accept the answer in case it works

[Robert McKee]

You could probably shorten this as well by combining the first select and groupby if you want to as well, but I think this is more readable, but that's very subjective.

[M.kazem Akhgary]

oh ok. i forgot null conditional. i delete my answer xD

[sports]

man, it works like a charm! I had some id's too, so if anyone's interested: Category = includeCategory ? r.Category : null, CategoryId = includeCategory ? r.CategoryId : (long?)null -- there is that (long?)null needed

[Robert McKee]

This is an excellent solution if you need a dynamic result. I typically try to avoid that wherever possible, but sometimes you just can't, and that is where this answer is perfect.

[sports]

Why is it better than your answer?