Entity Framework Code First Foreign Key issue

Question

It seems to me like the ForeignKey attribute is not working for me, but I guess I'm using it wrong ;)

It's easier to explain with code:

public class BaseCard
{
    public int Id {get ; set; }
    public int BaseCardId { get; set; }

    public List<Skill> Skills { get; set; }
}

public class Skill
{
    public int Id { get; set; }

    public int BaseCardId { get; set; }
    [ForeignKey("BaseCardId")]
    public BaseCard BaseCard { get; set; }
}

When I try to fill these objects with the seed method, I'm getting this error:

INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Skills_dbo.BaseCards_BaseCardId". The conflict occurred in database "Database", table "dbo.BaseCards", column 'Id'.

It seems to me like the ForeignKey in Skill tries to point at the Id column of BaseCards instead of the BaseCardId column, and I can't figure out why..

If I try to remove the "normal" Id property of BaseCard, and set the BaseCardId as the PK (with attribute [Key]), I get the following error:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

Does anyone know how I can get this code to work so the property BaseCardId from the Skill class will point to the BaseCardId property of BaseCard, instead of apparently the Id property?

Thanks in advance!

Answer 1

It is possible set by yourself the Id of BaseCard, but first you have to use Fluent Api to specify this and the one-to-many relationship. Also, at this way, you don't have to specify attributes in your model classes. Your model classes would be like this:

public class BaseCard
{
  public int Id {get ; set; }

  public virtual ICollection<Skill> Skills { get; set; }
}

public class Skill
{
  public int Id { get; set; }

  public int BaseCardId { get; set; }
  public virtual BaseCard BaseCard { get; set; }
}

As you can see, I change the navigation properties as virtual.If you define your navigation property as virtual EF will at runtime create a new class (dynamic proxy) derived from your BaseCard class and use it instead (the same happens with Skill). This new dynamically created class contains logic to load navigation property when accessed for the first time. This feature is called lazy loading.It enables Entity Framework to avoid loading an entire tree of dependent objects which are not needed from the database. You can find more info about this subject in these posts:Why Navigation Properties are virtual by default in EF and Entity Framework 4.1 Virtual Properties.

The other change that I proporse in your model is use ICollection<> type instead List<> in the Skills property. As you can see in this post, an Interface is good practice in this case. It lets you later specify the implementation that you want (could be a List<>).

Now, back to your problem,in your Context class you need to override the OnModelCreating method to specify the relationship and the no autogenerate column for Id in the BaseCards table.

public class YourContext : DbContext
{
    public DbSet<BaseCard> BaseCards { get; set; }

    public DbSet<Skill> Skill { get; set; }

    //...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Configure the primary key for BaseCard
        modelBuilder.Entity<BaseCard>().HasKey(t => t.Id);
        //specify no autogenerate the Id Column
        modelBuilder.Entity<BaseCard>().Property(b => b.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        //one-to-many relationship 
        modelBuilder.Entity<Skill>().HasRequired(c => c.BaseCard)
                .WithMany(s => s.Skills)
                .HasForeignKey(c => c.BaseCardId);
    }
}

With this configuration, you have to set always the Id of BaseCard objects with a different value.

If you prefer use data annotations it is possible specify the same at this way:

public class BaseCard
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public virtual ICollection<Skill> Skills { get; set; }
}

public class Skill
{
    [Key]
    public int Id { get; set; }

    public int BaseCardId { get; set; }

   [ForeignKey("BaseCardId")]
    public virtual BaseCard BaseCard { get; set; }
}

My recomendation is use Fluent API, it's more flexible and you don't have to touch your model classes. You can see some useful cosiderations in this post