Cannot create a relationship between Employee and StateProvince

Viewed 69

I have an initial class Employee with two properties StateOfEmployment, SecondaryStateOfEmployment of the same entity which is StateProvince.

I'm having this error:

{​​​​​​"Cannot create a relationship between 'StateProvince.Employee' and 'Employee.SecondaryStateProvinceOfEmployment', because there already is a relationship between 'StateProvince.Employee' and 'Employee.StateProvinceOfEmployment'. Navigation properties can only participate in a single relationship."}​​​​​​

My class

    public partial class Employee
    {
        public long EmployeeId { get; set; }
        public string JobTitle { get; set; }
        public string EmployeeCode { get; set; }
        public long? CountryOfEmploymentId { get; set; }
        public long? StateProvinceOfEmploymentId { get; set; }
        public long? SecondaryStateProvinceOfEmploymentId { get; set; }
        public string CostCenter { get; set; }
        public DateTime StartEffDate { get; set; }
        public DateTime CreatedTimestamp { get; set; }
        public string CreatedUser { get; set; }
        public DateTime UpdatedTimestamp { get; set; }
        public string UpdatedUser { get; set; }
        public bool IsDeleted { get; set; }
        public long ClientId { get; set; }
        public long PersonId { get; set; }
        public string CityOfEmployment { get; set; }
        public DateTime? HireDate { get; set; }
        public string BusinessUnit { get; set; }
        public string ManagerName { get; set; }
        public string ManagerEmail { get; set; }
        public long? EmploymentStatusId { get; set; }
        public long? EmploymentTypeId { get; set; }
        public string EmploymentTypeClientString { get; set; }
        public string HomeLegalEntity { get; set; }
        public bool Executive { get; set; }
        public string JobLevel { get; set; }
        public DateTime SysStartTime { get; set; }
        public DateTime SysEndTime { get; set; }

        public virtual Client Client { get; set; }
        public virtual Country CountryOfEmployment { get; set; }
        public virtual EmploymentStatus EmploymentStatus { get; set; }
        public virtual EmploymentType EmploymentType { get; set; }
        public virtual Person Person { get; set; }
        public virtual StateProvince StateProvinceOfEmployment { get; set; }
        public virtual StateProvince SecondaryStateProvinceOfEmployment { get; set; }

    }

