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
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.
Exists and you can invert it by using
//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
Any() just to keep it clear, but you can merge and just use
Any(predicate of where) also as shown in the other post