Selecting on a many table using Linq.

Question

I have a recipe table that has a related ingredients table on one to many basis.

How do I select using Linq ingredients that have a ingredientName column and it should contain a specified word.

This is what I tried.

 IQueryable<OurRecipes.Domain.Linq2Sql.Recipe> recipes = _dbctx.Recipes.AsQueryable();

    foreach (string word in searchdata.Keywords)
    {
        recipes = recipes.Where(r => r.RecipeTitle.Contains(word));
        recipes = recipes.Where(r => r.Ingredients.Where(i => i.IngredientName.Contains(word)));
    }

I get cannot convert type 'etc' to bool error.

Any ideas Malcolm

Answer 1

The error lies here:

 recipes = recipes.Where(r => r.Ingredients.Where(i => i.IngredientName.Contains(word)));

The condition inside Where must return a boolean, in this case, the r.Ingredients.Where(i => i.IngredientName.Contains(word)) will not return a boolean, and hence the error.

This is how you can fix the problem:

recipes = recipes.Where(i => i.Ingredients.Any(row=>row.IngredientName.Contains(word)));

Answer 2

r.Ingredients.Where(i => i.IngredientName.Contains(word)));

replace with

r.Ingredients.Any(i => i.IngredientName.Contains(word)));

---

Btw, I like SQL like syntax more as it more netural. The same:

from r in _dbctx.Recipes
where r.Ingredients.Any(i => i.IngredientName.Contains(word)));
select r;

This will select all recipies that has ingredients with name contains word.