Error Seeding Database, foreign key issue

Question

I have built out my models using POCO. When I go to seed my database I get:

Unable to determine the principal end of the 'CSP.Models.Type_Color' relationship. Multiple added entities may have the same primary key

Here's the models in question:

public class Type
{
    [Key]
    [ScaffoldColumn(false)]
    public int TypeId { get; set; }

    [Required(ErrorMessage = "A Type Name is Required")]
    [Display(Name="Type")]
    public string Name { get; set; }

    public int ColorId { get; set; }
    public bool Other { get; set; }

    //Navigations
    [ForeignKey("ColorId")]
    public virtual Color Color { get; set; }
    public virtual List<Tools> tools { get; set; }

}

public class Color
{
    [Key]
    [ScaffoldColumn(false)]
    public int ColorId { get; set; }

    [Required(ErrorMessage = "A name is required")]
    public string Name { get; set; }

    public string Description { get; set; }

    //navigation
    public virtual List<Type> Types { get; set; }
}

Most of the markup I did after reading suggestions.

My seed code that is getting the error is here:

var colors = new List<Color>
        {
            new Color{Name="Red"},
            new Color{Name="White"}
        };

            var types = new List<Type>
        {
            new Type{ Name="Hammer", Color = colors.Where(ws => ws.Name=="Red").Single()},
            new Type{ Name= "Electric", Color = colors.Where(ws => ws.Name=="Red").Single()}
        };

new List<Tool>
        {
            new Wine{ Maker= Maker.Single(v => v.Name=="HammerCo"), Type= types.Single(wt => wt.Name=="hammer")},
        }
        }.ForEach(a => context.Tools.Add(a));
            context.SaveChanges();

I also tried adding each value to the context and then saving. I got this error after it tried saving the type entity:

[System.Data.SqlClient.SqlException] = {"The INSERT statement conflicted with the FOREIGN KEY constraint \"Type_Color\". The conflict occurred in database \"TestTools\", table \"dbo.Colors\", column 'ColorId'.\r\nThe statement has been terminated."}

What am I missing?

you spelled "hammer" without a capital H the second time. Apart from that try to make the ColorID an int? (`Nullable`) — Dabblernl, Jul 22 '12 at 19:27

score 7 · Accepted Answer · answered Jul 22 '12 at 21:42

7

What is happening is your objects all have the default int value (0) for their primary key. When you add them to the context, EF detects this and throws an error (two objects of the same type cannot have the same key, in this case, 0. I assume your primary key fields in the database are set as IDENTITY columns and will auto increment +1 on insert. This may sound odd, but you need to give your objects placeholder IDs which will be replaced on insert with the IDENTITY values.

new Color{ColorId = 1, Name="Red"},
new Color{ColorId = 2, Name="White"}
new Type{TypeId = 1, Name="Hammer", ...}
new Type(TypeId = 2, Name="Electric", ...}

answered Jul 22 '12 at 21:42

cygnim

1,985
2
16
22

That did it! That does seem strange, It seems to automatically handle the IDs before I added the color table to the mix, but forcing it in worked. – Snowburnt Jul 23 '12 at 00:33
1

Thanks, Your Answer helped me on seeding my new db with db context – Kayvan Karim Feb 03 '13 at 20:39
And me! This trick wasn't required with a simple two table structure but when I added more complex secondary relationships the db context seed process lost it's way. – Xcheque Jul 05 '13 at 23:50
This helped me too! Simple, but very effective! Thanks! – PGallagher Jul 27 '13 at 13:05

Error Seeding Database, foreign key issue

1 Answers1

Linked