Search code examples
c#joinlinq-to-dataset

LINQ to DataSet - group by variable field, or join on a variable condition (with sum)


The following query works as long as I can add DataRelations. However, the relation "Table2Table3" may no longer be clearly defined, so I think I need to remove it. The old relation strictly joins on 2 string fields. The new relation exists if Table2.code = Table3.code and Table2.class = Table3.class OR Table2.code = Table3.code and Table2.class = "*" and Table3.class is anything.

How can I implement this condition and still sum Table3.cost field?

Can I use regular expressions?

I tried creating an anonymous class equality (see far below), but Table2 was no accessible on the right side of the equals clause.

var querytest = from x in ds.Tables[0].AsEnumerable()
                orderby x.Field<int>("Priority")
                select new
                {
                    Name = x.Field<string>("alias"),
                    Amount = x.GetChildRows("Table1Table2")
                           .Sum(product => product.GetChildRows("Table2Table3")
                                    .Sum(tx => tx.Field<decimal>("cost")))
                };

sample tables:

table1: list of aliases and priorities
-----------------
ID alias   priority
1  alias1  1 
2  alias2  2
3  alias3  4
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
code5 *       3
table3: data, joined on new condition
-----------------
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
code5 class6  25.00
code5 class7  26.00

expected output:

alias1 42.00
alias2 1000.00
alias4 0.00
alias3 51.00

This query can't access "codes" in the scope of the second anonymous class:

var querytest2 = from aliases in ds.Tables[0].AsEnumerable()
                 join codes in ds.Tables[1].AsEnumerable()
                 on aliases.Field<int>("ID") equals codes.Field<int>("ParentID")
                 join data in ds.Tables[2].AsEnumerable()
                 on new {
                     code = codes.Field<string>("code"), classcode = codes.Field<string>("class") } 
                 equals new { 
                     code = data.Field<string>("code"), classcode = (data.Field<string>("class") == "*" ? codes.Field<string>("class") : data.Field<string>("class") }
                 orderby aliases.Field<int>("Priority")
                 select new
                 {
                     name = aliases.Field<string>("alias"),
                     cost = //somehow sum here
                 };

Solution

  • I resorted to two foreach loops, and four separate LINQ queries. Please let me know if you find a single LINQ statement solution for this.