Adding foreign key with new table with automatic migrations entity framework mvc c#

Question

I would like to add a new table and a foreign key of an existing table in SQL Server with code first automatic migrations. The only problem I have is that there is data in the table and I can't set the foreign key to a default value for existing data.

Exemple :

Cars table

NameColor
MatrixBlue
CamryRed

Now I want to add the Brand table :

Cars table

IdNameColorBrandId
1MatrixBlue1
2CamryRed1

Brand table

IdName
1Toyota

The question is : How can I set the default BrandId to 1 in my Model Car without having a foreign key constraint error when I run Update-Database command?

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Cars_dbo.Brands_BrandId". The conflict occurred in database "Example", table "dbo.Brands", column 'Id'.

For now, In my Configuration.cs file, I add my car brands like this :

context.Brands.AddOrUpdate(
                new Brand{ Id = 1, Name = "Toyota" }
           );

In My context, I override protected override void OnModelCreating ( DbModelBuilder modelBuilder ) and apply this relation :

modelBuilder.Entity<Car>().HasRequired<Brand>(t => t.Brand);

In my Car model :

[Required( ErrorMessage = "The brand is mandatory" )]
public int BrandId { get; set; }
[ForeignKey( "BrandId" )]
public virtual Brand Brand { get; set; }

In the constructor of the Car model, I set the default value to the BrandId :

public Car() 
{
    BrandId = 1;
}

George Mauer · Accepted Answer · 2015-01-07T19:55:24.517

2

"Automatic migration" in EF is a bit of an ambiguous term but if you mean the one which attempts to modify your db for you, you can't. You have to use the form of migrations** where you are running Add-Migration from the visual studio console to generate migration classes. This is generally a much much better way of handling migrations anyway.

For this, I would I would typically use the Sql function to disable constraint checking.

Sql("ALTER TABLE Cars NOCHECK CONSTRAINT ALL")

add the FK, do some logic to populate that column, and then enable constraint checking again.

Sql("ALTER TABLE Cars CHECK CONSTRAINT ALL")

Another possibility, is maybe the cars already in the system you want to have null brands, but any future cars would have to reference an existing brand. In that case you allow NULL in that column, and handle the validation requirement in code.

In fact you should always handle all validation in code, never rely on your database to do validation for you.

** These are still occasionally referred to as Automatic Migrations since typically you set them to be applied automatically when your application runs.

edited Jan 07 '15 at 19:55

answered Jan 07 '15 at 19:41

George Mauer

117,483
131
382
612

I used automatic migrations because I though it was a simple database easy to update – Simon Paquet Jan 07 '15 at 20:04
@SimonPaquet sure, its fairly common to start with the automatically generated migrations and then switch to explicit migrations when needed. Sounds like you've hit that point, afterall, how can EF possibly know what value you want to put in that column? – George Mauer Jan 07 '15 at 20:17
I thought that by using the constructor in my model, It would set the default value. Looks like I've hit a wall here... – Simon Paquet Jan 07 '15 at 20:47
yup, the migration itself needs to know what the value should be, the only way to do that is with explicit migrations. The form you're using is not recommended for use with an existing database. It should only be used when on every change you drop, rebuild, and reseed the db. – George Mauer Jan 07 '15 at 20:50
Thanks, I finnaly used named migrations and built them from the ground up. It's working now! – Simon Paquet Jan 07 '15 at 21:46

score -1 · Answer 2 · answered May 25 '16 at 11:23

I had this problem too, and I found a way to solve this problem.

first, you should define only public int BrandId { get; set; } and run your project with that. then, in your database give a valid value (a primary key for your foreign key) to BrandId. Now, you can define public virtual Brand Brand { get; set; } , run your project again and you won't get no more errors.

Maybe it's not be the best solution for this problem, but if you've had some primary key for your foreign key , you can use this way.

I don't know what you are talking about, please be more specific in your answers. " then, in your database give a valid value (a primary key for your foreign key) to BrandId" huh? — Katherine, Jun 11 '18 at 15:23

Adding foreign key with new table with automatic migrations entity framework mvc c#

Cars table

Cars table

Brand table

2 Answers2