Entity Framework's Migrations are a powerful tool for keeping the application data model in sync with the database. They usually work great (besides the occasional conflicts when merging pull requests), but there might come a time when you need to debug either the migration scaffolding operation or applying the changes to the database - what to do then? It is easier than you might think!

Starting point

As a starting point, let's create a simple .NET 6 console application with the following NuGet packages added:

Microsoft.EntityFrameworkCore.SqlServer*
Microsoft.Extensions.Hosting
Microsoft.Extensions.DependencyInjection.Abstractions
Microsoft.Extensions.Logging.Abstractions

And Program.cs looking like this:

using EFCoreDebug;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

var host = Host.CreateDefaultBuilder(args)
               .ConfigureServices(
                   services =>
                   {
                       services.AddLogging();
                       services.AddDbContext<ApplicationDbContext>(
                           options =>
                               options.UseSqlServer("Server=127.0.0.1,1433;Database=Database;User ID=User;Password=Password;MultipleActiveResultSets=true"));
                       services.AddHostedService<Worker>();
                   }).ConfigureLogging(
                   log =>
                   {
                       log.AddFilter("Microsoft", level => level >= LogLevel.Warning);
                       log.AddConsole();
                   })
               .Build();

await host.RunAsync();

public class Worker : BackgroundService
{
    private readonly IServiceScopeFactory _serviceScopeFactory;
    
    private readonly IHost _host;
    
    private readonly ILogger<Worker> _logger;

    public Worker(IServiceScopeFactory serviceScopeFactory, IHost host, ILogger<Worker> logger)
    {
        _serviceScopeFactory = serviceScopeFactory;
        _host = host;
        _logger = logger;
    }

    protected override Task ExecuteAsync(CancellationToken stoppingToken)
    {
        return _host.StopAsync(stoppingToken);
    }
}

There is nothing fancy here. The Host bits might look strange, but the setup is there to easily inject the IServiceScopeFactory, which will be used to set up instances of the DbContext.

Remember to reference the correct DbContext for which the database update phase fails. It's also best to have the same DbContext configuration here as in the project that you're having problems with.

Debugging migration application

Let's start with the easier part - debugging the situation when errors appear while changes to the database are being applied, e.g. when running the dotnet ef database update command.

dotnet ef database update
Build started...
Build succeeded.
...
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
...
Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
...
Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'SET'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:81c26160-9086-4a70-9c5d-3e201074636d
Error Number:156,State:1,Class:15
Incorrect syntax near the keyword 'SET'.

Debugging this is very simple: just update the background service to get an instance of the DbContext class and call its DBContext.Database.Migrate() method:

protected override Task ExecuteAsync(CancellationToken stoppingToken)
{
    using var scope = _serviceScopeFactory.CreateScope();
    var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    dbContext.Database.Migrate();
    
    return _host.StopAsync(stoppingToken);
}

Now you can either set up your own breakpoints or have your debugger pause the application when any .NET exception is thrown to investigate the issues in-depth.

Debugging migration scaffolding

This is a tiny bit more complicated, but it's also much rarer that you will need to do this (unless you're developing your own providers or Entity Framework extensions). The problems here may vary greatly, but here's one example from my most recent project:

dotnet ef migrations add Initial
Build started...
Build succeeded.
System.InvalidOperationException: Sequence contains no elements
   at System.Linq.ThrowHelper.ThrowNoElementsException()
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Table.get_IsExcludedFromMigrations()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.Add(ITable target, DiffContext diffContext)+MoveNext()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffCollection[T](IEnumerable`1 sources, IEnumerable`1 targets, DiffContext diffContext, Func`4 diff, Func`3 add, Func`3 remove, Func`4[] predicates)+MoveNext()
   at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
   at System.Collections.Generic.List`1.AddRange(IEnumerable`1 collection)
   at EFCore.AuditableExtensions.Common.Migrations.MigrationsModelDiffer.GetDifferences(IRelationalModel source, IRelationalModel target) in C:\Users\maciejz\RiderProjects\EFCore.AuditableExtensions\EFCore.AuditableExtensions.Common\Migrations\MigrationsModelDiffer.cs:line 61
   at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsScaffolder.ScaffoldMigration(String migrationName, String rootNamespace, String subNamespace, String language)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Sequence contains no elements

Time to get to the root cause of the issue! First, add the Microsoft.EntityFrameworkCore.Design NuGet package. Then, edit the project's .csproj file and change the reference from:

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.6">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>

To:

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.6">
    <PrivateAssets>all</PrivateAssets>
<!--    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>-->
</PackageReference>

An explanation of why this needs to be can be found at Referencing Microsoft.EntityFrameworkCore.Design. To keep it short, you cannot reference the package's type by default so the dependency won't flow into your main project.

Once that's done, it's time to upgrade the Worker class:

protected override Task ExecuteAsync(CancellationToken stoppingToken)
{
    using var scope = _serviceScopeFactory.CreateScope();
    var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();

    var dbSpecificDesignTimeServices = new SqlServerDesignTimeServices(); // *
    var designTimeServiceCollection = new ServiceCollection().AddEntityFrameworkDesignTimeServices()
                                                             .AddDbContextDesignTimeServices(dbContext);
    dbSpecificDesignTimeServices.ConfigureDesignTimeServices(designTimeServiceCollection);
    var designTimeServiceProvider = designTimeServiceCollection.BuildServiceProvider();
    
    var scaffolder = designTimeServiceProvider.GetRequiredService<IMigrationsScaffolder>();
    var migration = scaffolder.ScaffoldMigration("Migration", "Migration.Debug");
    
    _logger.LogInformation("{DbContextModelSnapshot}", migration.SnapshotCode);
    
    return _host.StopAsync(stoppingToken);
}

And that's it - now you'll be able to see why the migration scaffolding process fails.

Custom IDesignTimeServices implementation

One small caveat - if your code has its own IDesignTimeServices implementation then you might be used to Entity Framework finding it on its own. In this case, you need to instantiate it yourself and apply the modifications to IServiceCollection manually.

Given a IDesignTimeServivces implementation:

public class CustomDesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services.AddSingleton<IMigrationsCodeGenerator, CustomCSharpMigrationsGenerator>();
        services.AddSingleton<ICSharpMigrationOperationGenerator, CustomCSharpMigrationOperationGenerator>();
    }
}

Use it in the Worker.ExecuteAsync like so:

protected override Task ExecuteAsync(CancellationToken stoppingToken)
{
    using var scope = _serviceScopeFactory.CreateScope();
    var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();

    var dbSpecificDesignTimeServices = new SqlServerDesignTimeServices();
    var customDesignTimeServices = new CustomDesignTimeServices();
    var designTimeServiceCollection = new ServiceCollection();
    
    customDesignTimeServices.ConfigureDesignTimeServices(designTimeServiceCollection);
    designTimeServiceCollection.AddEntityFrameworkDesignTimeServices()
                               .AddDbContextDesignTimeServices(dbContext);
    dbSpecificDesignTimeServices.ConfigureDesignTimeServices(designTimeServiceCollection);
    var designTimeServiceProvider = designTimeServiceCollection.BuildServiceProvider();
    
    var scaffolder = designTimeServiceProvider.GetRequiredService<IMigrationsScaffolder>();
    var migration = scaffolder.ScaffoldMigration("Migration", "Migration.Debug");
    
    _logger.LogInformation("{DbContextModelSnapshot}", migration.SnapshotCode);
    
    return _host.StopAsync(stoppingToken);
}

Now you're ready to fix those haunting problems!

All the code can be found at:

Cover photo by Agence Olloweb on Unsplash