I have the following table:

recipe            

id  | ingredients     
----+--------------
 1  | "1,2,3"          
 2  | "3,4"           
 3  | "2"        
 4  | "1,2,3,4" 

I want to find all recipes containing ingredient "1", "3" or "4". How can I achieve that using Entity Framework Core?

Here's what I've tried, but it seems like this expression is not translatable:

var ingredientIds = new List<string> {"1", "3", "4"};

var recipes = dbContext.Set<Recipe>
                       .Where(x => x.IngredientIds.Split(',', StringSplitOptions.None).Any(y => ingredientIds.Contains(y))
                       .ToList();

I have the following table:

recipe            

id  | ingredients     
----+--------------
 1  | "1,2,3"          
 2  | "3,4"           
 3  | "2"        
 4  | "1,2,3,4" 

I want to find all recipes containing ingredient "1", "3" or "4". How can I achieve that using Entity Framework Core?

Here's what I've tried, but it seems like this expression is not translatable:

var ingredientIds = new List<string> {"1", "3", "4"};

var recipes = dbContext.Set<Recipe>
                       .Where(x => x.IngredientIds.Split(',', StringSplitOptions.None).Any(y => ingredientIds.Contains(y))
                       .ToList();

• c#
• postgresql
• entity-framework-core

Share Improve this question Follow edited Nov 20, 2024 at 13:36 Poul Bak 11k55 gold badges3838 silver badges6969 bronze badges asked Nov 19, 2024 at 10:33 weksowekso 8522 silver badges1414 bronze badges 5

• What database are you using? – Guru Stron Commented Nov 19, 2024 at 10:42
• I'm using postgresql. – wekso Commented Nov 19, 2024 at 10:44
• use contains after splitting, that will help you to find the value exist or not in the columns, second approach is if you are using native Query then it will be easier SELECT * FROM [OneWindowPRD].[dbo].[recipe] WHERE [ingredients] like '%1%' OR [ingredients] like '%3%' OR [ingredients] like '%4%' – Arpit Sharma Commented Nov 19, 2024 at 10:50
• That's not a good design. PostgreSQL has arrays that can be indexed and searched. Either use a RecipeIngredients table or change the field type to int[]. That array can be mapped to an int[] or List<int> property. BUT it's infinitely better to have proper tables so you can have a Recipe.Ingredients property that's an actual List<Ingredient> – Panagiotis Kanavos Commented Nov 19, 2024 at 11:59
• This is an old database and I cannot change the design. – wekso Commented Nov 20, 2024 at 6:41

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 5

Well, you shouldn't be using comma delimited values in the first place, that's what's causing your problem.

The solution would be to change the database design to create a proper many-to-many relationship between recipe and ingredients.

In a DB first design, you'll add an IngredientToRecipe table:

CREATE TABLE IngredientToRecipe 
(
    recepieId int NOT NULL,
    IngredientId int NOT NULL,
    CONTRAINT PrimaryKey Pk_IngredientToRecipe (recepieId, IngredientId)
)

In a Code first design, you'll have to add navigation properties to your entities as collections:

class Recipe
{
  public List<Ingredient> Ingredients {get;set;}
  // other properties here
}

class Ingredient
{
  public List<Recipe> Recipes {get;set;}
  // other properties here
}

EF Core will know how to translate this to a many-to-many relationship.

var newrecipes = (from r1 in recipes
                  from i in ingredients
                  where r1.IngredientIds.Contains(i.Id.ToString())
                  select new { recipe = r1.Id, ingedientId = i.Id, ingredientName = i.Name }
                  );
foreach (var item in newrecipes )
{
System.Console.WriteLine($"recipe {item.recipe}: ingredient: {item.ingedientId}, {item.ingredientName}");
}

outputs (when using the data from you other question: Entity Framework Core : join comma-separated list ):

recipe 1: ingredient: 1, flour
recipe 1: ingredient: 2, sugar
recipe 1: ingredient: 3, egg
recipe 2: ingredient: 3, egg
recipe 2: ingredient: 4, butter
recipe 3: ingredient: 2, sugar
recipe 4: ingredient: 1, flour
recipe 4: ingredient: 2, sugar
recipe 4: ingredient: 3, egg
recipe 4: ingredient: 4, butter

EDIT: When ingredient like 100, or any ingredient with more than 1 digit, is added a small change is needed in the where part:

.Where(x => (x.r.IngredientIds+",").Contains(x.i.Id.ToString()+","))

EDIT2: (see comments, to get the difference between, for example, 21 and 121 correct):

• I also added a Trim() around IngredientIds, to make sure that does not end with 1 or more spaces.

.Where(x => ("," + x.r.IngredientIds.Trim() + ",").Contains("<" + x.i.Id.ToString() + ","))

FINAL EDIT (