Linq Sum with group by

Question

I am having a table structure with columns

FeesNormal

FeesCustom

Currency

Now i am looking for a SUM function group by currency .

For example 20 USD + 30 EURO + 40 INR something like this from this table

I also have to consider the scenario if FeesCustom > 0 I have to ignore FeesNormal for the row

Sample date and expected result is like this

  FeesNormal  FeesCustom  Currency
  10          0            USD   
  15          25           USD //in this case can ignore FeesNormal Since FeesCustom is more
  5           10           EUR //same for this row ignore FeesNormal
  10          0            EUR

Expected result  35 USD 20 EUR   

I able to find sum using the linq

 int sum_custom=(int)fee_list.Where(p => p.FeesCustom > 0).Sum(p => p.FeesCustom);
 int sum_normal = (int)fee_list.Where(p => p.FeesCustom ==0).Sum(p => p.FeesNormal);

Answer 1

It seems to me that you just need a projection from "entry" to "effective fee" which you can sum - something like:

var result = source
    .GroupBy(x => x.Currency)
    .Select(g => new {
        Currency = g.Key,
        Total = g.Sum(x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal)
    });

That's equivalent to:

var result = source
    .GroupBy(x => x.Currency,
             (key, values) => new {
                Currency = key,
                Total = values.Sum(x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal)
             });

Or do the transformation earlier:

 var result = source
     .Select(x => new {
         x.Currency,
         x.Fee = x => x.FeesCustom > 0 ? x.FeesCustom : x.FeesNormal
     })
     .GroupBy(x => x.Currency, x => x.Fee,
              (key, values) => new { Currency = key, Fee = values.Sum() });

Answer 2

Assuming you have DataTable with the mentioned data, you could do this using Linq

var result = table.AsEnumerable()        
     .GroupBy(x=> x.Field<string>("Currency"))
     .Select(x=> new
      {
          Currency = x.Key,
          Value = x.Sum(s=> Math.Max(s.Field<double>("FeesNormal"), s.Field<double>("FeesCustom "))),

      }      
      .ToList()

Answer 3

Using Query Syntax:

var feeResult = (from fee in fee_list
                group fee by fee.Currency into groupResult
                select new
                {
                    Currency = groupResult.Key,
                    FinalFees = groupResult.Sum(f => f.FeesCustom > 0 ? f.FeesCustom : f.FeesNormal)
                }).ToList();