0

I am wondering if anyone can help me. I am new to LINQ and still trying to understand how it fits together.

I have a simple left join query in SQL, returning all transactions that are on table 1 that do not exist in table 2. Both tables are identical in structure.

SELECT Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.DealReference = Table2.DealReference
WHERE (((Table2.DealReference) Is Null));

Can someone please guide me, how to achieve the same in LinQ.

I am using the following DataTables:

    Dim currentDataTable = _DataTable1.AsEnumerable
    Dim previousDataTable = _DataTable2.AsEnumerable

I am looking to have the results output back into a datatable.

Thanks

BM

Brian
  • 31
  • 1
  • 4
  • 1
    You can read : http://stackoverflow.com/questions/267488/linq-to-sql-multiple-left-outer-joins. – Nianios Nov 07 '12 at 15:40

1 Answers1

0

1) Linq-To-DataTable is a subset of Linq-To-Objects and assumes that the data is already in memory. So if you want a scalable solution you might want to have a look at Linq-To-Sql which queries the database directly.

2) Your join is not a simple left join but a left-outer join. You can achieve that for example with this query:

Dim rows = From r1 In _DataTable1
           Group Join r2 In _DataTable2
           On r1.Field(Of String)("DealReference") Equals r2.Field(Of String)("DealReference")
           Into DataGroup = Group
           From row In DataGroup.DefaultIfEmpty
           Where row Is Nothing
           Select r1
Dim tblresult = rows.CopyToDataTable()
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Hey, Thanks for the reply. Both DataTables have already been filled and are in memory so this solution looks relevant. Im stepping through your code, however, I am getting an error at the following part after looping through a few times. Where row.IsNull("DealReference") Table 1 has 3 transactions in it "Brian1", "Brian2" and "Brian3". Table 2 has 2 transactions in it. "Brian1" and "Brian2". It looks like it falls over when it gets to the 3rd entry. – Brian Nov 07 '12 at 15:59
  • @Brian: Edited my answer. `Where row Is Nothing` should work. – Tim Schmelter Nov 07 '12 at 16:03
  • Still getting error "Object reference not set to an instance of an object." – Brian Nov 07 '12 at 16:17
  • Hi Can anyone help? Thanks – Brian Nov 08 '12 at 09:29