LINQ - group/sum multiple columns

Question

Data is a local CSV file that is loaded into an ado.net dataset via OleDB. The table has 40+ columns consisting of invoice details. Each row is a separate line item within an invoice, which can consist of 1 to n rows.

The query is used to group the invoice details into a single row per invoice, totaling the invoice amount and balance due.

The following works, what I'm trying to determine: Is it possible to do this in a single query?

    //group the invoices by invoicenumber and sum the total //Zoho has a separate record (row) for each item in the invoice //first select the columns we need into an anon array    var invoiceSum =     DSZoho.Tables["Invoices"].AsEnumerable()     .Select (x =>          new {               InvNumber = x["invoice number"],             InvTotal = x["item price"],             Contact = x["customer name"],             InvDate = x["invoice date"],             DueDate = x["due date"],             Balance = x["balance"],             } );     //then group and sum     var invoiceTotals =         invoiceSum         .GroupBy (s => new {s.InvNumber, s.Contact, s.InvDate, s.DueDate} )         .Select (g =>              new {                 InvNumber = g.Key.InvNumber,                 InvDate = g.Key.InvDate,                 DueDate = g.Key.DueDate,                 Contact = g.Key.Contact,                 InvTotal = g.Sum (x => Math.Round(Convert.ToDecimal(x.InvTotal), 2)),                 Balance = g.Sum (x => Math.Round(Convert.ToDecimal(x.Balance), 2)),                 } ); 

Answer 1

You are, in fact, only doing one query when you use the results of invoiceTotals. In the code you are showing you are even not doing a query on the database.

Google "linq deferred execution", it's nifty ;-)

But as Uriil says, you can just combine the statements into one linq query:

var invoiceSum = DSZoho.Tables["Invoices"].AsEnumerable() .Select (x =>      new {           InvNumber = x["invoice number"],         InvTotal = x["item price"],         Contact = x["customer name"],         InvDate = x["invoice date"],         DueDate = x["due date"],         Balance = x["balance"],         }  )  .GroupBy (s => new {s.InvNumber, s.Contact, s.InvDate, s.DueDate} )  .Select (g =>          new {             InvNumber = g.Key.InvNumber,             InvDate = g.Key.InvDate,             DueDate = g.Key.DueDate,             Contact = g.Key.Contact,             InvTotal = g.Sum (x => Math.Round(Convert.ToDecimal(x.InvTotal), 2)),             Balance = g.Sum (x => Math.Round(Convert.ToDecimal(x.Balance), 2)),             }   );