CODEWITHSUNDEEP
c#sqlasp.net-mvclinq
josue Daniel Martínez noyola
josue Daniel Martínez noyola

Reputation: 21

How to do this query in Linq?

The query is as follows

var consulta = db.Alumnos.From(SELECT dbo.Alumnos.IdAlumno, Nombres + ' ' + Apellido1 + ' ' + Apellido2 AS Alumno FROM dbo.SolicitudCurso INNER JOIN dbo.Alumnos ON dbo.SolicitudCurso.IdAlumno = dbo.Alumnos.IdAlumno INNER JOIN dbo.Asistencia ON dbo.Asistencia.IdCurso = dbo.SolicitudCurso.IdCurso WHERE dbo.SolicitudCurso.IdCurso = id  AND dbo.Alumnos.IdAlumno NOT IN(SELECT dbo.Asistencia.IdAlumno FROM dbo.Asistencia WHERE dbo.Asistencia.IdDiasCurso = IdDiasCurso)");

and I need to use in Linq but I can't adapt to the syntax of Linq this is the code

 ViewBag.IdAlumno = new SelectList((from sc in db.SolicitudCursos
                                    join a in db.Alumnos on sc.IdAlumno equals a.IdAlumno
                                    join ss in db.Asistencias on sc.IdCurso equals ss.IdCurso
                                    where sc.IdCurso == id 
                                    select new
                                           {
                                               a.IdAlumno,
                                               Alumno = a.Nombres + " " + a.Apellido1 + " " + a.Apellido2
                                           }).ToList(), "IdAlumno", "Alumno");

Upvotes: 0

Views: 72

Answers (1)

StriplingWarrior
StriplingWarrior

Reputation: 156708

You're mostly there. It looks like the part you're struggling with is the NOT IN query. It's not clear where IdDiasCurso comes from, but I think something along these lines should work for you:

...
where sc.IdCurso == id 
...

Also, direct translations between LINQ and SQL are usually not as desirable. LINQ tends to be simpler and more expressive of what you really want. You can usually avoid explicit joins if your database model is set up with navigation properties. And since you're not using sc or ss in your final select, you can probably replace those joins with where or .Any() calls.

For example:

from a in db.Alumnos 
where a.SolicitudCursos.Any(sc => sc.Asistencias.Any() 
    && !db.Asistencias.Any(ss => ss.IdDiasCurso == sc.DiasCurso))
select new
{
    a.IdAlumno,
    Alumno = a.Nombres + " " + a.Apellido1 + " " + a.Apellido2
}

Upvotes: 1

Related Questions