C# Linq 中的“IN”运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2066084/
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
"IN" Operator in Linq
提问by Erick
I am trying to convert an old raw Sql query in Linq with Entity Framework here.
我正在尝试使用实体框架在 Linq 中转换旧的原始 Sql 查询。
It was using the IN operator with a collection of items. The query was something like that:
它使用 IN 运算符和一组项目。查询是这样的：
SELECT Members.Name
FROM Members
WHERE Members.ID IN ( SELECT DISTINCT ManufacturerID FROM Products WHERE Active = 1)
ORDER BY Members.Name ASC
Since the return of the subquery is not a single string but a collection of strings I can't use the String.Contains()method.
由于子查询的返回不是单个字符串而是字符串集合，因此我无法使用该String.Contains()方法。
I thought about doing something like :
我想过做类似的事情：
var activeProducts = (
from products in db.ProductSet
where product.Active == true
select product.ManufacturerID);
and then
进而
var activeMembers = (
from member in db.ContactSet
where member.ID.ToString().Contains(activeProducts));
but it stops at the contains saying it has invalid arguments ... I can't select activeProducts.ManufacturerID because obviously the proprety is not there since it returns an IQueryable...
但它停在包含说它有无效参数......我不能选择 activeProducts.ManufacturerID 因为显然属性不存在，因为它返回一个 IQueryable ...
Bottom line what I'm trying to do here is to return a list of members who have at least one active product.
最重要的是，我在这里尝试做的是返回至少拥有一个活动产品的成员列表。
Any hint ?
任何提示？
[edit]
[编辑]
Here's the full query code ... I tried with the contains on the second expression, Linq didn't seem to like it :
这是完整的查询代码......我尝试在第二个表达式中使用 contains，Linq 似乎不喜欢它：
Server Error in '/' Application.
LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Linq.IQueryable``1[System.String], System.String)' method, and this method cannot be translated into a store expression.
Server Error in '/' Application.
LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Linq.IQueryable``1[System.String], System.String)' method, and this method cannot be translated into a store expression.
var activeProduct =(from product in Master.DataContext.ProductSet
where product.Active == true
&& product.ShowOnWebSite == true
&& product.AvailableDate <= DateTime.Today
&& ( product.DiscontinuationDate == null || product.DiscontinuationDate >= DateTime.Today )
select product.ManufacturerID.ToString() );
var activeArtists = from artist in Master.DataContext.ContactSet
where activeProduct.Contains(artist.ID.ToString())
select artist;
NumberOfArtists = activeArtists.Count();
artistsRepeater.DataSource = activeArtists;
artistsRepeater.DataBind();
[More details] ManufacturerID is a nullable GUID apparently...
[更多详情] 制造商 ID 显然是一个可为空的 GUID...
For some reason the ContactSet class do not contain any reference to the products I guess I will have to do a join query, no clues here.
出于某种原因， ContactSet 类不包含对产品的任何引用，我想我将不得不进行连接查询，这里没有任何线索。
采纳答案by spadelives
var activeMembers = (
from member in db.ContactSet
where activeProducts.Select(x=>x.ID).Contains(member.ID));
回答by SLaks
Try where activeProducts.Contains(member.ID).
EDIT: Did you try it without any ToStrings?
试试where activeProducts.Contains(member.ID)。
编辑：你试过没有任何ToStrings 吗？
回答by Eric Petroelje
Instead of this:
取而代之的是：
var activeMembers = (
from member in db.ContactSet
where member.ID.ToString().Contains(activeProducts));
Try this:
尝试这个：
var activeMembers = (
from member in db.ContactSet
where activeProducts.Contains(member.ID));
回答by Matthias
What if you swap the statement (untested)?
如果你交换语句（未经测试）怎么办？
where activeProducts.Contains(member.ID)
回答by Manish Basantani
How about this...
这个怎么样...
var activeProducts = (
from products in db.ProductSet
where product.Active == true
select product.ManufacturerID);
var activeMembers = (
from member in db.ContactSet
where activeProducts.Contains(member.ID.ToString()));
回答by Craig Stuntz
You can do it in one query:
您可以在一个查询中完成：
var q = from member in db.ContactSet
where member.Products.Any(p => p.IsActive)
select member;
回答by herzmeister
A helper or extension method will work fine when querying against objects in memory. But against an SQL database, your LINQ code will be compiled into an expression tree, analysed and translated into an SQL command. This functionality has no concept of custom-made extension methods or methods of other objects like .Contains(...).
查询内存中的对象时，助手或扩展方法将正常工作。但是对于 SQL 数据库，您的 LINQ 代码将被编译成表达式树，进行分析并转换为 SQL 命令。此功能没有自定义扩展方法或其他对象（如.Contains(...).
It could be easily implemented into the standard LINQ-To-SQL functionality by Microsoft though. But as long as they don't want, we're helpless as long it's not an open source functionality.
不过，它可以很容易地由 Microsoft 实现到标准的 LINQ-To-SQL 功能中。但只要他们不想要，只要它不是开源功能，我们就无能为力。
All you can do is create your own QueryProvider that goes against an SQL database. But it will be hard and it would be only for that one infeature alone that you're missing.
你所能做的就是创建你自己的 QueryProvider 来对抗 SQL 数据库。但这会很困难，而且只会因为in您缺少一项功能。
However, if you really wanna go that route, have fun: LINQ: BUILDING AN IQUERYABLE PROVIDER SERIES
但是，如果您真的想走那条路，请玩得开心：LINQ：构建可查询的提供程序系列
回答by Erick
Finally I managed to code something really ugly, but that actually works! (lol)
最后我设法编写了一些非常难看的东西，但这确实有效！（哈哈）
var activeProduct =(from product in Master.DataContext.ProductSet
where product.Active == true
&& product.ShowOnWebSite == true
&& product.AvailableDate <= DateTime.Today
&& ( product.DiscontinuationDate == null || product.DiscontinuationDate >= DateTime.Today )
select product.ManufacturerID ).Distinct();
var artists = from artist in Master.DataContext.ContactSet
select artist;
List<Evolution.API.Contact> activeArtists = new List<Evolution.API.Contact>();
foreach (var artist in artists)
{
foreach(var product in activeProduct)
{
if (product.HasValue && product.Value == artist.ID)
activeArtists.Add(artist);
}
}
NumberOfArtists = activeArtists.Count();
artistsRepeater.DataSource = activeArtists;
artistsRepeater.DataBind();
回答by spot
Try the solution posted by Colin Meek at: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/. It worked for me.
试试 Colin Meek 发布的解决方案：http: //social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/。它对我有用。
回答by Taz
var q = (from p in db.DOCAuditTrails
where p.ActionUser == "MyUserID"
&& p.ActionTaken == "Not Actioned"
&& p.ActionDate > DateTime.Parse("2011-09-13")
select p.RequisitionId).Distinct();
var DocAuditResults = db.DOCAuditTrails.Where(p
=> q.ToArray().Contains(p.RequisitionId));
回答by Ashley Hymanson
What about this:
那这个呢：
from m in members
where products.FirstOrDefault(prod => prod.IsActive == 1 && prod.Id == m.Id) != null
select m;
you can chain any number of conditions required in the where clause using &&
您可以使用 && 链接 where 子句中所需的任意数量的条件
Ash..
灰..
