Linq - Grouping with sum calculation

Question

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

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.

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.

Example:

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

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

---

What's the best way to do this?

Answer 1

When I run this query I failed in visual studio. On More search found the following and worked well in my system.

var result = (from row in rulestable.AsEnumerable() 
             group row by new {sup= row.Field<string>("Supplier"), 
                              vat=row.Field<decimal>("VatRate")} into grp = Group 
             select new With 
             {.Supplier = 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  = vat
              }
             ).ToList();

Answer 2

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.