Generate migration-free SQL scripts for test cases

[ewlloyd]

The Problem

Our integration test suite creates and tears down the test database many times during each test run. Given the 170 migrations we've accumulated so far (and counting), this leads to some pretty inefficient DDL.

The Solution

What I propose is a command-line flag (say, --squash or --direct) that instructs EF to generate a DDL script that lays out the final schema directly, with no intermediate migration states.

This script, while it would be much more efficient for building transient test databases, would be dangerous to run against an existing database. To that end, some guard logic could be tacked on at the top of the script to defend against this: See the __EFMigrationsHistory table? Abort the run before you destroy something.

[roji]

That sounds like #2174 (squashing migrations). Or if you're looking for a feature which wouldn't actually get rid of the migrations on disk, but only generate a squashed SQL script, it would most probably depend on it (i.e. we could implement this by temporarily squashing everything in memory etc.).

But if you get to the point where it's too slow to run all the migrations in the tests, wouldn't you also just want to squash the migrations on disk as well?

[ewlloyd]

We have our specific reasons for maintaining the chain of migrations in our solution that I won't go into here (mainly because I'm not the one driving that policy), but indeed, as you said yourself, we just want to have a squashed script for maximum efficiency during testing.

Looking at some of the comments on #2174, I see I'm not the first to see the benefits of this.

[nlriverty]

I have also run into this issue with the same use case. To get around it I generated the script to create the schema in one go:

var databaseCreator = dbContext.Database.GetService<IRelationalDatabaseCreator>();
if (!await databaseCreator.ExistsAsync()) await databaseCreator.CreateAsync();
await using var transaction = await dbContext.Database.BeginTransactionAsync();
await dbContext.Database.ExecuteSqlRawAsync(dbContext.Database.GenerateCreateScript().Replace($"{Environment.NewLine}GO{Environment.NewLine}", string.Empty, StringComparison.Ordinal));
await transaction.CommitAsync();

Unfortunately now that I've tried to add a new view to the database, I see that it isn't included in the script.

We have in the past collapsed the migrations, but it seemed like a hack to change production code just to get the tests working.