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!

Solution

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

Comments

[Sefa]

why you have Id and BaseCardId fields in your BaseCard object?

[Rob]

@vgSefa I set the BaseCardId to a certain number. And the Id, I thought, was automatically generated. But it needs to be a specific number. Is it possible to set the Id to a specific number?

[Rob]

@vgSefa I tried to set the Id instead of the BaseCardId to the numbers I require, unfortunatly that doesn't work..

[Gert Arnold]

Set the [Key] attribute to explicitly indicate the primary keys. (This was suggested in a now deleted answer).

[Sefa]

@Rob As Gert suggested, set the [Key] attribute. And keep in mind that when you make an update you need to update the database too. There are many basic tutorials about EF, you might wanna take a quick look at them.

[Rob]

Thanks alot for the detailed explanation, and giving me more then 1 option. I'm going to do some more research and see which one I'm going to use. Thanks again.

[Rob]

octavioccl, When I use the Fluent API, I get the an error when calling the update-database -verbose. This calls the Seed method from migration. Here I try to fill the database but when saving a BaseCard to the context I get the following error: Cannot insert explicit value for identity column in table 'BaseCards' when IDENTITY_INSERT is set to OFF. Is this because the OnModelCreating method does not get called when migrating to the DB? How else do you suggest I can fill the database?

[ocuenca]

Did you delete the old database and create a new one?. I think this is because you have an old database with the column Id of BaseCards as Identity.

[ocuenca]

I suggest you use the DropCreateDatabaseIfModelChanges Initializer. Check this page to see how to use it:entityframeworktutorial.net/code-first/…. At this way, every time that model changes the database will be dropped

[brsfan]

I've been fighting this for two days., and what fixed it? "As you can see, I change the navigation properties as virtual." - This is what finally got me over the hump. My Foreign Key property left the "virtual" modifier out.