Sub-Select Using LinqToExcel

Question

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?

score 2 · Accepted Answer · answered Feb 05 '12 at 02:24

2

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

answered Feb 05 '12 at 02:24

Paul

18,349
7
49
56

I'd suggest that too (as commented above), but maybe you should define an extra variable for the list and then do the join, because that might save both memory and processing time. – jCoder Feb 05 '12 at 10:55

score 1 · Answer 2 · answered Feb 04 '12 at 16:46

1

Try a join:

var credits = from credit in this.test.WorksheetNoHeader()
  join debit in this.test.WorksheetNoHeader() on credit[6] equals debit[6]
  where debit[13] != "0.00"
  where debit[13] == credit[15]
  select credit

For more information: http://msdn.microsoft.com/en-us/library/bb311040.aspx

answered Feb 04 '12 at 16:46

eliah

2,267
1
21
23

LinqToExcel doesn't support join :( – backdesk Feb 04 '12 at 17:25
Ack, my bad. Was just giving a generic LINQ answer -- I didn't even know LinqToExcel existed before seeing your question. – eliah Feb 04 '12 at 18:09
It's cool. I think I need to try another library and use your example :) – backdesk Feb 04 '12 at 18:12
Depending on the size of your worksheet data you could do something like `List paymentList = this.test.WorksheetNoHeader().ToList();` and you'll get a "materialized" list of the entries, which fully supports JOIN (because it's a .NET framework class). – jCoder Feb 04 '12 at 18:16
Why don't you do what was suggested above, to add `toList()`? This works, I tried it. – user890332 Feb 07 '12 at 13:50

Sub-Select Using LinqToExcel

2 Answers2