NOT IN Condition in Linq

Question

I have a simple scenario.I want to list out all the employees except the logged in user.

Similar SQL Condition is

select * from employee where id not in(_loggedUserId)

How can I acheive the above using LINQ.I have tried the following query but not getting the desired list

int _loggedUserId = Convert.ToInt32(Session["LoggedUserId"]);

List<int> _empIds = _cmn.GetEmployeeCenterWise(_loggedUserId)                              
                        .Select(e => e.Id)
                        .Except(_loggedUserId) 
                        .ToList();

Possible duplicate of [How would you do a "not in" query with LINQ?](http://stackoverflow.com/questions/183791/how-would-you-do-a-not-in-query-with-linq) — Max, Dec 09 '15 at 09:55
Your select could be select * from employee where id <> _loggedUserId and then you do not need the IN operator. — dvjanm, Dec 09 '15 at 09:55
Try `.Where(e => e.Id != _loggedUserId)` instead of `Except` — Sergii Zhevzhyk, Dec 09 '15 at 09:56

score 6 · Accepted Answer · answered Dec 09 '15 at 09:56

Except expects argument of type IEnumerable<T>, not T, so it should be something like

_empIds = _cmn.GetEmployeeCenterWise(_loggedUserId)                              
                           .Select(e => e.Id)
                           .Except(new[] {_loggedUserId}) 
                           .ToList();

Also note, this is really redundant in the case when exclusion list contains only one item and can be replaces with something like .Where(x => x != _loggedUserId)

score 2 · Answer 2 · answered Dec 09 '15 at 10:09

2

Why not use a very simple Where condition?

_empIds = _cmn.GetEmployeeCenterWise(_loggedUserId).Where(e=>e.Id != _loggedUserId).ToList();

answered Dec 09 '15 at 10:09

Kosala W

2,133
1
15
20

Martin Liversage · Answer 3 · 2015-12-09T10:16:03.630

The title of your question is how to perform a not in query against a database using LINQ. However, as others have pointed out your specific problem is better solved by a using users.Where(user => user.Id != loggedInUserId).

But there is still an answer on how to perform a query against a database using LINQ that results in NOT IN SQL being generated:

var userIdsToFilter = new[] { ... };
var filteredUsers = users.Where(user => !userIdsToFilter.Contains(user.Id));

That should generate the desired SQL using either Entity Framework or LINQ to SQL.

Entity Framework also allows you to use Except but then you will have to project the sequence to ID's before filtering them and if you need to original rows you need to fetch them again from the filtered sequence of ID's. So my advice is use Where with a Contains in the predicate.

score 1 · Answer 4 · answered Dec 09 '15 at 10:01

Use LINQ without filtering. This will make your query execute much faster:

List<int> _empIds = _cmn.GetEmployeeCenterWise(_loggedUserId)                              
                    .Select(e => e.Id).ToList();

Now use List.Remove() to remove the logged-in user.

_empIds.Remove(_loggedUserId);

NOT IN Condition in Linq

4 Answers4