PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Entity Framework Core : check if comma-separated string contains any value from list

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();
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0 
    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()+","))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search