Entity Framework Core: many-to-many relationship with the same object

Question

I've started use Entity Framework Core. I have a User entity:

public class UserEntity
{
    public UserEntity()
    {
        ForbiddenIngredients = new HashSet<Ingredient>();
        PreferredIngredients = new HashSet<Ingredient>();
    }

    [Key]
    public string ID { get; set; }

    public string Email { get; set; } //UserName
    public string FullName { get; set; }

    public virtual ICollection<Ingredient> ForbiddenIngredients { get; set; }
    public virtual ICollection<Ingredient> PreferredIngredients { get; set; }
}

And I have also Ingredient entity:

public class Ingredient
{
    [Key]
    public string IngredientID { get; set; }

    [Required]
    public string Name { get; set; }
}

As you can see, I want to create 2 tables which collect forbidden ingredients and preferred ingredients for each user. What should I to to make it work?

Answer 1

You have to normalize your DB. The first table keeps a User with a primary key UserId , the second table - Ingridient has IngridientId as a primary key and the third table UserIngridient will keep UserId and Ingridient Id as the foreign keys. All ingridient are the same, only some of them maybe forbidden for user but it maybe not forbidden for another user . So you need a forth table - Type. But since you have only 2 types you can use a flag for this - Forbidden or Prefered. This flag should be kept in UserIngridient Table since it depends on the user and ingridient for this user.

Change your code to this:

public class User
{
    public User()
    {
        Ingredients = new HashSet<Ingredient>();
       
    }

    [Key]
    public string ID { get; set; }

    public string Email { get; set; } //UserName
    public string FullName { get; set; }

    public virtual ICollection<Ingredient> Ingredients { get; set; }
   
}

public class Ingredient
{
    [Key]
    public int ID { get; set; }

    [Required]
    public string Name { get; set; }
}

And you have to ADD one more table for normalization :

public class UserIngredient
{
    [Key]
    public int ID { get; set; }

     public int UserID { get; set; }
    public int IngredientID { get; set; }
    public int IngredientTypeId { get; set; }  // 0 or 1
   // OR   public string IngredientType { get; set; }   // Forb or Pref
.... add some virtual props  here
}

Answer 2

Answering original question about multiple foreign keys to same table. Logic is being set up in DbContext.OnModelConfiguring via Fluent API.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace ConsoleApp3
{
    public class User
    {
        public User()
        {
            ForbiddenIngredients = new HashSet<Ingredient>();
            PreferredIngredients = new HashSet<Ingredient>();
        }

        [Key]
        public string ID { get; set; }

        public string Email { get; set; }
        public string FullName { get; set; }

        public virtual ICollection<Ingredient> ForbiddenIngredients { get; set; }
        public virtual ICollection<Ingredient> PreferredIngredients { get; set; }
    }

    public class Ingredient
    {
        [Key]
        public int IngredientID { get; set; }

        [Required]
        public string Name { get; set; }

        public virtual User ForbiddenUser { get; set; }

        public string ForbiddenUserId { get; set; }

        public virtual User PreferredUser { get; set; }

        public string PreferredUserId { get; set; }
    }

    public class AppDbContext : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<Ingredient> Ingredients { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("connection string");
            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .HasMany(x => x.PreferredIngredients)
                .WithOne(x => x.PreferredUser)
                .HasForeignKey(x => x.PreferredUserId);

            modelBuilder.Entity<User>()
                .HasMany(x => x.ForbiddenIngredients)
                .WithOne(x => x.ForbiddenUser)
                .HasForeignKey(x => x.ForbiddenUserId);

            base.OnModelCreating(modelBuilder);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var user = new User()
            {
                Email = "test@test.com",
                ID = Guid.NewGuid().ToString(),
                FullName = "name",
            };

            user.ForbiddenIngredients.Add(new Ingredient()
            {
                ForbiddenUser = user,
                Name = "forbidden",
            });

            user.PreferredIngredients.Add(new Ingredient()
            {
                PreferredUser = user,
                Name = "preferred",
            });

            var dbContext = new AppDbContext();

            dbContext.Add(user);
            dbContext.SaveChanges();

            var dbUser = dbContext.Users
                .Include(x => x.ForbiddenIngredients)
                .Include(x => x.PreferredIngredients)
                .FirstOrDefault();
        }
    }
}

Answer 3

Your database relationship designing was wrong. You should add more a field to Ingredient to point out Forbidden or Preferred (tinyint type).