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...
You can try this approach: after creating table, create another empty migration in your Package Manager Console using:
Then open the
.cs
file of that migration and, inUp()
method, insert this code:After that, save and go back to Package Manager Console and update the database using:
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).
For those who looking for
EF Core
solution, In theUp
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
My recommendation is move that insert code to the Seed method. Migrations introduced its own
Seed
method on the DbMigrationsConfiguration class. ThisSeed
method is different from the database initializerSeed
method in two important ways:Update-Database
PowerShell command is executed. Unless the Migrations initializer is being used the MigrationsSeed
method will not be executed when your application starts.For that last reason it is useful to use the
AddOrUpdate
extension method in theSeed
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:
And the
Seed
method will do the job of inserting data.As Julie Lerman said on her blog: