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 :

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 :

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 :

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.