Click here to Skip to main content
15,868,340 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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?
SQL
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
Updated 6-Jan-13 5:05am
v2
Comments
Ravi Bhavnani 6-Jan-13 11:30am    
Does this Linq link help?

http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

C#
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 <identifier>” 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[^]
 
Share this answer
 
v2
Comments
aswathy.s.88 7-Jan-13 9:29am    
Sorry... It's not working .... getting some errors ....
Tharaka MTR 7-Jan-13 10:37am    
Could you please send me the error you got?
aswathy.s.88 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 7-Jan-13 12:08pm    
please check my answer, I have added all the information you need
aswathy.s.88 7-Jan-13 23:41pm    
Thank you very much ...
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)[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900