Click here to Skip to main content
13,000,324 members (77,419 online)
Rate this:
Please Sign up or sign in to 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?
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
Updated 6-Jan-13 5:05am
Ravi Bhavnani 6-Jan-13 11:30am
Does this Linq link help?
Rate this: bad
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 <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,[^]
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...
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 ...
Rate this: bad
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)[^]

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.170624.1 | Last Updated 7 Jan 2013
Copyright © CodeProject, 1999-2017
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