LINQ only sum rows if columns totaled is not zero

Question

Given the following rows:

Amount, Name, Id
   Scenario 1: 100.00,ABC,101
              -100.00,ABC,101

   Scenario 2: 50.00,XYZ,123
              -100.00,XYZ,123

I want to sum and group the rows only if the the amount does not totaled to 0.00 amount. So the Linq query should return this:

Amount, Name, Id
Scenario 1: 100.00,ABC,101
           -100.00,ABC,101

Scenario 2:-50.00,XYZ,123

What I have so far:

var results = dt.AsEnumerable().GroupBy(row => new
{
   Name = row.Field<string>("NAME"),
   Id = row.Field<int>("ID")
}).Select(grp =>
{
   DataRow dr = dt.NewRow();
   dr["AMOUNT"] = grp.Sum(r => r.Field<decimal>("AMOUNT"));
   dr["NAME"] = grp.Key.Name;
   dr["ID"] = grp.Key.Id;
   return dr;
}).CopyToDataTable();

Answer 1

You could try the following query using SelectMany extension method:

var query= dt.AsEnumerable().GroupBy(row => new
                             {
                                Name = row.Field<string>("NAME"),
                                Id = row.Field<int>("ID")
                             })
                            .SelectMany(grp=>
                             { 
                                var sum=grp.Sum(r => r.Field<decimal>("AMOUNT");
                                if(sum!=0)
                                {
                                   DataRow dr = dt.NewRow();
                                   dr["AMOUNT"] = sum;
                                   dr["NAME"] = grp.Key.Name;
                                   dr["ID"] = grp.Key.Id;
                                   return dr;
                                }
                                else
                                {
                                  return grp;
                                }
                             }).CopyToDataTable();  

Answer 2

It's difficult to understand what you're asking, so I'm assuming that you mean:

Sum and group rows, so only a single summarised transaction is listed for any given ID, unless the total is zero, then list all of the transactions for that ID.

Here's a working example, with the test data you provided:

var amounts = new[]
{
   new 
   {
       Amount = 100.00m,
       Name = "ABC",
       Id = 101,
   },
   new
   {
       Amount = -100.00m,
       Name = "ABC",
       Id = 101,
   },
   new
   {
       Amount = 50.00m,
       Name = "XYZ",
       Id = 123,
   },
   new
   {
       Amount = -100.00m,
       Name = "XYZ",
       Id = 123,
   },
};

// summarise everything
var summaries = from a in amounts
                group a by new { a.Id, a.Name } into grouping
                select new
                {
                    Amount = grouping.Sum(g => g.Amount),
                    grouping.Key.Name,
                    grouping.Key.Id,                    
                };

// get the ids of records we need the full audit log for
var zeroSummaries = summaries.Where(s => s.Amount == 0).Select(s => s.Id).ToList();

// concat the summarised records together with the ones we need the full audit log for
summaries = amounts.Where(a => zeroSummaries.Contains(a.Id))
                   .Concat(summaries.Where(s => s.Amount != 0));

Here's the output: