I'm trying to figure out how to perform a sub-select using Linq. I have an excel sheet with a 'debit' and a 'credit' column. I need to filter out any rows which have a debit column value (> 0.00) that matches up with a credit column value further down. Both rows must have the same payer id. Here's what I have come up with so far:
public void balanceSheet()
{
foreach (Payment payment in this.payments)
{
// c[6] is the payers ID.
var debits = from c in this.test.WorksheetNoHeader()
where c[6] != "0" && c[13] != "0.00"
select c;
// Find any rows in the sheet that have the same payer id AND the debit
// amount from the query above in it's credit column.
foreach(LinqToExcel.RowNoHeader debit in debits)
{
var credits = from c in this.test.WorksheetNoHeader()
where c[6] == debit[6] && c[15] == debit[13]
select c;
// Do something awesome if it finds something.
}
}
}
I'm hoping there's a way more elegant solution to select excel rows by the criteria above rather than looping through them each time. I don't think I am using LINQ to it's full potential here by any means. Any ideas?
LinqToExcel doesn't support joins, but you can convert it to an in-memory list and then perform the join
var credits = from credit in this.test.WorksheetNoHeader().ToList()
join debit in this.test.WorksheetNoHeader().ToList() on credit[6] equals debit[6]
where debit[13] != "0.00"
where debit[13] == credit[15]
select credit