开发者

LINQ - How can I use DataSet.DataRelation to join these tables and sum one field?

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("Table1开发者_开发问答Table2", 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

Expected output:

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")))
};
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