CODEWITHSUNDEEP
c#asp.netsqlasp.net-mvclinq
Jimmy
Jimmy

Reputation: 2951

LINQ Query C# ASP

In English I'm trying to: "Return a list of People who don't have the TeamID in their List of Teams

I have a many-to-many relationship between a Person table and a Team table so each instance of a Person has a List of Teams that they are a member of. (each Team also has a list of People)

Table

here a Person 2 would have a list of two Team objects with ID's of 1 and 2

I'm trying to write a query that, if TeamID were 18 it would return only Persons 1 and 3 for example

This is my attempt:

var query = from p in db.People
            where( query2 = from t in p.Teams
                            where t.ID != teamID
                            select t)
            select p;

Upvotes: 2

Views: 162

Answers (2)

Vic
Vic

Reputation: 632

Adapted to your code:

var query =    
        from p in db.People
        where !(from t in db.Teams    
                select t.ID)    
               .Contains(p.ID)    
        select p;

Upvotes: 0

Erik Schierboom
Erik Schierboom

Reputation: 16636

Using the method-based LINQ syntax this becomes:

var query = db.People.Where(p => !p.Teams.Any(t => t.ID == 18));

Upvotes: 6

Related Questions