And my context is

            modelBuilder.Entity<Employee>(entity =>
            {
                entity.Property(e => e.BusinessUnit).HasMaxLength(100);

                entity.Property(e => e.CityOfEmployment).HasMaxLength(100);

                entity.Property(e => e.CostCenter).HasMaxLength(100);

                entity.Property(e => e.CreatedTimestamp).HasDefaultValueSql("(getutcdate())");

                entity.Property(e => e.CreatedUser)
                    .IsRequired()
                    .HasMaxLength(100)
                    .HasDefaultValueSql("(suser_sname())");

                entity.Property(e => e.EmployeeCode).HasMaxLength(100);

                entity.Property(e => e.EmploymentTypeClientString).HasMaxLength(100);

                entity.Property(e => e.HomeLegalEntity).HasMaxLength(100);

                entity.Property(e => e.JobTitle).HasMaxLength(100);

                entity.Property(e => e.ManagerEmail).HasMaxLength(100);

                entity.Property(e => e.ManagerName).HasMaxLength(200);

                entity.Property(e => e.UpdatedTimestamp).HasDefaultValueSql("(getutcdate())");

                entity.Property(e => e.UpdatedUser)
                    .IsRequired()
                    .HasMaxLength(100)
                    .HasDefaultValueSql("(suser_sname())");

                entity.HasOne(d => d.Client)
                    .WithMany(p => p.Employee)
                    .HasForeignKey(d => d.ClientId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Employee_Client");

                entity.HasOne(d => d.CountryOfEmployment)
                    .WithMany(p => p.Employee)
                    .HasForeignKey(d => d.CountryOfEmploymentId)
                    .HasConstraintName("Fk_Employee_CountryId");

                entity.HasOne(d => d.EmploymentStatus)
                    .WithMany(p => p.Employee)
                    .HasForeignKey(d => d.EmploymentStatusId)
                    .HasConstraintName("Fk_Employee_EmploymentStatusId");

                entity.HasOne(d => d.EmploymentType)
                    .WithMany(p => p.Employee)
                    .HasForeignKey(d => d.EmploymentTypeId)
                    .HasConstraintName("Fk_Employee_EmploymentTypeId");

                entity.HasOne(d => d.Person)
                    .WithMany(p => p.Employee)
                    .HasForeignKey(d => d.PersonId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Employee_Person");

                entity.HasOne(d => d.StateProvinceOfEmployment)
                    .WithMany(p => p.Employee)
                    .HasForeignKey(d => d.StateProvinceOfEmploymentId)
                    .HasConstraintName("Fk_Employee_StateProvinceId");

                entity.HasOne(d => d.SecondaryStateProvinceOfEmployment)
                   .WithMany(p => p.Employee)
                   .HasForeignKey(d => d.SecondaryStateProvinceOfEmploymentId)
                   .HasConstraintName("Fk_Employee_SecondaryStateProvinceId");
            });

What am I doing wrong? Thank you.

2 Answers

Use DataAnnotations such as ForeignKey attribute, if you use it you don't need to add any code to the OnModelCreate method:

    [Table("Employee")]

    public partial class Employee
    {
        [Key]
        public long EmployeeId { get; set; }

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

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

        [Column("CountryOfEmploymentId")]
        [ForeignKey("CountryOfEmployment")]
        public long? CountryOfEmploymentId { get; set; }

        [Column("StateProvinceOfEmploymentId")]
        [ForeignKey("StateProvinceOfEmployment")]
        public long? StateProvinceOfEmploymentId { get; set; }

        [Column("SecondaryStateProvinceOfEmploymentId")]
        [ForeignKey("SecondaryStateProvinceOfEmployment")]
        public long? SecondaryStateProvinceOfEmploymentId { get; set; }

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

        [Column("StartEffDate")]
        public DateTime StartEffDate { get; set; }

        [Column("CreatedTimestamp")]
        public DateTime CreatedTimestamp { get; set; }

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

        [Column("UpdatedTimestamp")]
        public DateTime UpdatedTimestamp { get; set; }

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

        [Column("IsDeleted")]
        public bool IsDeleted { get; set; }

        [Column("ClientId")]
        public long ClientId { get; set; }

        [Column("PersonId")]
        public long PersonId { get; set; }

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

        [Column("HireDate")]
        public DateTime? HireDate { get; set; }

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

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

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

        [Column("EmploymentStatusId")]
        public long? EmploymentStatusId { get; set; }

        [Column("EmploymentTypeId")]
        public long? EmploymentTypeId { get; set; }

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

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

        [Column("Executive")]
        public bool Executive { get; set; }

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

        [Column("SysStartTime")]
        public DateTime SysStartTime { get; set; }

        [Column("SysEndTime")]
        public DateTime SysEndTime { get; set; }

        [IgnoreDataMember]
        public virtual Client Client { get; set; }

        [IgnoreDataMember]
        public virtual Country CountryOfEmployment { get; set; }

        [IgnoreDataMember]
        public virtual EmploymentStatus EmploymentStatus { get; set; }

        [IgnoreDataMember]
        public virtual EmploymentType EmploymentType { get; set; }

        [IgnoreDataMember]
        public virtual Person Person { get; set; }

        [IgnoreDataMember]
        public virtual StateProvince StateProvinceOfEmployment { get; set; }

        [IgnoreDataMember]
        public virtual StateProvince SecondaryStateProvinceOfEmployment { get; set; }
    }

Below should work,

 entity.HasOne(d => d.SecondaryStateProvinceOfEmployment)
                       .WithMany(p => p.Employee)
                       .HasForeignKey(d => d.SecondaryStateProvinceOfEmploymentId)
                       .HasConstraintName("Fk_Employee_SecondaryStateProvinceId").OnDelete(DeleteBehavior.Restrict);
Related