Not Exists using Linq

Question

sorry for my english, I'll try to be short and concise.

I have to tables "ALUMNOS" and "MATERIAS". In SQL exist another table "ALUMNOS BY MATERIAS". I know this is not necessary in EF because exists the properties navigation, but I need to get the ALUMNOS that were not enrolled in any MATERIAS.

I want something like that:

   SELECT *
     FROM ALUMNOS AS A
    WHERE NOT EXISTS(
               SELECT MA.MATERIAID 
                 FROM MATERIASXALUMNO AS MA 
                WHERE A.LEGAJO = MA.LEGAJO
                  AND .MATERIAID = XXX) 

But my confusion starts because in ALUMNOS entities I have Collection of Materias and vice versa, but I haven't in Alumnos MateriaID or in Materias AlumnosID so how can I do this with linq??

To summarize, what I need is get the Alumnos that aren't enrolled in an XX Materia and the Alumnos that they are enrolled in that Materia...

thanks in advance, I hope can help me, I fix it using Stored Procedures, but I want it with Linq or at least understand how is it doing...

Regards from Argentina! Guille

Answer 1

If your navigation properties are mapped such that an Alumno.Materias gives you the collection of Materias that the Alumno is enrolled in, you will want something like this:

var missing = dbcontext.Alumnos.Where(a => !a.Materias.Any(m => m.MateriaID == XXX));

This works as long as the relationship between ALUMNOS and MATERIAS is mapped as described. It allows us to treat them like objects instead of like tables. Under the hood, it generates SQL that uses EXISTS or NOT EXISTS, and MATERIASXALUMNO and LEGAJO are used automatically as well. But the mapping lets Entity Framework abstract that away.

Answer 2

Use .Any() for Exists and you can invert it by using !Any()

//assuming that ALUMNOS and MATERIASXALUMNO have FK A.LEGAJO = MA.LEGAJO
from a in context.Alumnos
where !a.MATERIASXALUMNO.Where(ma=>ma.MATERIAID == XXX).Any()
select a

Or if not then

from a in context.Alumnos
where !context.MATERIASXALUMNO.Where(ma=>ma.MATERIAID == XXX && a.LEGAJO == ma.LEGAJO)
                              .Any()
select a

I'm using Where(predicate) and Any() just to keep it clear, but you can merge and just use Any(predicate of where) also as shown in the other post

Answer 3

Your looking something like:

var existing = (from alumno in datacontext.Alumnos
                where datacontext.Materias.Any(m => m.AlumnoID == alumno.AlumnoID)
                select alumno);

and

var missing = (from alumno in datacontext.Alumnos
                where !datacontext.Materias.Any(m => m.AlumnoID == alumno.AlumnoID)
                select alumno);