Create table and insert data into it during EF code first migration

Question

I'm using Entity Framework Code First with Code First migrations.

During a migration, I need to create a new table, and then insert some data into it.

So I create the table with :

CreateTable("MySchema.MyNewTable", c => new { MYCOLUMNID = c.Int(nullable: false, identity: true), MYCOLUMNNAME = c.String(), }) .PrimaryKey(t => t.MYCOLUMNID);

Then I try to insert data with :

using (var context = new MyContext()) { context.MyNewTableDbSet.AddOrUpdate(new[] { new MyNewTable { MYCOLUMNNAME = "Test" } }); context.SaveChanges(); }

But I get an error :

Invalid object name 'mySchema.MyNewTable'.

Is it possible to do what I need ? Create a table and inserto data into it in the same migration ?

I already have other migrations where I create tables or insert data into a table, but never in the same migration...

learn.microsoft.com/en-us/ef/core/modeling/data-seeding

Gert Arnold
– Gert Arnold

2022-09-25 19:04:41 +00:00
Commented Sep 25, 2022 at 19:04 — Gert Arnold
– Gert Arnold, Commented Sep 25, 2022 at 19:04

binki · Accepted Answer · 2019-01-04 20:57:30Z

24

You can try this approach: after creating table, create another empty migration in your Package Manager Console using:

Add-Migration "MigrationName"

Then open the .cs file of that migration and, in Up() method, insert this code:

Sql("INSERT INTO MyNewTable(NyColumnName) Values('Test')");

After that, save and go back to Package Manager Console and update the database using:

Update-Database

edited Jan 4, 2019 at 20:57

binki

8,4246 gold badges76 silver badges121 bronze badges

answered Jul 20, 2017 at 23:29

Vishnu Mallipudi

3903 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

egmfrs Over a year ago

If I put this inside the Up method I get Sql does not exist in the current context. Cannot resolve symbol Sql. Can you provide a more complete example of how you'd use this?

binki Over a year ago

@egmfrs Are you using EntityFrameworkCore? This question concerns EntityFramework6. In EntityFrameworkCore, you would use migrationBuilder.Sql() (where migrationBuilder is the name of the first argument to your Up() method) instead of this.Sql().

egmfrs Over a year ago

@binki Thanks, you're right - that does work. I'm using EF core and have been using both the accepted answer and your suggestion with success in my project.

jdhurst · Accepted Answer · 2018-07-26 20:14:08Z

My recommendation is move that insert code to the Seed method. Migrations introduced its own Seed method on the DbMigrationsConfiguration class. This Seed method is different from the database initializer Seed method in two important ways:

It runs whenever the Update-Database PowerShell command is executed. Unless the Migrations initializer is being used the Migrations Seed method will not be executed when your application starts.
It must handle cases where the database already contains data because Migrations is evolving the database rather than dropping and recreating it.

For that last reason it is useful to use the AddOrUpdate extension method in the Seed method. AddOrUpdate can check whether or not an entity already exists in the database and then either insert a new entity if it doesn’t already exist or update the existing entity if it does exist.

So, try to run the script that you want this way:

 Update-Database –TargetMigration: ScriptName

And the Seed method will do the job of inserting data.

As Julie Lerman said on her blog:

The job of AddOrUpdate is to ensure that you don’t create duplicates when you seed data during development.

Wrong. The question is about EF core. Seeding is different there.
** Add-Migration "..." and Update-Database if first migration; Add-Migration "..." -Context MyOtherDbContext and Update-Database -Context MyOtherDbContext for all other DbContexts

Hizabr · Accepted Answer · 2023-06-21 07:51:03Z

For those who looking for EF Core solution, In the Up method, and after creating the table:

i.e:

migrationBuilder.CreateTable(name: "MyTable", .....

add the following code:

migrationBuilder.InsertData(table: "MyTable", column: "MyColumn", value: "MyValue");

or

migrationBuilder.InsertData(table: "MyTable", columns: ..., values: ...);

For more information see the docs: MigrationBuilder.InsertData Method

Francesc Castells · Accepted Answer · 2015-02-12 15:22:15Z

A way to do "random" things in migrations is to use the Sql method and pass whatever SQL statement you need to perform, for example, inserting data.

This is the best approach if you want your migrations to be able to generate a complete migration SQL script, including your data operations (the Seed method can only be executed in code and won't generate any sql script).

Uthpalavi Dias · Accepted Answer · 2022-04-12 10:27:42Z

First I run the

PM> Add-Migration MyTableInsertInto

then I got following,

**The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration MyTableInsertInto' again. ** I have used following code for c# data migrations

public partial class MyTableInsertInto : DbMigration { public override void Up() { Sql("INSERT INTO MyNewTable(MyColumnName) Values ('Test')"); } public override void Down() { Sql("DELETE MyNewTable WHERE MyColumnName= 'Test'"); } }

Then I run following

PM> update-database

Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Applying explicit migrations: [202204120936266_MyNewTableInsertInto]. Applying explicit migration: 202204120936266_MyNewTableInsertInto. Running Seed method.

After running the seed method data base is updated successfully and this works fine for me.

Collectives™ on Stack Overflow

Create table and insert data into it during EF code first migration

5 Answers 5

3 Comments

2 Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

1 Comment

Comments

Comments

Linked

Related