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 things the database 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.

NOTE: I describe this 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);
    }
}

`CompareEfSql` configuration

There is a class called CompareEfSqlConfig which contains configuration setting for the comparers. At the moment there is only one config,

`TablesToIgnoreCommaDelimited` property

If there are tables in your database that EF Core doesn't access then you need to tell CompareEfSql about them, otherwise it will output a message saying there are extra tables you are not accessing from EF Core. You do this by providing a comma delimited list of table names, with an optional schema name if needed. Here are two examples of a table name

MyTable - this has no schema, so the default schema of the database will be used
Dbo.MyTable - this defines the schema to be Dbo, - a full stop separates the schema name from the table name.

NOTE: The comparison is case insensitive.
Here is an example of configuring the comparer to not look at the tables Orders and LineItem

var config = new CompareEfSqlConfig
{
    TablesToIgnoreCommaDelimited = "Orders,LineItem"
};
var comparer = new CompareEfSql(config);

9. EfSchemaCompare