How to write a Linq to entity statement, using sql not in operator

Question

Possible Duplicate:
“NOT IN” clause in LINQ to Entities

I work with the schema below, and I want to know how I can write in the sql query in LINQ to Entities.

SQL Code:

Select * 
FROM GROUEPUSERS G
WHERE G.IDGROUPE NOT IN (SELECT IDGROUPE
                         FROM  APPARTENIR
                         WHERE IDCLIENT = id)

The ID is a variable that I will recover. The problem is that the table APPARTENIR becomes a navigation table.

I found the solution just in case :

var groups = from g in context.GROUPEUSER 
    where !g.UTILISATEUR.Select(d => d.ID).Contains(id)
    select g;

**http://stackoverflow.com/questions/432954/not-in-clause-in-linq-to-entities** — huMpty duMpty, Mar 20 '12 at 11:48
Hello and thank you for your answers but i'm just a beginner with entity framework and i don't know how i can deal with it yet, i'm reading some tutorials and i progress but here i need some help, — benjy, Mar 20 '12 at 12:05
Where is your APPARTENIR entity in your screenshot? I don't see which columns and tables map to which entities and properties in your screenshot. — Wouter de Kort, Mar 20 '12 at 12:12
That's the problem when i generated the entities it didn't give me an entity named APPARTENIR that contain the primary key of UTILISATEUR and GROUPEUSER it just transform it to NAVIGATION PROPERTY — benjy, Mar 20 '12 at 12:27

BitKFu · Accepted Answer · 2012-03-25T18:12:03.480

2

You can write something like that:

 var query = myEntities.Groupeusers
     .Where(gu => !myEntities.Utilisateur.Any(ut => ut.idgroupe == gu.idgroupe)).ToList();

That should work.

EDIT:

Try that query instead:

 var query = myEntities.Groupeusers
     .Where(gu => !myEntities.Utilisateur
                .SelectMany(ut=>ut.Groupeuser)
                .Any(gu => gu.Idgroupe == gu.Idgroupe)).ToList();

OR maybe even better:

 var query = myEntities.GroupeUsers
     .Except(myEntities.Utilisateur.SelectMany(ut => ut.Groupeuser))
     .ToList();

EDIT2:

If I understand your query correct, you would like to identify a special user entity.

var query = myEntities.GroupeUsers 
     .Except(myEntities.Utilisateur.Where(u => u.IdUser == id).SelectMany(ut => ut.Groupeuser)) 
     .ToList();

edited Mar 25 '12 at 18:12

answered Mar 20 '12 at 12:21

BitKFu

3,649
3
28
43

Thank you BitKFu But i can't get IDGROUP from entity UTILISATEUR it just gave me ut.GROUPEUSERS and not the attribut IDGROUPE – benjy Mar 20 '12 at 12:46
I think the problem is, that you did not map the link table APPARTENIR in your entity model. That's really the missing link in here. – BitKFu Mar 20 '12 at 13:06
Do you have a tutoriel or course that can help me to understand more , because all of tutorials that i had read aren't spécific. Any way thanks for your HELP :) – benjy Mar 20 '12 at 13:48
AND the ID variable that I recover where should i put it PS : i used the seconde query : `var query = myEntities.GroupeUsers .Except(myEntities.Utilisateur.SelectMany(ut => ut.Groupeuser)) .ToList();` @BitKFu – benjy Mar 21 '12 at 09:51

How to write a Linq to entity statement, using sql not in operator

1 Answers1