Search Database using a collection of entity objects

Question

I'm sure this is straight forward but I'm very new to entity queries and has probably been asked before.

What i need to to search for all business in my database where they have a category that exists in a collection of categories I have built up

IList<businessCategory> busCatList;

busCatList.Add(businessCategory.CreatebusinessCategory(1,"Tourism"));
busCatList.Add(businessCategory.CreatebusinessCategory(2,"Accomidation"));
busCatList.Add(businessCategory.CreatebusinessCategory(3,"Entertainment"));
busCatList.Add(businessCategory.CreatebusinessCategory(4,"Bar"));
busCatList.Add(businessCategory.CreatebusinessCategory(5,"Club"));

var items = Data.DBEntities.business.Where(b.businessCategory.Contains(busCatList) );

I know the syntax of the query is wrong but essentially what i what the query to do it pull out all the business where it has a category matching any of the categories in the busCatLsit

In my database one business can be linked to many categories

In SQL I would do

SELECT name  FROM business
join businessCategoryRlnshp on businessCategoryRlnshp.businessID = business.ID
where categoryID in (1,2,3)

Answer 1

just trying to read your mind here ;)

var items = busCatList.Where(businessCategory => b.businessCategory.Contains(businessCategory));

Answer 2

I can't really imagine a nice solution in linq - I mean other than some kind of performing the query several times - one for each business category.

However in SQL Server 2008 there is a new feature - passing a table variable to stored procedure. This can be done from code by passing a DataSet with 1 DataTable as a parameter. You can of course write an extension method for IEnumerable to convert it to a DataSet similar to ToList() or ToDictionary() methods. Stored procedure returning entities can be used in EntityFramework 1.0 so this should theoretically make the puzzle click.

PS> There's also a solution using E-SQL and probably query builder methods.