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

Answer 1

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

Answer 2

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));

Answer 3

What if you swap the statement (untested)?

where activeProducts.Contains(member.ID)

Answer 4

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()));

Answer 5

You can do it in one query:

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

Answer 6

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

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.

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 in feature alone that you're missing.

However, if you really wanna go that route, have fun: LINQ: BUILDING AN IQUERYABLE PROVIDER SERIES

Answer 7

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();

Answer 8

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.