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)

This question may help: http://stackoverflow.com/questions/374267/contains-workaround-using-linq-to-entities — Craig Stuntz, Oct 22 '09 at 18:49
In my database I have a business table a BusinessCategoryRelationship table and Category table, a business can be attached to many Categories. I want to be able to retrieve a collection business based on either a collection of category object or a list of category ID's. I been searching for two days for this simple query but cant get anything to return the business — Simon, Oct 23 '09 at 20:50

score 0 · Answer 1 · edited Oct 22 '09 at 18:47

0

just trying to read your mind here ;)

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

edited Oct 22 '09 at 18:47

Craig Stuntz

125,891
12
252
273

answered Oct 22 '09 at 17:36

Enes

3,951
3
25
23

What I’m looking for is to find out if I can search one collection of entity objects, with another collection of objects and if any match then i want to pull out the parent. Where a business (parent) has a collection of categories and busCatList is a collection of categories I’m searching for, so if a business has a category that exists in busCatList then I want the business/s retuned as a collection – Simon Oct 23 '09 at 11:13

score 0 · Answer 2 · answered Oct 24 '09 at 02:10

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.

Search Database using a collection of entity objects

2 Answers2