"IN" Operator in 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.

Solution 1

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

Solution 2

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

Solution 3

You can do it in one query:

var q = from member in db.ContactSet
        where member.Products.Any(p => p.IsActive)
        select member;

Solution 4

from m in members
where products.Any(p => p.Active && p.ManufacturerID == m.ID)
select m

or

from m in members
join p in products on m.ID equals p.ManufacturerID
where p.Active
select m

Solution 5

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.

View more solutions

Comments

[Erick]

Tried, Linq didn't seem to like it actually.

[Erick]

I had to simplify the statement but there is more conditions to the "where" ... I edited my post to include full code in fact.

[SLaks]

You can still do it in one query. Move the entire where clause to the Any call.

[Erick]

There's no link from ContactSet to ProductSet apprently (oh how I love 3rd party software modification), I guess I will have to do a join query...

[Erick]

Yup, and Linq still doesn't like it, I just did discovered that ManufacturerID is a Guid? (nullable) ... if I do a ` select ManufacturerID.Value` I have this : LINQ to Entities does not recognize the method 'Boolean Contains[Guid](System.Linq.IQueryable`1[System.Guid], System.Guid)' method, and this method cannot be translated into a store expression.

[Kevin Gauthier]

Even with the join you can still do it in one query, though.

[tofutim]

Is this as efficient as the IN operator?

[SLaks]

@tofutim: This should be translated into an IN clause. (disclaimer: I haven't checked)

[spadelives]

I think this change would make it work... var activeProducts = ( from products in db.ProductSet where product.Active == true select product.ManufacturerID); var activeMembers = ( from member in db.ContactSet where activeProducts.Select(x=>x.id).Contains(member.ID.ToString()‌​));

[Askolein]

product.Active == true is a bit redundant isn't it? Just product.Active is enough.

[Manish Basantani]

@Askolein I find it more readable, may be personal choice. Would rather like to rename 'Active' to 'IsActive' and then remove comparing with true/false.

[nicolas2008]

very bad solution