Unable to Convert SQL to LINQ

Question

0.00/5 (No votes)

See more:

I am new to LINQ. I have a folowing SQL query and I am trying to get LINQ equivalent statement.

SQL

SELECT orgNm.OrgNm
FROM OrgNm orgNM 
INNER JOIN ProspectClient pc
ON orgNM.OrgNmTypeId = 2
AND pc.ProspectClientID = orgNm.OrgId
AND orgNm.OrgId NOT IN (SELECT CloneOrgId FROM ClonedOrgInfo)
AND orgnm.orgId NOT IN (SELECT RelatedOrgId FROM OrgcRlshp)

Thanks...

AJ

[Edit]Code block added, 'SQL' tag added[/Edit]

Posted 8-Jan-13 6:22am

Jaydev.A

Updated 8-Jan-13 6:27am

Thomas Daniels

v2

Add a Solution

2 solutions

Solution 3

Thanks a ton, Zoltán Zörgő... Your solution solves my question!

As I am new to LINQ, could you suggest me a better approach/knowledge base where I can leverage my LINQ coding skills?

Regards
AJ

Posted 8-Jan-13 9:02am

Jaydev.A

Comments

Zoltán Zörgő 8-Jan-13 15:24pm

You are welcome, but you have added a comment as an answer to the question. You should have used the "Have a question or a comment?" button...
Anyway: have a look here: http://www.linqpad.net/ This is a great tool not only to learn, but also for productive tasks. And it has several built-in samples. Just play with it!

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)

**Zoltán Zörgő** · Accepted Answer · 2013-01-08T07:27:00

Let's clarify, just because you add something in the JOIN ON clause, does not mean it belongs there. Your query should be this one:

SQL

SELECT orgNm.OrgNm
FROM OrgNm orgNM
INNER JOIN ProspectClient pc ON pc.ProspectClientID = orgNm.OrgId
WHERE orgNM.OrgNmTypeId = 2
AND orgNm.OrgId NOT IN (SELECT CloneOrgId FROM ClonedOrgInfo)
AND orgnm.orgId NOT IN (SELECT RelatedOrgId FROM OrgcRlshp)

Here is one possible interpretation in three steps:

C#

var re = from o in OrgNm
         join pc in ProspectClient on o.OrgId = pc.ProspectClientID
         where o.OrgNmTypeId = 2;

var ex = ClonedOrgInfo.Select(x => x.CloneOrgId).Union(OrgcRlshp.Select(y => y.RelatedOrgId));

var result = re.Where(r => !ex.Contains(re.OrgId));

Since LINQ to SQL is using deferred execution, it does not matter how many steps you use.