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.

Craig Stuntz · Accepted Answer · 2010-07-21 16:31:10Z

2

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.

answered Jul 21, 2010 at 16:31

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chinjoo Over a year ago

Yes, You are correct, but I have PersonItemAssoc(int) used as FK in other tables, due to which I cant drop that one.

Collectives™ on Stack Overflow

Comparing SQL tables and getting Values using Entity Framework

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related