C# LINQ to Entities 中的“NOT IN”子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/432954/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"NOT IN" clause in LINQ to Entities
提问by dagda1
Is there anyway I can create a not in clause like I would have in SQL Server in Linq to Entities?
无论如何，我是否可以创建一个 not in 子句，就像在Linq to Entities中的 SQL Server 中那样？
采纳答案by tvanfosson
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).
如果您使用内存中集合作为过滤器，则最好使用 Contains() 的否定。请注意，如果列表太长，这可能会失败，在这种情况下，您需要选择另一种策略（请参阅下文，了解对完全面向 DB 的查询使用策略）。
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 Exceptmight be a better choice. (Here is a linkto the supported Set extensions in LINQ to Entities)
如果您基于另一个数据库查询进行排除，则使用Except可能是更好的选择。（这里是LINQ to Entities 中支持的 Set 扩展的链接）
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).
这假设一个复杂的实体，其中您根据另一个表的某些属性排除某些实体，并希望不排除实体的名称。如果您想要整个实体，那么您需要将异常构造为实体类的实例，以便它们满足默认的相等运算符（请参阅文档）。
回答by yfeldblum
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?
您想转换为 Linq 查询的 SQL 查询是什么？
回答by Mayank
I took a list and used,
我拿了一个清单并使用，
!MyList.Contains(table.columb.tostring())
Note: Make sure to use List and not Ilist
注意：确保使用 List 而不是 Ilist
回答by JoanComasFdz
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"));
回答by Rodolfo Gaspar
I created it in a more similar way to the SQL, I think it is easier to understand
我创建的方式更类似于SQL，我觉得更容易理解
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();
