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

your code looks like you are using EntityFramework... is that your ORM? — DarkSquirrel42, Jul 27 '17 at 10:03
See msdn : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Jul 27 '17 at 10:04
Possible duplicate of [C# Joins/Where with Linq and Lambda](https://stackoverflow.com/questions/2767709/c-sharp-joins-where-with-linq-and-lambda) — Serg, Jul 27 '17 at 10:05
@DarkSquirrel42 yes i am using EntityFramework and my ORM too — Kamil, Jul 27 '17 at 10:06
please show your Entities Project and UtilisateurInProjet ... did you set up a navigation property for the relation you want to use? — DarkSquirrel42, Jul 27 '17 at 10:08
Yes everthing is working just fine, i'm just want to know how to convert the sql to the lambda expression. I tried to display data with the WHERE clause and it is working. — Kamil, Jul 27 '17 at 10:10

score 0 · Answer 1 · answered Jul 27 '17 at 10:17

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);

}

score 0 · Answer 2 · answered Jul 27 '17 at 10:18

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);

    }

score 0 · Accepted Answer · answered Jul 27 '17 at 10:47

0

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();

answered Jul 27 '17 at 10:47

Richard Boyce

413
5
12

I understand much better this syntax. Thank you for your help. – Kamil Jul 27 '17 at 11:06
No problem Kamil – Richard Boyce Jul 27 '17 at 11:29

Convert SQL resquest to C# lambda Expression

3 Answers3