Click here to Skip to main content
Rate this: bad
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 5:50am
Edited 6-Jan-13 6:05am
Ravi Bhavnani at 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 ” 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 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...
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
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
0 OriginalGriff 295
1 DamithSL 265
2 CPallini 235
3 Maciej Los 190
4 Sergey Alexandrovich Kryukov 174
0 OriginalGriff 5,415
1 DamithSL 4,422
2 Maciej Los 3,820
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,911

Advertise | Privacy | Mobile
Web03 | 2.8.141216.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