Re-create table with Entity Framework 5 and nuget

问题:

I am using Code First to create a table.

I created the class, the mapping file and issued the add-migration command in nuget and then the update-database command

I then changed the class and like an idiot deleted the table.

I deleted the migration class file

I issued a add-migration command

When I issue the update-database command I get the following error:

System.Data.SqlClient.SqlException (0x80131904): Cannot find the object "dbo.CorrectiveActionPlan" because it does not exist or you do not have permissions. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable1 operations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore() at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run() ClientConnectionId:a6e92a35-cc9e-4867-97a5-0a274081d853 Cannot find the object "dbo.CorrectiveActionPlan" because it does not exist or you do not have permissions.

How do I force EF to recreate the table?

回答1:

I found my answer.

I deleted the row in [dbo].[__MigrationHistory] that corresponded to my Migration

I then deleted the new migration file

I re-ran add-migration

and then re-ran update-database -verbose

回答2:

There are a few options I keep in my arsenal for code-first migrations and they depend on why you need to delete tables or delete the records. Here is my methods:

• If you modified the models and the mappings are causing an error that prevents you from not being able to update the tables you could Delete the entire database using SQL Management Server Studio & Delete the Migration folder You may want to save a script to re-populate your test data using an sql script or save your Configuration.cs file and when you execute the update database command the data will be re-populated.

  Here is an example of script for a stored procedure to just drop table data:

  USE [DatabaseName]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[sp_DeleteAllYardPenaltyRecords]
  AS
  EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
  EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
  EXEC sp_MSForEachTable 'DELETE FROM ?'
  EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
  EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
  EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
  

• If you just want to drop the data you can use the package manager console command: PM> Update-Database -TargetMigration $InitialDatabase & delete the migration file created ie: '201502210525393_example_with_error.cs' and re-run 'Add-Migration new_example.cs' again. This puts the database back to its initial snapshot

• Or you could use your method: delete the row in [dbo].[__MigrationHistory] & migration file ie: '201502210525393_example_with_error.cs' then re-run add-migration and update-database

回答3:

You could just drop the database then from the package manager console run the 'update-database' command, all would be recreated including whatever updates you made.