Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL LINQ C#4.0
Hi there,
 
I have 3 tables
 
Contestant - Id, Name, Category
Competition - Id, Name
ContestantsInCompetition - Id, Competition, Contestant
 
How can I write c# LINQ for the following Query?
SELECT		Contst.Name,	Cat.Category	
FROM		Contestant Contst
LEFT JOIN	ContestantsInCompetition ContstComp
ON	        Contst.Id = ContstComp.Contestant4
JOIN		Category Cat
ON		Contst.Category = Cat.Id
WHERE		ContstComp.Competition IS NULL
 
Thanks in advance
Posted 6-Jan-13 4:50am
Edited 6-Jan-13 5:05am
v2
Comments
Ravi Bhavnani at 6-Jan-13 11:30am
   
Does this Linq link help?
 
http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

var query=from contest in Contestant 
	join ContstComp in ContestantsInCompetition on 
	contest.ID equals ContstComp.Contestant4 into ContestContestComp
	from ccmp in ContestContestComp.DefaultIfEmpty()
	join Cat in Category on contest.Category equals Cat.Id
	where ContstComp.Competition == null
	select new {Contst.Name,Cat.Category}
 
“Into ” serve as a reference to the results of join. It is just like ‘AS” keyword in SQL. In above query, join result set referred as “ContestContestComp”.
“DefaultIfEmpty” - returns a default instance if the collection is empty.
 
I think it is better you refer this article,
http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx[^]
  Permalink  
v2
Comments
aswathy.s.88 at 7-Jan-13 9:29am
   
Sorry... It's not working .... getting some errors ....
Tharaka MTR at 7-Jan-13 10:37am
   
Could you please send me the error you got?
aswathy.s.88 at 7-Jan-13 10:53am
   
It's better me to work out ... But could you help me explaining "into" and "DefaultIfEmpty" ... Actually how LINQ Left Join works? It's bit confusing for me though I googled and found many articles...
Thanks
Tharaka MTR at 7-Jan-13 12:08pm
   
please check my answer, I have added all the information you need
aswathy.s.88 at 7-Jan-13 23:41pm
   
Thank you very much ...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

To define the LINQ query for your case, you should post the classes which you use for the query. Meanwhile, have a look at the exmaple in How to: Perform Left Outer Joins (C# Programming Guide)[^]
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 310
1 Sergey Alexandrovich Kryukov 176
2 PhilLenoir 164
3 Richard MacCutchan 160
4 Sharmanuj 156
0 Sergey Alexandrovich Kryukov 6,081
1 OriginalGriff 5,115
2 CPallini 2,473
3 Richard MacCutchan 1,597
4 Abhinav S 1,505


Advertise | Privacy | Mobile
Web02 | 2.8.140814.1 | Last Updated 7 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