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;
}

Answer 1

"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.

Answer 2

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.