Linq-使用总和计算进行分组 [英] Linq - Grouping with sum calculation

问题描述

Linq菜鸟在这里.我该如何纠正?

Linq noob here. How do I correct this?

                var result = (from row in rulestable.AsEnumerable()
                         let sup = row.Field<int>("Supplier")
                         let amo = row.Field<decimal>("Amount")
                         let adv = row.Field<decimal>("Advance")
                         let bal = row.Field<decimal>("Balance")
                         let vat = row.Field<decimal>("VatRate")
                         group row by new { sup, vat } into grp
                         select new
                         {
                             Supplier = grp.Key.sup,
                             Amount = grp.Sum(grp => grp["amo"]),
                             Advance = grp.Sum(grp => grp["adv"]),
                             Balance = grp.Sum(grp => grp["bal"]),
                             VatRate = grp.Key.vat
                         }).ToList();

我想将我的数据表"rulestable"分为两列，供应商和VatRate.结果的金额，预付款和余额必须设置为分组的所有列的总和.

I want to group my datatable "rulestable" on 2 columns, Supplier and VatRate. The Amount, Advance and Balance of the result must be set to the sum of whatever columns were grouped.

我收到一个错误-grp.Sum上的无法从用法中推断出类型参数".但是，我认为此表达式是错误的-我认为它将对结果列求和，而不是像我想要的那样对源列求和.

I get an error - "the type arguments cannot be inferred from the usage" on grp.Sum. However, I think this expression is all wrong - I think it will sum the result columns and not the source columns like I want.

示例:

输入数据表:
供应商，金额，预付款，余额，增值税率
S1、10、5、5、1
S1、10、5、5、1

Input DataTable:
Supplier, Amount, Advance, Balance, VatRate
S1, 10, 5, 5, 1
S1, 10, 5, 5, 1

预期输出:
S1、20、10、10、1

Expected output:
S1, 20, 10, 10, 1

做到这一点的最佳方法是什么?

What's the best way to do this?

推荐答案

在lambda内，grp(我将其重命名为r以便与行本身区分开来)将成为DataRow .编译器不知道返回什么数据类型grp [string]，因为它只返回object.试试这个:

Inside the lambda, grp (which I've renamed r for row to distinguish from the group itself) is going to be a DataRow. The compiler doesn't know what datatype grp[string] returns, since it just returns an object. Try this:

DataTable rulestable = new DataTable();
rulestable.Columns.Add("Supplier");
rulestable.Columns.Add("Amount", typeof(decimal));
rulestable.Columns.Add("Advance", typeof(decimal));
rulestable.Columns.Add("Balance", typeof(decimal));
rulestable.Columns.Add("VatRate", typeof(decimal));
rulestable.Rows.Add("S1", 10M, 5M, 5M, 1M);
rulestable.Rows.Add("S1", 10M, 5M,  5M, 1M);

var result = (from row in rulestable.AsEnumerable()
              let sup = row.Field<string>("Supplier")
              let amo = row.Field<decimal>("Amount")
              let adv = row.Field<decimal>("Advance")
              let bal = row.Field<decimal>("Balance")
              let vat = row.Field<decimal>("VatRate")
              group row by new { sup, vat } into grp
              select new
              {
                  Supplier = grp.Key.sup,
                  Amount   = grp.Sum(r => r.Field<decimal>("Amount")),
                  Advance  = grp.Sum(r => r.Field<decimal>("Advance")),
                  Balance  = grp.Sum(r => r.Field<decimal>("Balance")),
                  VatRate  = grp.Key.vat
              }).ToList();

Input:
Supplier | Amount | Advance | Balance | VatRate
-----------------------------------------------
   S1    |   10   |    5    |    5    |    1
-----------------------------------------------  
   S1    |   10   |    5    |    5    |    1

Result:
Supplier | Amount | Advance | Balance | VatRate
-----------------------------------------------
   S1    |   20   |    10   |    10   |    1

使用您提供的输入，可以得到上面预期的结果.

Using the input you supplied, this gives the results you expected above.

这篇关于Linq-使用总和计算进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！