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

Question

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

Answer 1

I have a query that almost works. The output is slightly off--"alias3" is duplicated with one "lineitem" with cost = 0 (see output below). class lineitem has public members, Order, Text and cost.

var querytest = ((from aliases in Table1.AsEnumerable()
          select new mylineitem
          {
              Order = aliases.Field<int>("Priority")
              Text = aliases.Field<string>("alias"),
              cost = aliases.GetChildRows("Table1Table2")
                  .Where(codes => codes.Field<string>("class") != "*")
                      .Sum(codes => codes.GetChildRows("Table2Table3")
                          .Sum(data => data.Field<decimal>("cost")))
          }).Concat(from aliases in Table1.AsEnumerable()
                    join codes in Table2.AsEnumerable()
                        on aliases.Field<int>("ID") equals codes.Field<int>("ParentID")
                    join data in Table3.AsEnumerable()
                        on codes.Field<string>("code") equals data.Field<string>("code") into temp
                    where codes.Field<string>("class") == "*"
                    select new mylineitem
                    {
                        Order = aliases.Field<int>("Priority"),
                        Text = aliases.Field<string>("alias"),
                        cost = temp.Sum(x => x.Field<decimal>("cost"))
                    })).OrderBy(a => a.Order);

        foreach (var lineitem in querytest)
        {
            Console.WriteLine(lineitem.Text + " " + lineitem.cost);
        }

output:

alias1 42
alias2 1000
alias4 0
alias3 0 --this line should not be here.
alias3 51

Answer 2

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.