LINQ-如何使用DataSet.DataRelation联接这些表并求和一个字段? [英] LINQ - How can I use DataSet.DataRelation to join these tables and sum one field?

问题描述

我的LINQ查询未在下面产生预期的输出.基本上，它是由table1.alias分类并由table1.priority排序的table2.code和table2.class对应的table3.cost的总和.我在数据集中添加了两个DataRelation:

My LINQ query is not producing the expected output below. Basically, it's the sum of table3.cost corresponding to table2.code and table2.class categorized by table1.alias and ordered by table1.priority. I added two DataRelation's to the DataSet:

ds.Relations.Add("Table1Table2", ds.Tables[1].Columns("ID"), ds.Tables[2].Columns("ParentID");
ds.Relations.Add("Table2Table3", 
    new DataColumn[] { ds.Tables[2].Columns["Code"], ds.Tables[2].Columns["Class"] },            
    new DataColumn[] { ds.Tables[3].Columns["Code"], ds.Tables[3].Columns["Class"] });

var query = from aliases in table1.AsEnumerable()
    join children in table2.AsEnumerable() on aliases("ID") equals children("ParentID")
    orderby aliases("priority")
    select new
    {
        Name = aliases("alias"),
        Cost = 
            from data in table3.AsEnumerable()
            group data by new { code = data("code"), classcode = data("class") }
            into datatemp
            select new
            {
                cost = datatemp.Sum(x => x("cost"))
            }
    };

有什么主意我做错了吗?预先感谢！

Any ideas what I'm doing wrong? Thanks in advance!

table1: list of aliases and priorities
-----------------
alias   priority
alias1  1
alias2  2
alias3  4
alias4  3

table2: children records joined to table1 by ParentID (1-to-many)
-----------------
code  class   ParentID
code1 class1  1
code2 class2  1
code3 class3  2
code4 class4  4

table3: data, joined to table2 by class and code (1-to-many)
-----------------
code  class   cost
code1 class1  1.00
code1 class1  10.00
code1 class1  26.00
code2 class2  5.00
code2 class2  0.00
code3 class3  1000.00

预期输出:

alias1 42.00
alias2 1000.00
alias4 0.00
alias3 0.00

推荐答案

var query = from x in Table1.AsEnumerable()
orderby x.Field<int>("Priority")
select new {
    Name = x.Field<string>("alias"),
    TotalCost = x.GetChildRows("Table1Table2")
            .Sum(c => c.GetChildRows("Table2Table3")
                    .Sum(tx => tx.Field<decimal>("cost")))
};

这篇关于LINQ-如何使用DataSet.DataRelation联接这些表并求和一个字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！