LinQ : Join with multiple conditions

I wanted to some simple join in LinQ on multiple criteria. And coming from standard SQL it’s not as straightforward as I thought it would be. The little disturbing thing is that you lose auto-completion.

Here is my database schema :

1
2
3
4
5
6
7
8
9
10
11
12
13
User:
* UserId          int           (PK)
* Login           String
* DateOfBirth     DateTime
 
Parameter:
* ParameterId     int           (PK)
* Name            String
 
User_Parameter:
* UserId          int           (FK User:Userid)
* ParameterId     int           (FK Parameter:ParameterId)
* Value           String

If you want to fetch the “PreferredSuperHero” parameter, you have to execute :

1
2
3
4
5
6
7
8
9
10
11
12
13
var query = 
from user in Db.User
join preferredSuperHero in Db.User_Parameter on 
	new { 
		UserId = user.UserId, 
		ParamName = "PreferredSuperHero" 
	} 
	equals new { 
		UserId  = preferredSuperHero.UderId, 
		ParamName = preferredSuperHero.Parameter.Name 
	}
orderby user.Name
select new { User = user, PreferredSuperHero = preferredSuperHero };

In SQL, you would execute :

1
2
3
4
5
SELECT `u`.*, `up`.*
FROM `user` AS `u`
JOIN `Parameter` AS `p`
JOIN `User_Parameter` AS `up` ON `up`.`UserId`=`u`.`UserId` AND `up`.`ParameterId`=`p`.`ParameterId`
WHERE  `p`.`ParamName`='PreferredSuperHero'

On the LinQ Query, the creation of an anonymous type is necessary for the multiple join condition you can see on line 4.

Leave a Reply

Your email address will not be published. Required fields are marked *