linq to sql where not in

Question

I am trying find all the users that are not in a friends user friend list using linq to sql. Right now I am comparing friendslist to all the users in the system. Theres gotta be a better way of doing this using linq to sql. Thanks for any help

// Comparing this to list to see who does not exist.
// What I would like to do is just use one statement
// to get the list of non friend users
var friends = (from x in db.FriendsLists
               where
                   (x.TheUser == GlobalVariables.User.ID) ||
                   (x.TheFriend == GlobalVariables.User.ID)
               select x).ToList();

var allUsersInSystem = (from x in db.Users select x).ToList();

possible duplicate of ["NOT IN" clause in LINQ to Entities](http://stackoverflow.com/questions/432954/not-in-clause-in-linq-to-entities) — devuxer, Aug 23 '12 at 20:52
Actually that question was asking about LINQ to Entities, not LINQ to SQL. It's possible this would work in LINQ to SQL as well... — devuxer, Aug 23 '12 at 20:53

score 2 · Accepted Answer · answered Aug 23 '12 at 21:19

  var friends = (from x in db.FriendsLists
                                   where
                                       (x.TheUser == GlobalVariables.User.ID) ||
                                       (x.TheFriend == GlobalVariables.User.ID)
                                   select x.UserID).ToList();

     var notInFriendList = from nf in db.Users
                where !friends.Contains(nf.UserID)
                select nf;

score 0 · Answer 2 · answered Aug 23 '12 at 20:53

0

You can use IEnumerable.Except.

Example:

var notIn = allUsersInSystem.Except(friends);

answered Aug 23 '12 at 20:53

Tigran

61,654
8
86
123

linq to sql where not in

2 Answers2