LinQ : Join with multiple conditions
October 22, 2009 — Florent ClairambaultI 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.
