Comparing SQL tables and getting Values using Entity Framework

Question

I have three tables viz: Person(PersonID(INT), Personname(varchar)) , Items(ItemID(INT), Itemname(varchar)) and PersonItemAssoc(PersonItemAssoc(Int), PersonID(INT), ItemID(INT)).

PersonItemAssoc is having many to many association for personid and Itemid.

I want to get way in which if I pass the itemId, I should get all the PersonIds which dont have an association witn this ItemId in the PersonItemAssoc table.

I am using Entity Framework 4.0.
Please suggest a way for implementing this.

Answer 1

var peopleWithoutItem = from p in Context.Person
                        where !p.PersonItems.Any(pi => pi.Item.ItemId == someItemId);
                        select p;

Note that if you get rid of PersonItemAssoc(int) and make the PersonItemAssoc PK the compound of PersonID and ItemID then the EF can do People to Items as a many to many, instead of two 1 to many relationships.