"NOT IN" clause in LINQ to Entities

Question

Is there anyway I can create a not in clause like I would have in SQL Server in Linq to Entities?

tvanfosson · Accepted Answer · 2014-10-06T17:07:06.680

107

If you are using an in-memory collection as your filter, it's probably best to use the negation of Contains(). Note that this can fail if the list is too long, in which case you will need to choose another strategy (see below for using a strategy for a fully DB-oriented query).

   var exceptionList = new List<string> { "exception1", "exception2" };

   var query = myEntities.MyEntity
                         .Select(e => e.Name)
                         .Where(e => !exceptionList.Contains(e.Name));

If you're excluding based on another database query using Except might be a better choice. (Here is a link to the supported Set extensions in LINQ to Entities)

   var exceptionList = myEntities.MyOtherEntity
                                 .Select(e => e.Name);

   var query = myEntities.MyEntity
                         .Select(e => e.Name)
                         .Except(exceptionList);

This assumes a complex entity in which you are excluding certain ones depending some property of another table and want the names of the entities that are not excluded. If you wanted the entire entity, then you'd need to construct the exceptions as instances of the entity class such that they would satisfy the default equality operator (see docs).

edited Oct 06 '14 at 17:07

answered Jan 11 '09 at 14:48

tvanfosson

524,688
99
697
795

1

For some reason `Except` produces horrible SQL. `Contains` is the method to use here: `myEntities.MyEntity.Select(e => e.Name ).Where(x => !exceptionList.Contains(x))`. – Gert Arnold Aug 09 '13 at 23:28
@GertArnold, would you please elaborate the statement "produces horrible SQL" ? I'm using Except, and it's working just fine. No oddities, nor performance quirks, AFAIK. – NinjaCross Oct 06 '14 at 16:00
@NinjaCross A statement as in the answer above produces SQL with n-1 `UNION ALL` clauses, where n is the number of items in `exceptionList`. I just tried with EF 6.1, so it's not that it improved or something. In EF 4.1 it's the same, so I just don't understand why this answer has ever been accepted. The answer proposing `Contains` is the right one. I think you used `Except` with another `IQueryable` so EF was able to translate it into a SQL `EXCEPT`. Did you? – Gert Arnold Oct 06 '14 at 16:14
@GertArnold - not Contains is perfectly acceptable within the limitation that a NOT IN clause maxes out at around 2000 entries. After that you can get a SQL resources error. It's probably what I would use as well in a simple case. – tvanfosson Oct 06 '14 at 16:29
@GertArnold, this is what I'm doing: (from e in dataModel.TABLE1 select e.ID).Except(dataModel.TABLE2.Select(m => m.LightTypeID)).Min(); This is the generated query: SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT MIN([Except1].[ID]) AS [A1] FROM (SELECT [Extent1].[ID] AS [ID] FROM [dbo].[TABLE1] AS [Extent1] EXCEPT SELECT [Extent2].[LightTypeID] AS [LightTypeID] FROM [dbo].[TABLE2] AS [Extent2]) AS [Except1] ) AS [GroupBy1] – NinjaCross Oct 06 '14 at 16:43
1

@NinjaCross Indeed, that's two `IQueryable`s with `Except`. So the whole expression contains mapped objects and can be translated into SQL `EXCEPT`. Using `Except` with an in-memory list is different. @tvanfosson I know, there is some sort of a fix though: http://stackoverflow.com/q/24534217/861716. `Except` (with an in-memory list) will throw "too deeply nested" well before these numbers. Can we conclude that `Except` is OK as long as only mapped objects are involved, and that otherwise `Contains` is better? – Gert Arnold Oct 06 '14 at 16:58
@GertArnold For the example in this answer, yes. As a general response, I think with EF it's probably best to say "it depends" - test your performance, choose the strategy that works best. – tvanfosson Oct 06 '14 at 17:01
1

@GertArnold I've updated the answer to address the various differences. At the time there were other answers that covered that case and I didn't want to cover the same ground. Now that it's old and the accepted answer I've gone ahead and included that material. – tvanfosson Oct 06 '14 at 17:09

score 14 · Answer 2 · edited Oct 05 '12 at 13:31

14

Try:

from p in db.Products
where !theBadCategories.Contains(p.Category)
select p;

What's the SQL query you want to translate into a Linq query?

edited Oct 05 '12 at 13:31

tvanfosson

524,688
99
697
795

answered Jan 11 '09 at 14:45

yfeldblum

65,165
12
129
169

score 7 · Answer 3 · answered Mar 02 '16 at 15:24

I have the following extension methods:

    public static bool IsIn<T>(this T keyObject, params T[] collection)
    {
        return collection.Contains(keyObject);
    }

    public static bool IsIn<T>(this T keyObject, IEnumerable<T> collection)
    {
        return collection.Contains(keyObject);
    }

    public static bool IsNotIn<T>(this T keyObject, params T[] collection)
    {
        return keyObject.IsIn(collection) == false;
    }

    public static bool IsNotIn<T>(this T keyObject, IEnumerable<T> collection)
    {
        return keyObject.IsIn(collection) == false;
    }

Usage:

var inclusionList = new List<string> { "inclusion1", "inclusion2" };
var query = myEntities.MyEntity
                     .Select(e => e.Name)
                     .Where(e => e.IsIn(inclusionList));

var exceptionList = new List<string> { "exception1", "exception2" };
var query = myEntities.MyEntity
                     .Select(e => e.Name)
                     .Where(e => e.IsNotIn(exceptionList));

Very useful as well when passing values directly:

var query = myEntities.MyEntity
                     .Select(e => e.Name)
                     .Where(e => e.IsIn("inclusion1", "inclusion2"));

var query = myEntities.MyEntity
                     .Select(e => e.Name)
                     .Where(e => e.IsNotIn("exception1", "exception2"));

It is helpful but it cannot be translated into store expression. — Marek Bar, Apr 26 '17 at 06:36
That's right but I'd like to execute in database. AsEnumerable will load data into memory. — Marek Bar, Apr 26 '17 at 06:46

score 4 · Answer 4 · edited Jun 03 '12 at 23:23

4

I took a list and used,

!MyList.Contains(table.columb.tostring())

Note: Make sure to use List and not Ilist

edited Jun 03 '12 at 23:23

EdChum

376,765
198
813
562

answered Sep 08 '11 at 15:26

Mayank

41
1

score 0 · Answer 5 · answered Nov 21 '16 at 19:27

I created it in a more similar way to the SQL, I think it is easier to understand

var list = (from a in listA.AsEnumerable()
            join b in listB.AsEnumerable() on a.id equals b.id into ab
            from c in ab.DefaultIfEmpty()
            where c != null
            select new { id = c.id, name = c.nome }).ToList();

"NOT IN" clause in LINQ to Entities

5 Answers5

Linked

Related