Convert SQL resquest to C# lambda Expression

Question

I am trying to convert a sql request to a lambda expression but I only know how to do it with a where statement. This is my request :

SELECT Projet.ProjetId, Projet.Libelle, UtilisateurInProjet.UtilisateurId
FROM Projet INNER JOIN
 UtilisateurInProjet ON Projet.ProjetId = UtilisateurInProjet.ProjetId
WHERE (UtilisateurInProjet.UtilisateurId = @UtilisateurId)

and @UtilisateurId would be the selected value from the DropDownList in the view.

In my controller, I have this code :

 public JsonResult GetProjsName(int id)
 {
 db.Configuration.ProxyCreationEnabled = false;
 List<Projet> liprojs = db.Projets.Where(x => x.ProjetId == id).ToList();
 return Json(liprojs, JsonRequestBehavior.AllowGet);
 }

and "id" is the selected value from the DropDownList in the view. Thank you

• DarkSquirrel42: your code looks like you are using EntityFramework... is that your ORM?
• jdweng: See msdn : code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b
• Serg: Possible duplicate of C# Joins/Where with Linq and Lambda
• Kamil: @DarkSquirrel42 yes i am using EntityFramework and my ORM too
• DarkSquirrel42: please show your Entities Project and UtilisateurInProjet ... did you set up a navigation property for the relation you want to use?

Answer 1

Give this a try. I find it easier to use Linq query syntax for joins, rather than Linq extensions.

var liprojs = (from p in db.Projets
 join uip in db.UtilisateurInProjet on p.ProjetID equals uip.ProjetID
 where uip.UtilisateurId == utilisateurId 
 select new {p.ProjetId, p.Libelle, uip.UtilisateurId}).ToList();

• Kamil: I understand much better this syntax. Thank you for your help.
• Richard Boyce: No problem Kamil

Answer 2

Is this you want,

public JsonResult GetProjsName(int id)
{
 db.Configuration.ProxyCreationEnabled = false;
 List<Projet> liprojs = db.Projets.Join(db.UtilisateurInProjet,projet=> projet.ProjetId,utilisateurInProjet => utilisateurInProjet.ProjetId,(projet,utilisateurInProjet) => new {projet.ProjetId, projet.Libelle, utilisateurInProjet.UtilisateurId} ).Where(utilisateurInProjet.UtilisateurId==id).ToList();
 return Json(liprojs, JsonRequestBehavior.AllowGet);
}

Answer 3

since your navigation properties remain unknown this is more or less a wild guess...

public JsonResult SomeMethod(int id)
 {
 db.Configuration.ProxyCreationEnabled = false;
 return Json(db.UtilisateurInProjet.Where(x=>x.id==id).SelectMany(x=>x.Projects.Select(p=>new { ProjetId=p.ProjetId, Libelle=p.Libelle, UtilisateurInProjet=x.UtilisateurId})).ToList(), JsonRequestBehavior.AllowGet);
 }