EntityFramework code-first: how to use the “-script” option to avoid the timeout of the “update-database” command

By using EntityFramework in the code-first mode to interact with the SQL database and to map on it the entities of our data model, we will necessarily create a migration every time we want to make a modification and to apply it using an update command.
In this case, the need was to change the DbContext by adding a composite index on 3 columns of a table that already contained a large amount of records (several millions).

In overriding the OnModelCreating method, the following statements were added:

modelBuilder.Entity<MyEntity>()
	.Property(f => f.ColumnOne)
	.HasColumnAnnotation(
		IndexAnnotation.AnnotationName,
		new IndexAnnotation(new[]
			{
				new IndexAttribute("ThreeColumnIndex") { Order = 1 }
			}
		)
	);

modelBuilder.Entity<MyEntity>()
	.Property(f => f.ColumnTwo)
	.HasColumnAnnotation(
		IndexAnnotation.AnnotationName,
		new IndexAnnotation(new[]
			{
				new IndexAttribute("ThreeColumnIndex") { Order = 2 }
			}
		)
	);
modelBuilder.Entity<MyEntity>()
	.Property(f => f.ColumnThree)
	.HasColumnAnnotation(
		IndexAnnotation.AnnotationName,
		new IndexAnnotation(new[]
			{
				new IndexAttribute("ThreeColumnIndex") { Order = 3 }
			}
		)
	);

After making this modification I ran the “add-migration CreateIndexMyThreeColumns” command that created in the “Migrations” folder the related file which contains the instructions for applying and removing the migration itself:

namespace MyDataDataModel.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class CreateIndexMyThreeColumns : DbMigration
    {
        public override void Up()
        {
            CreateIndex("dbo.MyEntity", new[] { "ColumnOne", "ColumnTwo", "ColumnThree" }, name: "ThreeColumnIndex");
        }
        
        public override void Down()
        {
            DropIndex("dbo.MyEntity", "ThreeColumnIndex");
        }
    }
}

At this point, I ran the update-database command to apply the migration to the context, but as we can see from the following screenshots, I got a timeout error:
Update Database command timeoutEntity Framework Update Database timeoutThe error is described in quite explicit terms:

System.Data.SqlCliente.SqlException: Execution Timeout Expired. 
The timeout period elapsed prior to completation of the operation or the server is not reposnding.

The creation of the index on the table already populated with several records is in fact a long operation, which couldn’t complete before the expiration of the run commands timeout, which is set to 30 seconds as default.

At this point the possible solutions are 2:

  • To increase the CommandTimeout of the DbContext
  • To use the “-script” option of the “update-database” command

In this article we see how to use the second method.

By adding the -script option to the update-database command, what happens is that EntityFramework, instead of trying to apply the migration, generates a SQL script that we can then launch from SQL Server Management Studio.

Let’s try to run it:

update-database -script

Entity Framework Update Database -script option

As we can see from the following image, after executing the command, Visual Studio opens a new tab containing the SQL statement to run:
Update Database -script option SQL statement The statement, in this case, is the following:

CREATE INDEX [ThreeColumnIndex] ON [dbo].[MyEntity]([ColumnOne], [ColumnTwo], [ColumnThree])
INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'201703291516276_CreateIndexMyThreeColumns', N'MyDataDataModel.Migrations.Configuration',  0x1F8B0....., N'6.1.3-40302')

By copying and pasting the script to SQL Server Management Studio and then executing it, the index was created without any problems, avoiding any timeout issues.

This entry was posted in $1$s. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *