Linq - Left Outer Join Two Datatables

Question

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 7-Nov-12 23:46pm

mccrimmon

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Greysontyrus · Answer 1 · 2012-11-19T00:15:00

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 ^_^