LINQ GroupBy与一组动态列

问题描述

我有这样一张表：

 品种类别数量
 --------- ------------------------------------- 
 rg pm 10 
 gs pm 5 
 rg com 8 
  

我想创建一个 GroupBy 基于这些 bool 参数：

• IncludeVariety


• IncludeCategory

例如：

  IncludeVariety = true; 
 IncludeCategory = true; 
  

会返回这个：

 品种类别数量
 ------------------------------------ ---------- 
 rg pm 10 
 gs pm 5 
 rg com 8 
  

和这个：

  IncludeVariety = true; 
 IncludeCategory = false; 
  

会返回这个：

 品种类别数量
 ------------------------------------ ---------- 
 rg  -  18 
 gs  -  5 
  

以及：

  IncludeVariety = false; 
 IncludeCategory = true; 
  

会返回这个：

 品种类别数量
 ------------------------------------ ---------- 
  -  pm 15 
  -  com 8 
  

问题：我如何用LINQ实现这一点？

重要：我将问题简化为两个bool变量（ IncludeVariety 和 IncludeCategory ），但实际上我会有更多的列（比如说五）

我无法弄清楚如何动态生成查询（ .GroupBy 和 .Select ）：

  rows.GroupBy（r => new {r.Variety，r.Category}）
 .Select（g => ;新
 {
 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。品种，
 Category = new {}，
 Quantity = g.Sum（a => a.Quantity），
}）; 
  

过去我所做的类似的事情是连接 Where 的，如：

  var query = ... 
 
 if（foo）{
 query = query.Where（...）
} 
 
 if（bar）{
 query = query。 （...）
} 
 
 var result = query.Select（...）
  

我可以在这里做类似的事吗？

解决方案

  var results = items $ b $ .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（）
}）; 
  

缩短：

  var results = items 
 .GroupBy（g => 
 new {
 variety = includevariety？t.variety：null，
 category = includecategory？t。类别：空，
 ... 
}，g => g.quantity）
 .Select（i => new {
 variety = i.Key.variety， 
 category = i.Key.category，
 ... 
 quantity = i.Sum（）
}）; 
  

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?

解决方案

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

这篇关于LINQ GroupBy与一组动态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！