Use-case: Dump database schema for DbContext

[johncrim]

For EF 6, I created a helper function using automatic migrations and the SQL Generator to dump the entire SQL schema for a DbContext model to a schema.sql file. The schema.sql file was checked into source control. This supports the following objectives:

1. Transfer EF schema changes to a corresponding SSDT (Sql Server Data Tools) project. We migrate and deploy using SSDT, not EF migrations, for a number of reasons, explained below. The schema.sql file can either be imported into the SSDT project, or we can use EF schema diffs to manually make corresponding schema changes in the SSDT project.
2. Monitor the result of model changes on the EF-expected database schema
3. (By running schema generation in a unit test) Automated validation that there are no errors when generating the EF schema

The DDL script generator requires(?) automatic migrations, which I understand will not be supported in EF Core (#6214).

I'm creating this issue for 2 reasons: First, to explain our use-cases to the EF Core team for general awareness; Second, to ask whether there is a straightforward way to dump the whole DbContext schema as a SQL script in EF Core?

In the absence of direct support for this, I think I can work out an implementation starting from here.

EF6 implementation

My EF 6 class looks like this (simplified to exclude our custom SqlGenerator):

/// <summary>
/// Helper methods for generating DDL scripts for entity framework <see cref="DbContext"/>s.
/// </summary>
public static class DdlScriptHelper
{

	public static string GenerateDdlScriptFor<TDbContext>(DbMigrationsConfiguration<TDbContext> migrationsConfiguration = null, string sqlProviderName = "System.Data.SqlClient")
		where TDbContext : DbContext
	{
		if (migrationsConfiguration == null)
		{
			migrationsConfiguration = new DbMigrationsConfiguration<TDbContext>()
										{
											AutomaticMigrationsEnabled = true
										};
		}

		// Use a custom HistoryContext table, so it isn't affected by the presence of a dbo.__MigrationHistory table.
		migrationsConfiguration.SetHistoryContextFactory(sqlProviderName, CreateHistoryContext);

		DbMigrator dbMigrator = new DbMigrator(migrationsConfiguration);
		var loggingMigrator = new MigratorLoggingDecorator(dbMigrator, new ConsoleMigrationsLogger());
		MigratorScriptingDecorator scriptMigrator = new MigratorScriptingDecorator(loggingMigrator);
		return scriptMigrator.ScriptUpdate(DbMigrator.InitialDatabase, null);
	}

	private static HistoryContext CreateHistoryContext(DbConnection dbConnection, string s)
	{
		// Return a HistoryContext that purposefully does not match the standard dbo.__MigrationHistory table.
		return new HistoryContext(dbConnection, "notdbo");
	}

}

Why SSDT

The list of reasons why we use SSDT for managing our database projects is long; here are a top few reasons:

1. Give us the ability to manage the contents of a database using SQL source code files and source control. A DbContext is a portion of a database, it is not the whole database in most real-world scenarios. In many cases multiple DbContexts are combined in a single database.
2. Gives us a project to hold sproc, index, trigger etc SQL source code.
3. Separates migration script generation from deployment. Our flow is EF -> SSDT -> dbup -> database for production updates. We usually use EF -> SSDT -> database during development for integration databases. The migration scripts generated by SSDT are reviewed by DBAs and tested against production database backups.

I probably should write a blog post describing the setup we came up with. It gives us push-button production deploys and the safety of code-reviewing and tweaking migration scripts that may cause data loss.

Further technical details

EF Core version: 2.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
IDE: VS 2017

[bricelam]

Dupe of #2943?

[johncrim]

Yes! Thank you - I tried the snippet in #2943 and it meets my needs. So, the primary ask here is addressed, though you can also consider this a vote to make that a standard extension method.

I hope that the additional info on EF -> SSDT is useful for design decisions around migrations.

[bricelam]

Yes, we should definitely consider it as part of #4321.

[ErikEJ]

@johncrim FYI: I am adding this to my "EF Core Power Tools"