I apologize for my English; I'll try to be brief and to the point.
I have two tables: "MATERIAS" and "ALUMNOS." Another table called "ALUMNOS BY MATERIAS" exists in SQL. I am aware that the properties navigation in EF makes this unnecessary, but I still need to obtain the ALUMNOS who did not enroll in any MATERIAS.
I desire such a thing.
SELECT * FROM ALUMNOS AS A WHERE NOT EXISTS( SELECT MA.MATERIAID FROM MATERIASXALUMNO AS MA WHERE A.LEGAJO = MA.LEGAJO AND .MATERIAID = XXX)
But the reason I'm confused is that I have Collection of Materias in ALUMNOS entities and vice versa, but I don't have Alumnos MateriaID or Materias AlumnosID, so how can I do this with linq?
To sum up, I need to find out who is enrolled in a XX Materia and who isn't, as well as who is enrolled in that Materia.
I appreciate your help in advance. I was able to repair issue using stored procedures, but I'd like to use Linq or at the very least understand how it works.
from Argentina, regards! Guille
If the mapping of your navigational properties makes an Alumno. You want something like this: Materias offers you the list of Materias the Alumno is enrolled in.
var missing = dbcontext.Alumnos.Where(a => !a.Materias.Any(m => m.MateriaID == XXX));
If the link between ALUMNOS and MATERIAS is mapped as specified, then this works. It enables us to handle them more like objects as opposed to tables. Under the hood, it creates SQL that automatically employs MATERIASXALUMNO and LEGAJO as well as EXISTS or NOT EXISTS. However, Entity Framework can abstract that away thanks to the mapping.