Not Exists using Linq

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.

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

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