How can i replace default IMigrationsSqlGenerator service

[guillerglez88]

What i'm trying to enable is running code in the middle of a migration in order to apply some processing in c# code, it is specially useful wen you need to consume external services or you need to migrate serialized data, E.g:

            migrationBuilder.CodeMigration((db, builder) =>
            {
                var result = db.Query<BriefcaseAliases>(@"
SELECT B.[Id] AS BriefcaseId, 
       B.[CompanyId], 
       B.[AliasesRaw] AS AliasByTemplateIdJson 
FROM [Template].[Briefcases] B;")
                    .Select(e => new
                    {
                        e.CompanyId,
                        e.BriefcaseId,
                        AliasByTemplateId = Raw
                        .ToObject<Dictionary<string, int>>(e.AliasByTemplateIdJson)
                        .GroupBy(k => k.Value)
                        .Select(g => new { TemplateId = g.Key, Alias = string.Join(",", g.Select(i => i.Key)) })
                    })
                    .SelectMany(e => e.AliasByTemplateId.Select(a => new
                    {
                        CompanyId = e.CompanyId,
                        BriefcaseId = e.BriefcaseId,
                        TemplateAlias = a.Alias,
                        TemplateId = a.TemplateId
                    }));

                foreach (var item in result)
                {
                    builder.AppendLines($@"
UPDATE [Template].[Templates]
   SET [Identifier_Alias] = '{item.TemplateAlias}',
       [Identifier_CompanyId] = {item.CompanyId}
WHERE BriefcaseId = {item.BriefcaseId}
AND Id = {item.TemplateId};");

                    builder.EndCommand();
                }
            });

The issue

In older versions of EF (7), i used to register my custom SQL migration generator before calling services.AddEntityFramework(), as shown below:

            services.AddScoped<IMigrationsSqlGenerator, CustomSqlGenerator>();
            services.AddEntityFramework()

I discovered this could work because you use a .TryAdd(...) method witch attempts to register services if not registered yet. That stopped working suddenly after upgrading to EntityFrameworkCore 1.0.0.

Further technical details

EF Core version: 1.0.0 Operating system: Visual Studio version: 2015

[ajcvickers]

@guillerglez88 You need to make sure to call UseInternalServiceProvider in either AddDbContext or OnConfiguring. For example:

public class MyContext : DbContext
{
    private static readonly IServiceProvider _serviceProvider
        = new ServiceCollection()
            .AddEntityFrameworkSqlServer()
            .AddScoped<IMigrationsSqlGenerator, CustomSqlGenerator>()
            .BuildServiceProvider();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseInternalServiceProvider(_serviceProvider)
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=ListenerTest;Trusted_Connection=True;");
}

or

            var appServiceProivder = new ServiceCollection()
                .AddEntityFrameworkInMemoryDatabase()
                .AddScoped<IMigrationsSqlGenerator, CustomSqlGenerator>()
                .AddDbContext<MyContext>(
                    (p, b) => b.UseInMemoryDatabase().UseInternalServiceProvider(p))
                .BuildServiceProvider();

The exact pattern to use depends on how you are configuring your context and whether or not you want EF to share the same service provider as the rest of your app, but hopefully you get the idea.

[guillerglez88]

Is there any pitfall if I choose to use same service provider as the rest of my app? I noticed many times a new scope is created in order to provide a clean scope to EF DbContext.

[guillerglez88]

I have chosen option 2, because I'm configuring services in app Stratup.cs. It worked right as you said. Thanks!

[ajcvickers]

@guillerglez88 Using the same service provider as your app should be fine. It means that your app is more tightly coupled to EF and it is possible that something you do with services for your app could impact EF, although this is unlikely.

[roji]

Note to newcomers: it's now trivial to replace any service with ReplaceService in your OnConfiguring:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseNpgsql(@"Host=localhost;Username=test;Password=test")
        .ReplaceService<IMigrationsSqlGenerator, CustomMigrationsSqlGenerator>();