.net Linq to Entities - SQL“IN”子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/857973/
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
Linq to Entities - SQL "IN" clause
提问by StevenMcD
In T-SQL you could have a query like:
在 T-SQL 中，您可以有如下查询：
SELECT * FROM Users WHERE User_Rights IN ("Admin", "User", "Limited")
How would you replicate that in a LINQ to Entities query? Is it even possible?
您将如何在 LINQ to Entities 查询中复制它？甚至有可能吗？
回答by BenAlabaster
You need to turn it on its head in terms of the way you're thinking about it. Instead of doing "in" to find the current item's user rights in a predefined set of applicable user rights, you're asking a predefined set of user rights if it contains the current item's applicable value. This is exactly the same way you would find an item in a regular list in .NET.
你需要根据你思考它的方式来改变它。您不是在预定义的一组适用用户权限中执行“in”来查找当前项的用户权限，而是询问一组预定义的用户权限是否包含当前项的适用值。这与您在 .NET 中的常规列表中查找项目的方式完全相同。
There are two ways of doing this using LINQ, one uses query syntax and the other uses method syntax. Essentially, they are the same and could be used interchangeably depending on your preference:
使用 LINQ 有两种方法可以做到这一点，一种使用查询语法，另一种使用方法语法。本质上，它们是相同的，可以根据您的喜好互换使用：
Query Syntax:
查询语法：
var selected = from u in users
where new[] { "Admin", "User", "Limited" }.Contains(u.User_Rights)
select u
foreach(user u in selected)
{
//Do your stuff on each selected user;
}
Method Syntax:
方法语法：
var selected = users.Where(u => new[] { "Admin", "User", "Limited" }.Contains(u.User_Rights));
foreach(user u in selected)
{
//Do stuff on each selected user;
}
My personal preference in this instance might be method syntax because instead of assigning the variable, I could do the foreach over an anonymous call like this:
在这种情况下，我个人的偏好可能是方法语法，因为我可以通过匿名调用来执行 foreach，而不是分配变量，如下所示：
foreach(User u in users.Where(u => new [] { "Admin", "User", "Limited" }.Contains(u.User_Rights)))
{
//Do stuff on each selected user;
}
Syntactically this looks more complex, and you have to understand the concept of lambda expressions or delegates to really figure out what's going on, but as you can see, this condenses the code a fair amount.
从语法上看，这看起来更复杂，您必须了解 lambda 表达式或委托的概念才能真正弄清楚发生了什么，但正如您所见，这大大压缩了代码。
It all comes down to your coding style and preference - all three of my examples do the same thing slightly differently.
这一切都取决于你的编码风格和偏好——我的三个例子都在做同样的事情，但略有不同。
An alternative way doesn't even use LINQ, you can use the same method syntax replacing "where" with "FindAll" and get the same result, which will also work in .NET 2.0:
另一种方法甚至不使用 LINQ，您可以使用相同的方法语法将“where”替换为“FindAll”并获得相同的结果，这也适用于 .NET 2.0：
foreach(User u in users.FindAll(u => new [] { "Admin", "User", "Limited" }.Contains(u.User_Rights)))
{
//Do stuff on each selected user;
}
回答by Balaji Birajdar
This should suffice your purpose. It compares two collections and checks if one collection has the values matching those in the other collection
这应该足以满足您的目的。它比较两个集合并检查一个集合的值是否与另一个集合中的值匹配
fea_Features.Where(s => selectedFeatures.Contains(s.feaId))
回答by KristoferA
If you're using VS2008/.net 3.5, see Alex James' tip #8: http://blogs.msdn.com/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx
如果您使用的是 VS2008/.net 3.5，请参阅 Alex James 的提示 #8：http: //blogs.msdn.com/alexj/archive/2009/03/26/tip-8-writing-where-in-style -queries-using-linq-to-entities.aspx
Otherwise just use the array.Contains(someEntity.Member) method.
否则只需使用 array.Contains(someEntity.Member) 方法。
回答by Pankaj
I will go for Inner Join in this context. If I would have used contains, it would iterate 6 times despite if the fact that there are just one match.
在这种情况下，我将采用 Inner Join。如果我使用 contains，即使只有一个匹配项，它也会迭代 6 次。
var desiredNames = new[] { "Pankaj", "Garg" };
var people = new[]
{
new { FirstName="Pankaj", Surname="Garg" },
new { FirstName="Marc", Surname="Gravell" },
new { FirstName="Jeff", Surname="Atwood" }
};
var records = (from p in people join filtered in desiredNames on p.FirstName equals filtered select p.FirstName).ToList();
Disadvantages of Contains
包含的缺点
Suppose I have two list objects.
假设我有两个列表对象。
List 1 List 2
1 12
2 7
3 8
4 98
5 9
6 10
7 6
Using Contains, it will search for each List 1 item in List 2 that means iteration will happen 49 times !!!
使用包含，它将搜索列表 2 中的每个列表 1 项目，这意味着迭代将发生 49 次！！！
回答by Torakami
This could be the possible way in which you can directly use LINQ extension methods to check the in clause
这可能是您可以直接使用 LINQ 扩展方法检查 in 子句的可能方式
var result = _db.Companies.Where(c => _db.CurrentSessionVariableDetails.Select(s => s.CompanyId).Contains(c.Id)).ToList();
回答by fire in the hole
An alternative method to BenAlabaster answer
BenAlabaster 答案的替代方法
First of all, you can rewrite the query like this:
首先，您可以像这样重写查询：
var matches = from Users in people
where Users.User_Rights == "Admin" ||
Users.User_Rights == "Users" ||
Users.User_Rights == "Limited"
select Users;
Certainly this is more 'wordy' and a pain to write but it works all the same.
当然，这更“冗长”并且写起来很痛苦，但它的工作原理都是一样的。
So if we had some utility method that made it easy to create these kind of LINQ expressions we'd be in business.
因此，如果我们有一些实用方法可以轻松创建此类 LINQ 表达式，我们就会开展业务。
with a utility method in place you can write something like this:
有了实用程序方法，您可以编写如下内容：
var matches = ctx.People.Where(
BuildOrExpression<People, string>(
p => p.User_Rights, names
)
);
This builds an expression that has the same effect as:
这将构建一个与以下效果相同的表达式：
var matches = from p in ctx.People
where names.Contains(p.User_Rights)
select p;
But which more importantly actually works against .NET 3.5 SP1.
但更重要的是，它实际上适用于 .NET 3.5 SP1。
Here is the plumbing function that makes this possible:
这是使这成为可能的管道功能：
public static Expression<Func<TElement, bool>> BuildOrExpression<TElement, TValue>(
Expression<Func<TElement, TValue>> valueSelector,
IEnumerable<TValue> values
)
{
if (null == valueSelector)
throw new ArgumentNullException("valueSelector");
if (null == values)
throw new ArgumentNullException("values");
ParameterExpression p = valueSelector.Parameters.Single();
if (!values.Any())
return e => false;
var equals = values.Select(value =>
(Expression)Expression.Equal(
valueSelector.Body,
Expression.Constant(
value,
typeof(TValue)
)
)
);
var body = equals.Aggregate<Expression>(
(accumulate, equal) => Expression.Or(accumulate, equal)
);
return Expression.Lambda<Func<TElement, bool>>(body, p);
}
I'm not going to try to explain this method, other than to say it essentially builds a predicate expression for all the values using the valueSelector (i.e. p => p.User_Rights) and ORs those predicates together to create an expression for the complete predicate
我不打算解释这个方法，只是说它本质上使用 valueSelector（即 p => p.User_Rights）为所有值构建了一个谓词表达式，并将这些谓词 ORs 在一起以创建一个完整的表达式。谓词
来源：http: //blogs.msdn.com/b/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx
回答by Matthias Meid
I also tried to work with an SQL-IN-like thing - querying against an Entity Data Model. My approach is a string builder to compose a big OR-expression. That's terribly ugly, but I'm afraid it's the only way to go right now.
我还尝试使用类似 SQL-IN 的东西 - 查询实体数据模型。我的方法是一个字符串生成器来组成一个大的 OR 表达式。那太难看了，但恐怕这是目前唯一的出路。
Now well, that looks like this:
现在好了，看起来像这样：
Queue<Guid> productIds = new Queue<Guid>(Products.Select(p => p.Key));
if(productIds.Count > 0)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0}.ProductId = Guid\'{1}\'", entities.Products.Name, productIds.Dequeue());
while(productIds.Count > 0)
{
sb.AppendFormat(" OR {0}.ProductId = Guid\'{1}\'",
entities.Products.Name, productIds.Dequeue());
}
}
Working with GUIDs in this context: As you can see above, there is always the word "GUID" before the GUID ifself in the query string fragments. If you don't add this, ObjectQuery<T>.Wherethrows the following exception:
在此上下文中使用 GUID：正如您在上面看到的，在查询字符串片段中的 GUID ifself 之前总是有“GUID”这个词。如果不添加此项，则会ObjectQuery<T>.Where引发以下异常：
The argument types 'Edm.Guid' and 'Edm.String' are incompatible for this operation., near equals expression, line 6, column 14.
参数类型“Edm.Guid”和“Edm.String”与此操作不兼容。，接近等于表达式，第 6 行，第 14 列。
Found this in MSDN Forums, might be helpful to have in mind.
在 MSDN 论坛中找到了这个，记住它可能会有所帮助。
Matthias
马蒂亚斯
... looking forward for the next version of .NET and Entity Framework, when everything get's better. :)
...期待 .NET 和 Entity Framework 的下一个版本，届时一切都会变得更好。:)
回答by Adel Mourad
Real example:
真实例子：
var trackList = Model.TrackingHistory.GroupBy(x => x.ShipmentStatusId).Select(x => x.Last()).Reverse();
List<int> done_step1 = new List<int>() {2,3,4,5,6,7,8,9,10,11,14,18,21,22,23,24,25,26 };
bool isExists = trackList.Where(x => done_step1.Contains(x.ShipmentStatusId.Value)).FirstOrDefault() != null;
回答by cjm30305
Seriously? You folks have never used
严重地？你们都没用过
where (t.MyTableId == 1 || t.MyTableId == 2 || t.MyTableId == 3)
