Click here to Skip to main content
14,978,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

VB
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:

VB
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

1 solution

Bit rusty on my VB syntax but here goes

first off, there is a better way to put your SQL:
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
VB
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 ^_^
   

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900