How can I sum a field from a grandchild table in a LINQ to DataSet where clause?

Question

I think I need to do exactly this, but instead using LINQ to DataSets because the "Transactions" table is in DB2 and there is no data context.

http://stackoverflow.com/questions/1813285/linq-query-across-three-levels-of-tables-to-generate-sum

I setup two DataRelations on the DataSet:
1. relate Categories (ds.tables[0] in my example) to Products (ds.tables[1])
2. relate Products to Transactions (ds.tables[2])

var query = from x in ds.tables[0].AsEnumerable()
    orderby x.Field<int>("Priority")
    select new {
        Name = x.Field<string>("Text"),
        Amount = //maybe using GetChildRows here???
    };

I don't really know what to do about the amount. Thanks in advance!

Answer 1

If the rows you need in the Transactions table are loaded in ds, then I think you could do this:

DataRelation relationToProducts;
DataRelation relationToTransactions;    
var query = from x in ds.tables[0].AsEnumerable()
orderby x.Field<int>("Priority")
select new {
    Name = x.Field<string>("Text"),
    Amount = x.GetChildRows(relationToProducts)
            .Sum(product => product.GetChildRows(relationToTransactions)
                    .Sum(tx => tx.Field<decimal>("Amount")))
};