Linq's "IN" Operator

c# c#-3.0 entity-framework linq

Question

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.

1
13
1/14/2010 5:48:34 PM

Accepted Answer

var activeMembers = (
from member in db.ContactSet
where activeProducts.Select(x=>x.ID).Contains(member.ID));
15
12/24/2012 7:09:32 PM

Popular Answer

Try where activeProducts.Contains(member.ID).
EDIT: Did you try it without any ToStrings?



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow