CODEWITHSUNDEEP
c#sqlentity-frameworklinq
lordvlad30
lordvlad30

Reputation: 451

SQL/ Linq query select from many to many

I have following (simplified) structure in my SQL database. enter image description here

Now I would like to select all groups where the user is not yet part of. It would help if the query is written using Linq/Entity Framework but I can work with the SQL statement too.

I tried using Include (using EF):

.Include(g => g.Group_User)

in the query and than using a: 

.Where(g => g.Group_User.UserId != userId)

but that did not work and is probably totally not the correct way of doing these kind of query's.

Thanks for reading

Upvotes: 1

Views: 59

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

I would like to select all groups where the user is not yet part of.

So you want all groups without a group-user with this UserId, so use !Any and ==:

var q = db.Group.Include(g => g.Group_User)
    .Where(g => !g.Group_User.Any(gu => gu.UserId == userId));

Upvotes: 2

Related Questions