EF Migrations: Truncate table

user_6072156 0
Viewed 3313

I am working on an existing project that uses Entity-Framework with code-first. I need to run some SQL before the migrations run, but I get an error regarding foreign-key constraint, so I'll need to delete existing data from tables. Can I do that without deleting the tables using DbMigration.DropTable() ?

entity-framework-migrations entity-framework c#
5 Answers

I think I've found it:

Sql("Truncate table dbo.MyTable");

Thank you for your help.

user_60721560

You can't truncate tables referenced with a foreign key constraint.

Your only option is to truncate data manually using DELETE FROM starting from the table that's not referenced by any other table. The EF equivalent would be something like

db.TableToTruncate.RemoveRange(db.TableToTruncate);
user_16780530

I had a similar problem but I solved it by manually adding sql to the MigrationBuilder. Just in case this saves someone else a few minutes of searching...

migrationBuilder.Operations.Add(new SqlOperation
{
    Sql = "delete from WhateverTable"
});
user_61957570

In some case in production environment you don't have population scripts, and you need the content of your table. In that case you can use DbMigration.DropForeignKey and DbMigration.AddForeignKey functions or create a MyTableCopy table, run your SQL and insert the content of MyTable to your new table. After that in a new migration you can delete your original table and rename your new table.

user_78148180

I also had the similar problem in .net core EF. so i added below code.

await dbContext.Database.ExecuteSqlRawAsync(
            "Truncate only public.\"TableName\" RESTART IDENTITY CASCADE");
user_35104420
Related