LINQ-组/求和多列 [英] LINQ - group/sum multiple columns

问题描述

Data是一个本地CSV文件，已通过OleDB加载到ado.net数据集中.该表有40多个由发票明细组成的列.每行是发票中的一个单独的行项目，可以包含1到n行.

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)),
                } );

推荐答案

实际上，当您使用invoiceTotals的结果时，您只会执行一个查询.在显示的代码中，您甚至不对数据库进行查询.

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推迟执行"，很漂亮；-)

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

但是正如Uriil所说的，您可以将语句合并为一个linq查询:

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)),
            } 
 );

这篇关于LINQ-组/求和多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！