Use one entity field as a key in two relationships

Question

I have an ordering system which has 2 actors: Customer and OrderPicker. Both of them should be authenticated through the User entity. I want User's Id property to be a key in both Customer and OrderPicker relationships so that User references either Customer or OrderPicker by its Id. I seed db with DataGenerator class. For example, I try to seed db with the following entities:

Users: 
    [
   {
      "Id":"guid_a",
      "Role":"Customer",
      "Password":"pwd_a"
   },
   {
      "Id":"guid_b",
      "Role":"Picker",
      "Password":"pwd_b"
   }
]

Customers:   
[
   {
      "Id":"guid_a",
      "Name":"customer_name"
   }
]   

OrderPickers:
[
   {
      "Id":"guid_b",
      "Name":"picker_name"
   }
]

I try this:

    public class User
    {
        [Key]
        public Guid Id { get; private set; }
        public virtual Customer? Customer { get; private set; }
        public virtual OrderPicker? OrderPicker { get; private set; }
        [Required]
        public string Role { get; private set; }
        [Required]
        [DataType(DataType.Password)]
        [StringLength(8)]
        public string Password { get; private set; }
    }
    public class OrderPicker
    {
        [Key, ForeignKey(nameof(User))]
        public Guid Id { get; private set; }
        public virtual User User { get; set; }
        [Required]
        public string Name { get; private set; }
        public ICollection<Order>? Orders { get; private set; } = new List<Order>();
    }
    public class Customer
    {
        [Key, ForeignKey(nameof(User))]
        public Guid Id { get; private set; }
        public virtual User User { get; set; }

        [Required]
        public string Name { get; private set; }
        [Required]
        public string Address { get; private set; }
        [Required]
        [DataType(DataType.PhoneNumber)]
        public string Phone { get; private set; }
        [Required]
        [DataType(DataType.EmailAddress)]
        public string Email { get; private set; }
        public ICollection<Order>? Orders { get; private set; } = new List<Order>();
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        DataGenerator.InitData();

        modelBuilder
          .Entity<User>()
          .HasOne(u => u.Customer)
          .WithOne(c => c.User);
        modelBuilder
          .Entity<User>()
          .HasOne(u => u.OrderPicker)
          .WithOne(oi => oi.User);
        modelBuilder.Entity<User>().HasData(DataGenerator.Users);
        modelBuilder.Entity<Customer>().HasData(DataGenerator.Customers);
        modelBuilder.Entity<OrderPicker>().HasData(DataGenerator.OrderPickers);
    }

but it does not work, I get the following error:

The seed entity for entity type 'Customer' cannot be added because another seed entity with the key value 'Id:some_alphanumeric_guid' has already been added.

What am I doing wrong?

score 0 · Accepted Answer · answered May 08 '23 at 09:47

The original problem cannot be solved and is a consequence of a poor entity design. One column cannot be referred as many foreign keys in database (read this for details). The solution proposed in above mentioned topic is to not use a FK constraint but to use fields as is (for example, select necessary entities with FirstOrDefault(x => x.Key)).

Use one entity field as a key in two relationships

1 Answers1