9. EfSchemaCompare

9. EfSchemaCompare - How to ensure EF Core's view of the database matches the actual database

This feature allows you to compare what EF Core thinks the database schema looks like against what the actual database scheme is. This can be useful in a couple of places:

If you are changing the schema of your database outside of EF Core' migrations, say by using SQL change scripts, then you can use this feature to check that the changed database is in step with EF Core's model of the database.
This feature can quickly tell you if your EF Core code has changed in any way that would mean it won't work with a database. This can be useful to run before deploying a new software version to a production database to check it will still work.

I personally use the EFSchemaCompare feature to allow me to create/migrate the database via SQL change scripts, using DbUp. I find this a good way to handle a database, especially in production environments.

NOTE: I describe example 1 in chapter 11, section 11.4.3 in my book Entity Framework Core in Action.

How to use the feature

Here is a simple example of using this feature

[Fact]
public void CompareViaContext()
{
    //SETUP
    using (var context = new BookContext(_options))
    {
        var comparer = new CompareEfSql();

        //ATTEMPT
        //This will compare EF Core model of the database with the database that the context's connection points to
        var hasErrors = comparer.CompareEfWithDb(context); 

        //VERIFY
        //The CompareEfWithDb method returns true if there were errors. 
        //The comparer.GetAllErrors property returns a string, with each error on a separate line
        hasErrors.ShouldBeFalse(comparer.GetAllErrors);
    }
}

Different parameters to the `CompareEfWithDb` method

The CompareEfWithDb method can take multiple DbContexts, known as bounded contexts (see chapter 10, section 10.6 in my book). You can add as many contexts and they are compared to one database
You can also provide a string that points to the database as the first parameter. It can have two forms
- It will use the string as a connection string name in the test's appsetting.json file
- If no connection string is found in the appsetting.json file it assumes it is a connection string

See below for an example of both of of these options

[Fact]
public void CompareBookThenOrderAgainstBookOrderDatabaseViaAppSettings()
{
    //SETUP
    //... I have left out how the options are created
    //This is the name of a connection string in the appsetting.json file in your test project
    const string connectionStringName = "BookOrderConnection";
    using (var context1 = new BookContext(options1))
    using (var context2 = new OrderContext(options2))
    {
        var comparer = new CompareEfSql();

        //ATTEMPT
        //Its starts with the connection string/name  and then you can have as many contexts as you like
        var hasErrors = comparer.CompareEfWithDb(connectionStringName, context1, context2);

        //VERIFY
        hasErrors.ShouldBeFalse(comparer.GetAllErrors);
    }
}

Limitations

There are a few limitations on what the EfSchemaCompare feature can check.

Checks that the system can't do

Because I use EF Core's scaffolder there are things I can't check.

I cannot check if an index is applied to a foreign key. EF Core adds unique indexes to foreign keys.

Checks that my software doesn't do, or gets wrong

Currently I can't find a way to stop a foreign key error from occurring with split tables. That is why I created the IgnoreTheseErrors method (see The IgnoreTheseErrors method, which allows you to suppress the error once you have seen it).
I have not included code to find/check alternate keys. I have added an issue (#1) to track this.

Further information

See 9a. EfSchemaCompare config for the configuration options available. These allow you to

Exclude specific tables in your database that you do not want compared with EF Core.
Turn off certain errors through building an 'ignore' matching CompareLog.

9. EfSchemaCompare