EF code first migration error "Object has been disconnected or does not exist at the server"

Question

I am using Entity Framework 6.1.1 on SQL Server 2008 and I have a long running code first migration (about 20 minutes). It gets to the end and then gives the following error.

System.Runtime.Remoting.RemotingException: Object '/f10901d8_94fe_4db4_bb9d_51cd19292b01/bq6vk4vkuz5tkri2x8nwhsln_106.rem' has been disconnected or does not exist at the server.
   at System.Data.Entity.Migrations.Design.ToolingFacade.ToolLogger.Verbose(String sql)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbTransaction transaction, 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, XDocument 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)

The point of the migration is to update a field in the database that stores the MIME type of some binary data. It loops through every row, reads the binary data, attempts to determine what kind of content it is, then writes the appropriate MIME type value into the that row.

The script below uses ADO.NET to generate a list of update statements to run. I use ADO.NET because I must use .NET's imaging libraries (System.Drawing.Imaging.ImageFormat) to determine the type of binary content in each row (it'll be a jpeg, png, or pdf).

public override void Up()
{
    List<string> updateStatements = new List<string>();

    using(SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
    {
        SqlCommand cmd = new SqlCommand("SELECT Table1ID, Image FROM Table1"), conn);
        conn.Open();

        //read each record and update the content type value based on the type of data stored
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                long idValue = Convert.ToInt64(reader["Table1ID"]);
                byte[] data = (byte[])reader["Image"];
                string contentType = GetMimeType(data);
                updateStatements.Add(string.Format("UPDATE Table1 SET Content_Type = {0} WHERE Table1ID = {1}", contentType, idValue));
            }
        }
    }

    foreach (string updateStatement in updateStatements)
        Sql(updateStatement);
}

public string GetMimeType(byte[] document)
{
    if (document != null && document.Length > 0)
    {
        ImageFormat format = null;

        try
        {
            MemoryStream ms = new MemoryStream(document);
            Image img = Image.FromStream(ms);
            format = img.RawFormat;
        }
        catch (Exception)
        {
            /* PDF documents will throw exceptions since they aren't images but you can check if it's really a PDF
             * by inspecting the first four bytes with will be 0x25 0x50 0x44 0x46 ("%PDF"). */
            if (document[0] == 0x25 && document[1] == 0x50 && document[2] == 0x44 && document[3] == 0x46)
                return PDF;
            else
                return NULL;
        }

        if (format.Equals(ImageFormat.Jpeg))
        {
            return JPG;
        }
        else if (format.Equals(System.Drawing.Imaging.ImageFormat.Png))
        {
            return PNG;
        }
    }

    return NULL;
}

I've seen this five year old post and the articles that it links to do not seem to exist anymore. At least I can't find them.

Does anyone know what's going on here?

-- UPDATE --
This appears to have something to do with how long the migration takes to run. I created a migration that does absolutely nothing other than sleep for 22 minutes

public override void Up()
{
    System.Threading.Thread.Sleep(1320000);
}

and I got the same error. So it appears to be a timeout thing. I'm not 100% what object on the server they are referring to and I can't find much on this issue as it relates to code first migrations.

I tried setting the CommandTimeout property in the migrations Configuration.cs file to 5000 but it didn't help. I also attempted to set the SQL Server's Remove query timeout setting to 0 to prevent any timeouts but it didn't help either.

Answer 1

Poached from [GitHub EntityFramework 6 Issue #96][https://github.com/aspnet/EntityFramework6/issues/96#issuecomment-289782427]

The issue is that the ToolLogger lease lifetime (base class MigrationsLogger is a MarshalByRefObject) is at the default (5 minutes). The ToolingFacade creates the logger, which lives in the main program's app domain. The migrations run in a different app domain. If a migration takes longer than 5 minutes, the attempt to log any further information results in this error. A solution would be to increase the lease lifetime in the main program. So... in the main program, prior to creating the ToolingFacade, set the lease lifetime to a longer time period:

using System.Runtime.Remoting.Lifetime;
...
LifetimeServices.LeaseTime = TimeSpan.FromHours(1);

Answer 2

It's a known issue in Entity Framework 6 for scripts that take a long time to complete.

A workaround is to generate only SQL script via the Update-Database command and execute the generated SQL directly on the SQL Server. In order to generate only the SQL you have to use the -Script flag:

Update-Database -Script