I'm using EF Core to model an old legacy database. One table uses a comma separated list to reference another table instead of a proper foreign key. How can I model this with EF as a navigation property?

Tables:

recipe                         ingredient

id  | ingredients              id  | name
----+--------------            ----+----------
 1  | "1,2,3"                   1  | "flour"
 2  | "3,4"                     2  | "sugar" 
 3  | "2"                       3  | "egg"
 4  | "1,2,3,4"                 4  | "butter"

EF classes:

public class Recipe
{
    [Column("id")]
    [Key]
    public int Id {get; set;}

    [Column("ingredients")]
    public string IngredientIds {get; set;}

    // How to implement this?
    public virtual ICollection<Ingredient> Ingredients {get; set;}
}

public class Ingredient
{
    [Column("id")]
    [Key]
    public int Id {get; set;}

    [Column("name")]
    public string Name {get; set;}

    // This would also be nice to have
    public virtual ICollection<Recipe> Recipes{get; set;}
}

I'm using EF Core to model an old legacy database. One table uses a comma separated list to reference another table instead of a proper foreign key. How can I model this with EF as a navigation property?

Tables:

recipe                         ingredient

id  | ingredients              id  | name
----+--------------            ----+----------
 1  | "1,2,3"                   1  | "flour"
 2  | "3,4"                     2  | "sugar" 
 3  | "2"                       3  | "egg"
 4  | "1,2,3,4"                 4  | "butter"

EF classes:

public class Recipe
{
    [Column("id")]
    [Key]
    public int Id {get; set;}

    [Column("ingredients")]
    public string IngredientIds {get; set;}

    // How to implement this?
    public virtual ICollection<Ingredient> Ingredients {get; set;}
}

public class Ingredient
{
    [Column("id")]
    [Key]
    public int Id {get; set;}

    [Column("name")]
    public string Name {get; set;}

    // This would also be nice to have
    public virtual ICollection<Recipe> Recipes{get; set;}
}

• c#
• entity-framework-core

Share Improve this question Follow edited Nov 19, 2024 at 17:16 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Nov 19, 2024 at 8:13 weksowekso 8522 silver badges1414 bronze badges 9

• Is this really a SQL language question? (I see no SQL above, do you expect a SQL answer?) – jarlh Commented Nov 19, 2024 at 8:17
• Sample data is great, but can you also specify the expected? I.e. provide a complete minimal reproducible example. – jarlh Commented Nov 19, 2024 at 8:18
• Can you elaborate? The expected is that ingredients would be joinable with include, as they would if the tables were joined using a proper foreign key. – wekso Commented Nov 19, 2024 at 8:23
• 1 To check, do you have the option to refactor your database / to create a RecipeIngredient table with columns RecipeId & IngredientId to create this association? It'll give much better performance & will help avoid various pitfalls (e.g. ensuring unique entries for recipe-ingredient pairs); so is the better route to go down vs adding a workaround solution on top of a bad implementation. – JohnLBevan Commented Nov 19, 2024 at 8:24
• 2 This is a terrible design, impossible to index or search. You could use a computed property that maps to an expression using regexp_split_to_array to convert this to an array but you can't define relations on this. Even with manually written SQL you'd have to use some tricky operands to JOIN between an ID and the contents of an array – Panagiotis Kanavos Commented Nov 19, 2024 at 17:32

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 2

To bring this question to a conclusion: no this isn't possible. EF navigation properties can be defined between scalar properties in an equals relationship, not contains.

Contains would be problematic anyway, because a more realistic example like "251,21223,1453,21569" would match a large range of Id values, most of them unintended: 2, 5, 25, 251, 21, 212, ... (you get the picture).

You are aware that this is bad design. To make this a little bit workable, and if it's allowed to add views to the database, you could consider adding a view like this one (in SQL Server, but there are equivalents in other database providers):

CREATE VIEW IngredientsWithRecipeId
AS
SELECT ing.Id
    , ing.Name
    , r.Id AS RecipeId
FROM [dbo].[Recipe] r
OUTER APPLY string_split(r.[IngredientIds] ,',') AS ids
INNER JOIN [dbo].[Ingredient] ing ON ids.value = ing.Id

Then you could map an entity to it in EF and use navigation properties based on this view.

Obviously, the view does not return any rows for non-existing Id values in IngredientIds, but that's a consequence of this design totally lacking any referential integrity. Also, its performance is likely to be incurably poor.