"在"运营商的Linq [英] "IN" Operator in Linq

问题描述

我想转换成Linq的一个古老原始的SQL查询与实体框架在这里。

它使用IN操作符与项目的集合。查询是类似的东西：

  SELECT Members.Name 
议员
，其中Members.ID IN （SELECT DISTINCT ManufacturerID FROM产品WHERE活动= 1）
 ORDER BY Members.Name ASC 
  

因为子查询返回的不是一个字符串，但字符串我不能使用 String.Contains（）方法的集合。

我想过做这样的事情：

  VAR activeProducts =（$ b $从产品数据库b .ProductSet $​​ b $ b，其中product.Active == TRUE 
选择product.ManufacturerID）; 
  

然后

  VAR activeMembers =（会员
在db.ContactSet $​​ b $ b，其中member.ID.ToString（）包含（activeProducts））; 
  

但它停靠在包含说它具有参数无效......我不能选择activeProducts。 ManufacturerID因为很明显的proprety是不存在的，因为它会返回一个IQueryable ...

底线是什么，我想在这里做的是返回谁拥有成员名单至少有一个活动的产物。

任何暗示？

下面是完整的查询代码...我试着在第二表达式中包含，LINQ的似乎不喜欢它：

在'/'应用程序的服务器错误。
LINQ到实体无​​法识别方法'布尔包含[字符串]（System.Linq.IQueryable``1 [System.String] System.String）的方法，而这种方法不能被翻译成表达店

  VAR activeProduct =（从Master.DataContext.ProductSet $​​ b $ b产品，其中产品。主动==真
和;&安培; product.ShowOnWebSite ==真
和;&安培; product.AvailableDate< = DateTime.Today 
和;及（product.DiscontinuationDate ==空|| product.DiscontinuationDate> = DateTime.Today）
选择product.ManufacturerID.ToString（））; 
 
变种activeArtists从艺术家=在Master.DataContext.ContactSet $​​ B $ b，其中activeProduct.Contains（artist.ID.ToString（））
选择的艺术家; 
 
 NumberOfArtists = activeArtists.Count（）; 
 
 artistsRepeater.DataSource = activeArtists; 
 artistsRepeater.DataBind（）; 
  

[详细信息]
ManufacturerID是一个可空GUID显然...

由于某些原因，ContactSet类不包含对我想我会做一个连接查询，这里没有任何线索的产品有任何引用。

解决方案

 在db.ContactSet $​​ b $ b，其中activeProducts.Select（X => VAR activeMembers =（
会员; x.ID）。载有（member.ID））; 
  

I am trying to convert an old raw Sql query in Linq with Entity Framework here.

It was using the IN operator with a collection of items. The query was something like that:

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.

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...

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 :

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...

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.

解决方案

var activeMembers = (
from member in db.ContactSet
where activeProducts.Select(x=>x.ID).Contains(member.ID));

这篇关于"在"运营商的Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！