Eventually after investigation and a bit of playing around, I achieved this by doing the following:
Created an Entity Framework Initializer based on CreateDatabaseIfNotExists (http://www.entityframeworktutorial.net/code-first/database-initialization-strategy-in-code-first.aspx):
public class DbTestInitializer : CreateDatabaseIfNotExists<DBContext>
{
public override void InitializeDatabase(DBContext context)
{
if(context.Database.Exists())
{
// We have a database already, so we can clean entities and run seed.
CleanseTables(context);
Seed(context);
}
base.InitializeDatabase(context);
}
private void CleanseTables(DBContext context)
{
// Run the database teardown script
context.Database.ExecuteSqlCommand(Properties.Resources.DatabaseTeardown);
}
protected override void Seed(DBContext context)
{
this.ApplySeed(context);
}
}
Inside the initializer InitializeDatabase method, I check to see if the database exists - if so then I execute a cleanse script that does the following:
- Collected all foreign key references and constructed a drop constraint and create constraint script for each one. I then executed the drop constraints, truncated every table in the database (excluding the MigrationHistory table specific to EF Migrations) and reenabled the constraints using the create constraint scripts. This is done by executing the script via context.Database.ExecuteSQLCommand([script])
Here's how that script looks.
DECLARE @ConstraintsTable TABLE
(
ID INT IDENTITY(1,1),
DropConstraintScript VARCHAR(MAX),
EnableConstraintScript VARCHAR(MAX)
)
INSERT INTO @ConstraintsTable
SELECT
'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
+ '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT ['
+ ForeignKeys.ForeignKeyName + ']; ',
'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
+ '].[' + ForeignKeys.ForeignTableName
+ '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName
+ '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn
+ ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id)
+ '].[' + sys.objects.[name] + ']([' + sys.columns.[name]
+ ']);'
FROM sys.objects
INNER JOIN sys.columns
ON ( sys.columns.[object_id] = sys.objects.[object_id] )
INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
,sys.objects.[name] AS ForeignTableName
,sys.columns.[name] AS ForeignTableColumn
,sys.foreign_keys.referenced_object_id AS referenced_object_id
,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
FROM sys.foreign_keys
INNER JOIN sys.foreign_key_columns
ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
INNER JOIN sys.objects
ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
INNER JOIN sys.columns
ON ( sys.columns.[object_id] = sys.objects.[object_id] )
AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
) ForeignKeys
ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
WHERE ( sys.objects.[type] = 'U' )
AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) )
declare @count int, @ndx int
declare @script nvarchar(max)
select @count = count(ID) from @ConstraintsTable
set @ndx = 1
while(@ndx <= @count)
begin
select @script = DropConstraintScript from @ConstraintsTable where ID = @ndx
EXEC sp_executesql @script;
set @ndx = @ndx + 1
end
EXEC sp_msforeachtable @command1 = 'TRUNCATE TABLE ?', @whereand = 'AND Object_Id NOT IN (SELECT Object_Id FROM sys.objects WHERE name like ''__Migration%'')';
set @ndx = 1
while(@ndx <= @count)
begin
select @script = EnableConstraintScript from @ConstraintsTable where ID = @ndx
EXEC sp_executesql @script;
set @ndx = @ndx + 1
end
The script
EXEC sp_msforeachtable @command1 = 'TRUNCATE TABLE ?', @whereand = 'AND Object_Id NOT IN (SELECT Object_Id FROM sys.objects WHERE name like ''__Migration%'')';
Truncates all tables in the database except for the migration table. This is after the drop constraint scripts have been executed. After the tables are truncated, the next part of the script adds all the constraints back in. By using table variables instead of cursors, we should get better performance when the script executes. It may be possible to improve on the script in places to get better performance. This is the only area where we rely on database script execution. By utilizing the benefits of the EF Initializer we ensure the following:
- The database can be created from scratch if it is not already created.
- The database is cleansed (all tables truncated) using the script detailed above in the CleanseTables method.
- The database is then seeded. In my instance I used the same seeding for the test db initializer that I have for my default migration initializer. This ensures that the database is 'reset' to the state that the database was before any data was added in.
