Click here to Skip to main content
Rate this: bad
good
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
ProgramFOX127.9K
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 at 8-Jan-13 13:33pm
   
Good answer, +5!
Zoltán Zörgő at 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ő at 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
0 OriginalGriff 490
1 Maciej Los 305
2 Richard MacCutchan 270
3 Mathew Soji 220
4 BillWoodruff 210
0 OriginalGriff 8,834
1 Sergey Alexandrovich Kryukov 7,477
2 DamithSL 5,689
3 Maciej Los 5,329
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411028.1 | Last Updated 8 Jan 2013
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