LINQ to EF - Find records where string property of a child collection at least partially matches all records in a list of strings

Question

I am currently writing a web-based 'recipe' application using LINQ and Entity Framework 5.0. I've been struggling with this query for awhile, so any help is much appreciated!

There will be a search function where the users can enter a list of ingredients that they want the recipe results to match. I need to find all recipes where the associated ingredient collection (name property) contains the text of every record in a list of strings (the user search terms). For example, consider the following two recipes:

Tomato Sauce: Ingredients 'crushed tomatoes', 'basil', 'olive oil'
Tomato Soup:  Ingredients 'tomato paste', 'milk', 'herbs

If the user used the search terms 'tomato' and 'oil' it would return the tomato sauce but not the tomato soup.

var allRecipes = context.Recipes
                .Include(recipeCategory => recipeCategory.Category)
                .Include(recipeUser => recipeUser.User);

IQueryable<Recipe> r = 
from recipe in allRecipes
let ingredientNames = 
    (from ingredient in recipe.Ingredients 
     select ingredient.IngredientName)
from i in ingredientNames
let ingredientsToSearch = i where ingredientList.Contains(i)
where ingredientsToSearch.Count() == ingredientList.Count()
select recipe;

I've also tried:

var list = context.Ingredients.Include(ingredient => ingredient.Recipe)
       .Where(il=>ingredientList.All(x=>il.IngredientName.Contains(x)))
       .GroupBy(recipe=>recipe.Recipe).AsQueryable();

Thank you for your help!

Answer 1

Just off the top of my head i would go for something like this

public IEnumerable<Recipe> SearchByIngredients(params string[] ingredients)
{
    var recipes = context.Recipes
                .Include(recipeCategory => recipeCategory.Category)
                .Include(recipeUser => recipeUser.User);
    foreach(var ingredient in ingredients)
    {
        recipes = recipes.Where(r=>r.Ingredients.Any(i=>i.IngredientName.Contains(ingredient)));
    }

    //Finialise the queriable
    return recipes.AsEnumerable();

}

You can then call it using:

SearchByIngredients("tomatoes", "oil");

or

var ingredients = new string[]{"tomatoes", "oil"};
SearchByIngredients(ingredients );

What this is going to do is attach where clauses to the queriable recipes for each of your search terms. Multiple where clauses are treated as ANDs in SQL (which is what you want here anyway). Linq is quite nice in the way that we can do this, at then end of the function we finalise the queriable essentially saying all that stuff that we just did can get turned into a single query back to the DB.

My only other note would be you really want to be indexing/full text indexing the Ingredient name column or this wont scale terribly well.