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
I thought about doing something like :
var activeProducts = ( from products in db.ProductSet where product.Active == true select product.ManufacturerID);
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 ?
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));
EDIT: Did you try it without any