Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: LINQ VB.NET DataTable
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.
 
Thanks
 
BM
Posted 8-Nov-12 0:46am

1 solution

Rate this: bad
good
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:
SELECT *
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)
                       .Contains(t1.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 ^_^
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 290
1 Jochen Arndt 150
2 Kornfeld Eliyahu Peter 149
3 PIEBALDconsult 110
4 Richard MacCutchan 105
0 OriginalGriff 6,080
1 DamithSL 4,648
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,624
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2014
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