Foreign key constraint when there's a navigation property to a derived class

Question

I'm using code-first to create my databases in Entity Framework, and am using the Table per Hierarchy approach for the inheritance hierarchy for the class shown below.

When I run a migration everything works, but when I update my database I get the following error:

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.BaseStatus_dbo.Rider_ID". The conflict occurred in database "ASPNET_INSURANCE_c48b7267b7cd4bac952caa1a467ab9ae", table "dbo.Rider", column 'ID'.

I have a class that has a navigation property to a derived class. (Simplified code)

    public class Rider
    {
       public int ID { get; set; }
       public virtual DerivedStatus derivedStatus { get; set; }
    }

The derived classes with data annotations look like so:

    public class BaseStatus
    {
        [Key, ForeignKey("rider")]
        public int ID { get; set; }

        public bool? Married { get; set; }

        public virtual Rider rider { get; set; }
    }

    public class DerivedStatus : BaseStatus
    {
        public bool? HomeOwnership { get; set; }
    }

For the DbContext I have just added the following to create the table which works as expected (all properties in one table with a Discriminator column).

public DbSet Status { get; set; }

The relationship between the Rider and the derived class is one-to-one.

I'm not sure if it's relevant/of help, but below is part of the DbMigration file which is generated:

                CreateTable(
                "dbo.Rider",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true),
                        CorrelationID = c.Guid(),
                        derivedStatus_ID = c.Int(),
                    })
                .PrimaryKey(t => t.ID)
                .ForeignKey("dbo.BaseStatus", t => t.derivedStatus_ID)
                .Index(t => t.derivedStatus_ID);

When updating the database I'm also seeding it with some test data which I guess is when it picks up on this problem.

This is the first time I'm using inheritance in Entity and I'm not sure where the problem lies. I've tried dropping my database and re-running migrations as I'd read this can sometimes cause problems but I got the same error message.

Going forward there will likely be further derived classes of both the BaseStatus and Rider classes, with different derived Riders using different Status classes so any solution must be scalable. It may be that when the project grows I'll change from Table per Hierarchy to Table per Type, but for now I'd like a solution for TPH (if it's possible)

user_2131539 · Answer

check the values which are in the table, Are these value are valid (follow Foreign key constraint)

user_10015021 · Answer

I have finally found the answer!!

Part of the problem was I didn't know the phrase for what I was trying to accomplish so it made searching for answers difficult. If you are trying to achieve the same thing as me the phrase that you need is Polymorphic Associations. I finally found what I was looking for in this article here.

To correct my code all I needed to do was remove the foreign key and navigation property from the dependent classes, whilst keeping the navigation property in Rider.

    public class Rider
    {
       public int ID { get; set; }
       public virtual DerivedStatus derivedStatus { get; set; }
    }

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

        public bool? Married { get; set; }

    }

    public class DerivedStatus : BaseStatus
    {
        public bool? HomeOwnership { get; set; }
    }

DbContext remains the same as before. Having tested this, this also works for TPT, you just need to add data annotations to make Entity created separate tables.

UPDATE: After some more testing, I have found that you can use Foreign Key and navigational properties on the base class but that you need to supplement the data annotations with Fluent API as I can't seem to get it to work on it's own. The Fleunt API I used was:

modelBuilder.Entity()
                .HasRequired(e => e.Rider)
                .WithOptional(e => e.DerivedStatus)
                .WillCascadeOnDelete(true);

Foreign key constraint when there's a navigation property to a derived class

2 Answers