How to write a LINQ to Entities query with List in a "WHERE" condition

Question

I would like to know to to write most efficient LINQ (EDIT: to Entities) query with a list as a condition. Here is the thing.

Lets say we have the following data structure:

 public class Recipe
 {
   public int Id;
   public string Name;
   public List<Ingredient> IngredientList;
 }

 public class Ingredient
 {
   public int Id;
   public string Name;
 }

Now, I would like to make a query which will search all the Recipes which have ALL given ingredients.

 public List<Recipe> GetRecipesWhichHaveGivenIngredients(List<Ingredients> ingredients)
 {
   List<Recipe> recipes;

   using (DataContext context = new DataContext())
   {
    //efficient LINQ query goes here
    recipes = context.Recipes.Where(recipe => /*medaCode recipe.IngredientList.Contains(ingredients) */).ToList();
   }
   return recipes;
 }

Basically this is the problem how to determine whether a given set is a subset of another set.

I have tried with the following query (the main idea is usage of the Intersect operation):

List<Recipe> recipes = dataContext.Recipes.Include("Ingrediens").Where(rec => rec.IngredientList.Select(ingr => ingr.Id).Intersect(ingredients.Select(sy =>  sy.Id)).Count() == ingredients.Count).ToList();

But I get the following error:

Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Answer 1

Don't use a List<Ingredient> for the ingredients that you want to find; use a HashSet<Ingredient> and the IsProperSubsetOf method, which accepts a collection as its argument:

.Where(x => ingredients.IsProperSubsetOf(x.IngredientList))

In addition to being an O(n+m) operation, this has the added benefit of being code that tells you what it's doing when you look at it.

Edit

In case the above is not clear:

public List<Recipe> GetRecipesWhichHaveGivenIngredients(HashSet<Ingredient> ingredients)
{
   using (DataContext context = new DataContext())
   {
       return context.Recipes
           .Where(x => ingredients.IsProperSubsetOf(x.IngredientList)  
           .ToList();
   }
 }

Answer 2

well, if IngredientList really is a List<T>, you'll be able to do:

recipes = context.Recipes.Where(recipe => recipe.IngredientList.Exists(i => i.Id == ingredient.Id)).ToList();

but that means all the list needs to be populated. Since this looks like a LINQ to SQL query, I'm guessing IngredientList is just a connected table...? In that case, you won't have the full list, but you'll still be able to do something similar:

recipes = context.Recipes.Where(recipe => recipe.IngredientList.Count(i => i.Id == ingredient.Id) > 0).ToList();

...and it should still just query the sql server once.

EDIT

As was just pointed out in comments, this doesn't quite answer the question. As for contains-all search, I don't think it can be done without looping through the input. The good thing is that this can be done without enumerating the IEnumerable recipes, so the below code will still just hit the sql server once, with a single query:

var recipes = context.Recipes.AsEnumerable<Recipe>();

ingredients.ForEach(i =>
    var recipes = recipes.Where(r =>
        r.IngredientList.Count(ii => ii.Id == i.Id) > 0
    );
);

return recipes.ToList();

The query will not be executed until ToList() is hit.