CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6one-to-one
Jacob Horbulyk
Jacob Horbulyk

Reputation: 2646

Entity Framework 6: Create 0..0:1 mapping where both tables have foreign keys of different types

I have a database with the following structure:

Create Table Employees
(
    EmployeeId INT NOT NULL PRIMARY KEY,
    Name NVARCHAR(20) NOT NULL,
    CertificationId UNIQUEIDENTIFIER NULL
);

Create Table Certifications
(
    CertificationId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
    EmployeeIssuedToId INT NOT NULL FOREIGN KEY REFERENCES Employees(EmployeeId),
    ExpiryDate DATETIME2 NOT NULL
);

ALTER TABLE Employees 
    ADD FOREIGN KEY (CertificationId) 
        REFERENCES Certifications(CertificationId);

In the database you can have employees who may or may not have a certification but every certification has a corresponding employee. I have the following EF classes:

    public partial class Certification
    {
        [Key]
        public Guid CertificationId { get; set; }

        public int EmployeeIssuedToId { get; set; }
        [Required, ForeignKey("EmployeeIssuedToId")]
        public virtual Employee EmployeeIssuedTo { get; set; }

        [Column(TypeName = "datetime2")]
        public DateTime ExpiryDate { get; set; }
    }

    public partial class Employee
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int EmployeeId { get; set; }

        [Required]
        [StringLength(20)]
        public string Name { get; set; }

        public Guid? CertificationId { get; set; }
        [ForeignKey("CertificationId")]
        public virtual Certification Certification { get; set; }
    }

    public partial class EmployeeCertificationModel : DbContext
    {
        public EmployeeCertificationModel()
            : base("name=DbConnString")
        {
        }

        public virtual DbSet<Certification> Certifications { get; set; }
        public virtual DbSet<Employee> Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Employee>()
                .HasOptional(e => e.Certification)
                .WithOptionalDependent(c => c.EmployeeIssuedTo);
        }
    }

I then try to execute the following code:

        using (var writeContext = new EmployeeCertificationModel())
        {
            var fred = new Employee
            {
                EmployeeId = 1,
                Name = "Fred"
            };
            writeContext.Employees.Add(fred);
            writeContext.SaveChanges();

            fred.Certification = new Certification
            {
                CertificationId = new Guid("00000000-0000-0000-0000-000000000001"),
                EmployeeIssuedToId = fred.EmployeeId,
                ExpiryDate = DateTime.Today.AddDays(7)
            };
            writeContext.SaveChanges();
        }

However, that results in the following error:

System.InvalidOperationException : The ForeignKeyAttribute on property 'EmployeeIssuedTo' on type 'EmployeeCertifications.Certification' is not valid. The foreign key name 'EmployeeIssuedToId' was not found on the dependent type 'EmployeeCertifications.Employee'. The Name value should be a comma separated list of foreign key property names.

What am I doing wrong here?

Upvotes: 1

Views: 54

Answers (1)

Sarah Cartwright
Sarah Cartwright

Reputation: 136

I'd recommend avoiding this non-normalized schema in the first place. So your first table declaration becomes: 

Create Table Employees
(
    EmployeeId INT NOT NULL PRIMARY KEY,
    Name NVARCHAR(20) NOT NULL
);

And you can define the property in your model with an InverseProperty attribute.

public partial class Employee
{
    ...

    [InverseProperty("EmployeeIssuedToId")]
    public virtual Certification Certification { get; set; }
}

Upvotes: 1

Related Questions