0

I'm using .net, mssql, LinqToSQL. My mssql db has the following schema - Recipes are related to Ingredients. Now I want to let users to find recipes by several ingredients. I need a stored-procedure or query which I can use to select all those recipes. The number of searching ingredients is unknown.

I tried to use lintosql

var list = datacontext.Recipes.Where(r => values.All(v => r.RecipeIngredientMeasures.Any(i => i.Ingredient.NameTrans == v))).ToList();

Where values is array of my searching ingredients.

But then I got

"Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator."

chridam
  • 100,957
  • 23
  • 236
  • 235
Lara G
  • 23
  • 2
  • Have you tried replacing the usages of `Any` with `Contains` in your query? – chridam Feb 16 '15 at 09:49
  • How will that look like? ...r.RecipeIngredientMeasures.Contains(...) - this Contains is waiting for RecipeIngredientMeasure entity, not for boolen expression. – Lara G Feb 16 '15 at 09:55
  • First do something like `var ingredientNames = i.Ingredient.Select(x=>x.NameTrans)` then do the contains on that. – MWillemse Feb 16 '15 at 18:24
  • for each recipe? loop over 1.000.000 recipes for each search? :) – Lara G Feb 17 '15 at 06:21

1 Answers1

2

Here is the SQL query I use to use for this case.

declare @ingredNames table (name nvarchar(255));

-- so you need to split the list of ingredient comma separated string to table
-- to do that take a look to the link in the comments
insert into @ingredNames (name) values ('rice'), ('chicken');

-- get recipes that have all the specified ingredients
select r.Id, r.Name as number from Recipies as r 
inner join RecipeIngredientMeasures as rim on rim.RecipeId= r.Id
inner join Ingredients as ing on rim.IngId= ing.Id

inner join @ingredNames as ingN on ingN.name = ing.NameTrans 
Group By r.Id, r.Name
Having count(ing.Id) = (select count(name) from @ingredNames )

good luck.

Harutyun Terteryan
  • 36
  • 2