Linq返回不同数据表的和值

本文关键字：数据表 返回 Linq | 更新日期: 2023-09-27 17:54:28

我有以下代码:-

        var distinctValues = DtSet.Tables["tblData"].AsEnumerable()
                    .Select(row => new
                    {
                        name_1 = "1",
                        name_2 = "I",
                        name_3 = row.Field<string>("EorD"),
                        name_4 = row.Field<string>("InvNo")
                    })
                    .Distinct();

如果我有另一个数据表(DtSet.Tables["tblCost"]) 2列，"InvNo"answers"Amount"，我怎么能添加第五列到我的第一段代码，以带回每个InvNo对tblCost的金额总和?如:- 

        var distinctValues = DtSet.Tables["tblData"].AsEnumerable()
                    .Select(row => new
                    {
                        name_1 = "1",
                        name_2 = "I",
                        name_3 = row.Field<string>("EorD"),
                        name_4 = row.Field<string>("InvNo")
                        name_5 = **Summed total of Amount column on tblCost per InvNo**
                    })
                    .Distinct();

Linq返回不同数据表的和值

您可以在一个查询中执行"组连接":

var distinctValues = 
    (from data in DtSet.Tables["tblData"].AsEnumerable()
     join cost in DtSet.Tables["tblCost"].AsEnumerable().Where(r=>!r.IsNull("Amount"))
        on data.Field<string>("InvNo") equals cost.Field<string>("InvNo") 
            into dc
    select new
    {
        name_1 = "1",
        name_2 = "I",
        name_3 = data.Field<string>("EorD"),
        name_4 = data.Field<string>("InvNo"),
        name_5 = dc.Sum(d=>d.Field<int>("Amount"))
    })
    .Distinct();

在SQL中执行一个查询，连接两个表并求和

SELECT
    D.EorD,
    D.InvNo,
    SUM(C.Amount) As TotalAmount
FROM
    tblData D
    LEFT JOIN tblCost C
        ON D.InvNo = C.InvNo
GROUP BY
    D.EorD, D.InvNo;

我使用左连接，以防在tblCost中没有相应的记录。

注意:我不确定EorD列。我假设InvNo在tblData中是唯一的。否则，如果tblCost中有相应的列，则应该将其包含在连接中，或者将其从GROUP BY中删除，并像MAX(D.EorD) AS EorD一样对其应用聚合函数。

您应该加入您的集合，将其分组并汇总:

        var tblDataValues = DtSet.Tables["tblData"].AsEnumerable()
                                .Select(row => new
                                {
                                    name_1 = "1",
                                    name_2 = "I",
                                    name_3 = row.Field<string>("EorD"),
                                    name_4 = row.Field<string>("InvNo")
                                })
                                .Distinct();;
        var tblCostValues = DtSet.Tables["tblCost"].AsEnumerable()
                                .Select(row => new
                                {
                                    InvNo = row.Field<string>("InvNo"),
                                    Amount = row.Field<int>("Amount"),
                                }
            );

        var joinedValue = tblDataValues.Join(tblCostValues, v => v.name_4, c => c.InvNo, (v, c) => new { c, v })
                .GroupBy(k => k.v, c => c.c)
                .Select(g => new
                {
                    g.Key.name_1,
                    g.Key.name_2,
                    g.Key.name_3,
                    g.Key.name_4,
                    name_5 = g.Aggregate(0, (s, c) => s + c.Amount)
                });
相关文章：