Click here to Skip to main content
12,079,391 members (26,367 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: LINQ SQL
I am new to LINQ. I have a folowing SQL query and I am trying to get LINQ equivalent statement.
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 7:22am
Edited 8-Jan-13 7:27am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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:
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:
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.
  Permalink  
v2
Comments
ProgramFOX 8-Jan-13 13:33pm
   
Good answer, +5!
Zoltán Zörgő 8-Jan-13 13:36pm
   
Thank you.
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
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!

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 | Mobile
Web01 | 2.8.160212.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2016
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