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.

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