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, with is where the figure below comes from.

EfSchemaCompare diagram

FIGURE showing how EfSchemaCompare works.

How to use the EfSchemaCompare 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);
    }
}

Using with database provider not installed in `EfCore.TestSupport` library

The EfCore.TestSupport library only contains the SqlServer and Sqlite database providers. I did that because the other databases have multiple database providers. But if you want to run the compare with a specific database provider you can do that using the version

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

        //ATTEMPT
        //This will use the database provider design time type you gave to get the database information
        var hasErrors = comparer.CompareEfWithDb<SqlServerDesignTimeServices>(context);

        //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 I cannot check if an index is applied to a foreign key (the scaffolder assumes EF Core will do that by default). EF Core adds indexes to foreign keys.

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

I have not included code to find/check alternate keys. I have added an issue (#1) to track this.
I have not included code to find/check sequences.

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