Click here to Skip to main content
13,201,934 members (72,025 online)
Rate this:
Please Sign up or sign in to vote.
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
Dim previousDataTable

I am looking to have the results output back into a datatable.
Basically only trades that exist in table 1 but do not exist in table 2.


Posted 7-Nov-12 23:46pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Bit rusty on my VB syntax but here goes

first off, there is a better way to put your SQL:
FROM Table1 t1
Where not exists (
  select * from Table2 t2 
  Where t1.DealReference = t2.DealReference

This minimises the content scanned so is much more efficient. That in mind you can create linq queries that reflect this logic
public function ByExtensions() '' .Net4.0 only
  var result = db.table1.Where(t1=>!db.table2.Any(t2=>t2.DealReference==t1.DealReference))
end function

public function NonExtensions() '' .Net3.5 and above
  var result = from t1 in db.table1
               where !(from t2 in db.table2
                       select t2.DealReference)
               select t1
end function

The inline (NonExtensions) method could well be wrong and look inefficient to me. If you are using 4.0 then stick with the ByExtentions menthod. If not then let me know and I'll have another go

Good Luck ^_^

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.171020.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100