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

Pressley

Post your entities schema, its hard to understand what you mean based on your description – Gaberdine

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.

Incumber

I'm surprised this was marked as an answer as it'll not work. In fact, it wouldn't even compile. This will work: var missing = dbcontext.Alumnos.Where(a => !dbcontext.Materias.Any(m => m.AlumnoID == a.AlumnoID && m.MateriaID == XXX)); – Archespore

I have updated my answer to try to clarify the assumption I make (based on the OP's description) about the mapping. The mapping is essential to whether it works or even compiles. I hope it helps. – Incumber

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

Senhor

I guarantee you that using .!Any() (as in Foo.!Any()) would not compile. – Ajax

They said they have navigation properties; it sounds like they have a context.MATERIASXALUMNO? – Incumber

Any also takes a predicate just as Where does. So you can pass the predicate directly to Any and remove the Where. – Whose

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

Gaberdine

I was trying to do this, but when I do "...datacontext.Materias.Any(m => m.AlumnoID..." I haven't AlumnoID I have an EntityCollection of Alumnos... this is my confusion... – Pressley

You don't specify which is the parent table. Post your entity classes if your using code-first or a database schema otherwise, in order to better understand what you're trying to do. – Gaberdine