Error: Invalid object name 'dbo.__MigrationHistory'. with custom schema name

ef-code-first ef-migrations entity-framework entity-framework-6

Question

I get an exception when trying to run "Update-Database" from Package Manager. This happens with and without a scaffolded migration.

I have the following:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

    modelBuilder.HasDefaultSchema("final");
    ...
    ...
}

I had just created this DB and did a couple of updates, and things were going fine. EF put the __MigrationsHistory table in the "final" schema as it should have (final.__MigrationsHistory). But at some point, I did an "update-database" and got the error below.

I think I can see what's wrong in EF's autogenerated SQL script. I think the last line (the line that caused the exception) should check for the existence of the dbo__MigrationsHistory before it tries to drop it. Or just check for the existence of dbo__MigrationsHistory in the first place before it tries to move its contents to the custom schema's Migration History table.

For some reason EF thought I had a dbo.__MigrationsHistory table when I never did.

So, I think it's a bug in EF. I'm using EF 6.1.3.

Maybe someone knows about this.

Here is the end of the autogenerated SQL migration script...

...
IF object_id('final.__MigrationHistory') IS NULL BEGIN
        CREATE TABLE [final].[__MigrationHistory] (
            [MigrationId] [nvarchar](150) NOT NULL,
            [ContextKey] [nvarchar](300) NOT NULL,
            [Model] [varbinary](max) NOT NULL,
            [ProductVersion] [nvarchar](32) NOT NULL,
            CONSTRAINT [PK_final.__MigrationHistory] PRIMARY KEY ([MigrationId], [ContextKey])
        )
    END
    INSERT INTO [final].[__MigrationHistory]
    SELECT * FROM [dbo].[__MigrationHistory]
    WHERE [ContextKey] = 'MyNamespace.Migrations.Configuration'
    DELETE [dbo].[__MigrationHistory]
    WHERE [ContextKey] = 'MyNamespace.Migrations.Configuration'
    IF NOT EXISTS(SELECT * FROM [dbo].[__MigrationHistory])
        DROP TABLE [dbo].[__MigrationHistory]
    System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'dbo.__MigrationHistory'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
       at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
       at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
       at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
       at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
       at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
       at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e()
       at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
       at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
       at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
       at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
       at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, 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(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
       at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
       at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
       at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b()
       at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
       at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
       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.Run()
       at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
       at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
       at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
       at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
       at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
       at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
    ClientConnectionId:ff9329dc-c707-43bd-b2da-78f4e41ba3d6
    Error Number:208,State:1,Class:16
    Invalid object name 'dbo.__MigrationHistory'.
1
1
11/23/2015 1:50:19 PM

Accepted Answer

Well, there is a workaround.

If you add a file to your project with the following code, things will work.

using System.Data.Entity;
using System.Data.Entity.Migrations.History;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyNamespace.Migrations
{
    public class CustomHistoryContext : HistoryContext
    {
        public CustomHistoryContext(DbConnection dbConnection, string defaultSchema)
            : base(dbConnection, defaultSchema)
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<HistoryRow>().ToTable(tableName: "__MigrationHistory", schemaName: "dbo");
            //modelBuilder.Entity<HistoryRow>().Property(p => p.MigrationId).HasColumnName("Migration_ID");
        }
    }

    public class CustomHistoryConfiguration : DbConfiguration
    {
        public CustomHistoryConfiguration()
        {
            this.SetHistoryContext("System.Data.SqlClient",
                (connection, defaultSchema) => new CustomHistoryContext(connection, defaultSchema));
        }
    }
}

I got it from here: http://blog.oneunicorn.com/2012/02/27/code-first-migrations-making-__migrationhistory-not-a-system-table/

The code above causes EF to put your MigrationsHistory table/entries in the dbo schema - even if you are using another schema as your default.

1
11/23/2015 2:45:31 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow