EfSchemaCompare config

CompareEfSql configuration

You can provide configuration data to the CompareEfSql class via a class called CompareEfSqlConfig. This class contains configuration setting for the comparer. Here are the possible configuration options.

1. TablesToIgnoreCommaDelimited property

By default (i.e. when TablesToIgnoreCommaDelimited is null) then CompareEfSql will only look at the tables in the database that your EF Core entity classes are mapped to. This provides an simple starting point. The other options are:

• Set TablesToIgnoreCommaDelimited to "" (i.e. empty string)
  This will check all the tables in the database
• Set TablesToIgnoreCommaDelimited to a list of tables to ignore
  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);

2. CaseComparer case insensitive database comparing

By default CompareEfWithDb looks for tables, columns etc. in the database that exactly match the tables, columns etc. set by EF Core, e.g. "MyTable" and "mytable" don't match. This gives you the most detailed matching approach.

However databases are often be set to be case insensitive, e.g. "MyTable" is the same as "mytable", or "mYtAble". You can tell CompareEfWithDb to use case insensitive matching by setting the CaseComparer property to something like StringComparer.CurrentCultureIgnoreCase. See the comments on the CaseComparer property in the CompareEfSqlConfig class for what it effects.

3. The IgnoreTheseErrors method

If you run the CompareEfWithDb method you might get errors that you don't care about. In that case you can simply copy the error messages, and put them into a string. The IgnoreTheseErrors method will decode the errors back into a CompareLog and register them in the LogsToIgnore property. The example below shows this in action.

[Fact]
public void CompareSpecializedDbContextSupressErrors()
{
    //SETUP
    var errorString =
    @"NOT IN DATABASE: BookDetail->ForeignKey 'FK_Books_Books_BookSummaryId', constraint name. Expected = FK_Books_Books_BookSummaryId
    DIFFERENT: BookSummary->Property 'BookSummaryId', value generated. Expected = OnAdd, found = Never";
    using (var context = new SpecializedDbContext(_options))
    {
        var config = new CompareEfSqlConfig();
        config.IgnoreTheseErrors(errorString);
        var comparer = new CompareEfSql(config);

        //ATTEMPT
        var hasErrors = comparer.CompareEfWithDb(context);

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

NOTE: In the first error test came from a problem I had in table splitting, which I have now fixed. The second error is due to a bug in EF Core version 2.0.0 on table splitting and primary keys (see issue #9652).

4. The AddIgnoreCompareLog method

You can get the Comparer to ignore certain errors. You do this by adding 'ignore matching' CompareLogs to an list called LogsToIgnore. The matching is quite flexible. It always exactly matches the Type (like Entity, Index and so on). Other parts are optional matching: for enums there is a MatchAnything entry, for strings null will match anything.

For instance, say I wasn't interested in any tables that were in the database that EF Core didn't have in its configuration. Normally these would show as an error, but I can suppress this error using the following config setup

using (var context = new BookContext(GetBookContextOptions()))
{
    var config = new CompareEfSqlConfig();
    // This adds a CompareLog matcher which will ignore any "EXTRA IN DATABASE" errors for Tables 
    config.AddIgnoreCompareLog(new CompareLog(CompareType.Table, CompareState.ExtraInDatabase, null));
    var comparer = new CompareEfSql(config);

    //ATTEMPT
    var hasErrors = comparer.CompareEfWithDb(_connectionString, context);

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

There are lots of options, and you can be very specific about the error you want to ignore by adding more details in the CompareLog matcher. But, as you will see next, there is another way to do that.