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.

Homunculus

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();
   }
 }

Gunfire

Ok, but how would I use this in LINQ to Entities? – Homunculus

@peter: EF3.5 cannot create SQL for IsProperSubsetOf. NotSupportedExcpetion: LINQ to Entities does not recognize the method 'Boolean IsProperSubsetOf(System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression – Winni

in the end I am using David's approach, of filtering afterwards in-memory. I know EF SQL supports IN operation - so there must be a way to this in via LINQ to EF ? – Winni

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.

Kong

Where do you get ingredient object (ingredient.Id)? This method gets ingrediens (which is IEnumerable<T>). You do not use it. – Homunculus

hey, sorry. then i missed the point of the question entirely :o i read it as just a find-occurrence-within-inner-list question. i'll edit accordingly – Kong

Answer 3

don't know if this will work in Linq2SQL, but in Linq2Object, this works:

public static class Util
{
    public static List<Recipe> GetRecipesWhichHaveGivenIngredients(this List<Recipe> recipies, List<Ingredient> ingredients)
    {
        int icount=ingredients.Count;

        var res = recipies.Where(r => r.IngredientList.Where(i => ingredients.Contains(i)).Count() == icount).ToList();
        return res;
    }
}

Brakeman

Answer 4

The already given answers either don't work for Linq2SQL, or they don't answer the question, or they don't work at all.

This should work both for Linq2SQL and Linq2Objects:

using System;
using System.Collections.Generic;
using System.Linq;
public List<Recipe> GetRecipesWhichHaveGivenIngredients(List<Ingredients> ingredients)
{
    List<Recipe> recipes;

    // Since Linq2SQL cannot convert Ingredients, but only value types,
    // use only the ingredients' identifiers
    List<int> ingredientIds = ingredients.Select(i => i.Id).ToList();

    using (DataContext context = new DataContext())
    {
        recipes = context.Recipes.Where(recipe => 
            ingredientIds.All(ingredientId => 
                recipe.IngredientList.Any(ingredient => 
                    ingredient.Id == ingredientId
                )
            )
        ).ToList();
    }
    return recipes;
}

This returns the recipes that contain all the ingredients from the method argument and it returns all recipes if that list is empty: the method argument works as a restriction on the recipes to retrieve.

Binturong

Wrong. Get all recipes of which some (not all) ingredients are in the specified ingredients. – Year

@GertArnold, how so? For all ingredientId values in ingredientIds, the condition for each recipe is that any of it's IngredientList entries is the referenced ingredient. A recipe is only selected if it is true for all inredientId values. – Binturong

Yeah, unfortunately OP is not clear in what "all" is exactly. I read it as: recipes consisting of exactly these ingredients (not less, but also: not more). – Year

Ah, I see. Indeed, my suggestion does not get recipes with an exact match of the list of ingredients. – Binturong