EF Core Migrations - creating Indexes Online depending on SQL Server edition

Ted Anderson

Ted Anderson

— 1 min read

I recently hit the classic case of trying to add Indexes to a large table. Whilst Entity Framework Core supports creating Indexes online during migrations, not all versions of SQL Server support this.

In the case that your migration contains the code:

 migrationBuilder.CreateIndex(
                 name: "IX_TableName_ColumnName"
                 table: "TableName",
                 column: "ColumnName").Annotation("SqlServer:Online", true);

This will fail hard on SQL Server Express, which you are likely using for development locally, with the error message “Online index operations can only be performed in Enterprise edition of SQL Server.”. Online index operations are available in Enterprise or luckily in my case, Azure SQL.

Whilst there is not a “feature flag” to detect the support of Online index creation, you can execute the following query to detect the edition of SQL Server your app is running on.

SELECT SERVERPROPERTY(‘EngineEdition’)

Which returns 3 for Enterprise edition or 5 for SQL Azure (full list here).

EF Core has removed the ability to easily execute scalar queries so you’ll need a small extension method:

public static class SqlQueryExtensions
    {
        public static T ExecuteScalar<T>(this DbContext context, string rawSql,
         params object[] parameters)
        {
            var conn = context.Database.GetDbConnection();
            using (var command = conn.CreateCommand())
            {
                command.CommandText = rawSql;
                if (parameters != null)
                    foreach (var p in parameters)
                        command.Parameters.Add(p);
                conn.Open();
                return (T) command.ExecuteScalar();
            }
        }

And then you can set a public static property on your migration before calling DbContext.Migrate():

var dbEngineVersion = dbContext.ExecuteScalar<int>("SELECT SERVERPROPERTY('EngineEdition')");
MyMigrationName.UseOnlineIndexCreation = dbEngineVersion == 3 || dbEngineVersion == 5;
dbContext.Database.Migrate();

public partial class MyMigrationName : Migration
{
    public static bool UseOnlineIndexCreation { get; set; }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        if (UseOnlineIndexCreation)
        {
            migrationBuilder.CreateIndex(
             name: "IX_TableName_ColumnName",
             table: "TableName",
             column: "ColumnName").Annotation("SqlServer:Online", true);
        }
        else
        {
            migrationBuilder.CreateIndex(
             name: "IX_TableName_ColumnName",
             table: "TableName",
             column: "ColumnName");
            }

        }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_TableName_ColumnName",
            table: "AuditTrail");
    }
}

Now your Index will be created Online on editions of SQL Server that support it.

Read more