Repository files navigation

If you are changing the schema of your database's schema outside of EF Core' migrations, say by using SQL change scripts, then this library can quickly tell you if the a specific database schema and EF Core'sModel of the database are in step. Versions below 8 this library

The first number in the version number of this library defines what version of NET framework it works for. e.g.

• EfCore.SchemaCompare version 10.?.? works with to NET 10.
• EfCore.SchemaCompare version 9.?.? works with to NET 9.
• EfCore.SchemaCompare version 8.?.? works with to NET 8.
• EfCore.SchemaCompare version 7.?.? works with to NET 7 ... an so

WARNING: Version 8 any above contains aBREAKING CHANGE. You need to add the Microsoft.EntityFrameworkCore.Design NuGet to your application when using this library. This is needed as the EfCore.SchemaCompare library now supports multiple database providers does - seeWhat database providers does it support.

The EfCore.SchemaCompare library (shortened to EfSchemaCompare in the documentations) is available onNuGet as EfCore.SchemaCompare and is an open-source library under the MIT licence. SeeReleaseNotes for details of changes and information on versions from EF Core 5 onwards.

TABLE OF CONTENT

1. What database providers does it support
2. What does EfSchemaCompare check?
3. List of limitations
4. Introduction to how EfSchemaCompare works
5. How to use EfSchemaCompare
6. Different parameters to theCompareEfWithDb method
7. Understanding the error messages
8. How to suppress certain error messages
9. Other configuration options

NOTE: I use the termentity class for classes mapped to the database by EF Core.

• Version 8 now compares all EF Core database providers that can bebe scaffolded. However some database providers may show incorrect match errors, because some database providers don't follow the SqlServer style, e.g. CosmosDB

Thanks to GitHub @bgrauer-atacom and @lweberprb for suggesting that this library could support extra database providers. See theissue #26 to see the code that these two people provided to add this feature.

• Versions below 8 support:
  • SqlServer
  • Sqlite
  • Npgsql.EntityFrameworkCore.PostgreSQL

• Table/View exists: That a table or view that an entity class is mapped exists. This checks both table/view name and schema name
• Property/Column: exists, database type (including size and precision), nullability, computed column (including persistence), column default value, when updated (e.g. column is updatedOnAdd for a int primary key which is provided by the database)
• Primary key: SQL constraint name, properties
• Foreign keys: SQL constraint name, Delete behavior, properties
• Indexes: SQL constraint name, Unique/not unique, properties

• Unused tables or views
• Unused columns
• Unused Indexes

• Normal classes/properties
• Keyless classes
• Backing fields
• Shadow properties
• Value Converters
• Owned Types
• Table-per-Hierarchy
• Table splitting
• Concurrency tokens
• Json Mapping added in V8.1.0 and above - see Json Mapping checking limitation below

• The EF Core's scaffolder doesn't read in any index on the foreign key (the scaffolder assumes EF Core will do that by default). That means I can't check that there is an index on a foreign key.
• Cannot correctly check Table-per-Type or Table-per-Class classes because EF Core doesn't currently hold that data. This is tracked byEf Core #19811.
• Cannot compare database tables/columns using InvariantCultureIgnoreCase. That is a EF Core 5+ limitation.
• EfCore.SchemaCompare versions below 8 only support SQL Server, Sqlite and PostgresSql, but version 8 supports all EF Core database providers that can be be scaffolded. However some database providers may show incorrect match errors, because some database providers don't follow the SqlServer style, e.g. CosmosDB
• The EF Core 8new features aren't compared in this release. I may add some later.
• The Json Mapping checking assumes you are using the default name for the column, which is the name of the property.

The following are things I haven't bothered to check.

• Checking of Alternative keys
• Checking of collations
• Checking of sequences

EfSchemaCompare uses two EF Core features to get EF Core's internal schema and the database's schema. They are

1. EF Core'sModel property in your application's DbContext. This provides the internal version of the database schema that EF Core builds from looking at the entity classes and any EF Core configuration attributes/methods.
2. EF Core'sReverse Engineering service, which allows me to access an actual database schema.

The fun part is comparing these two sources, especially with all the different types of configurations that EF Core can handle. The diagram shown below shows using EfSchemaCompare to check a test database that you updated with some SQL migration scripts against the current EF Core'sModel.

The EfSchemaCompare uses two stages: Stage 1 checks your EF Core DbContext matches your database. Stage 2 checks your database for extra tables, columns, etc. that your EF Core DbContext doesn't use.

I usually run the EfSchemaCompare code in my unit tests, but that is up to you.

Here is a example of using the EfSchemaCompare feature

[Fact]publicvoidCompareViaContext(){//SETUPvaroptions=//... with connection to database to checkusing(varcontext=newBookContext(options)){var comparer=newCompareEfSql();//ATTEMPT//This will compare EF Core model of the database with the database that the context's connection points tovarhasErrors=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 linehasErrors.ShouldBeFalse(comparer.GetAllErrors);}}

1. TheCompareEfWithDb method can take multiple DbContexts, known asbounded contexts (see chapter 13, section 13.4.8 in my bookEntity Framework Core in Action, second edition). You can add as many contexts and they are compared to one database.
2. 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'sappsetting.json file.
   • If no connection string is found in theappsetting.json file, or there is noappsetting.json, then it assumes the string is a connection string.

See below for an example of both of of these options:

[Fact]publicvoidCompareBookThenOrderAgainstBookOrderDatabaseViaAppSettings(){//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 projectconststringconnectionStringName="BookOrderConnection";using(varcontext1=newBookContext(options1))using(varcontext2=newOrderContext(options2)){varcomparer=newCompareEfSql();//ATTEMPT//Its starts with the connection string/name  and then you can have as many contexts as you likevarhasErrors=comparer.CompareEfWithDb(connectionStringName,context1,context2);//VERIFYhasErrors.ShouldBeFalse(comparer.GetAllErrors);}}

Thecomparer.GetAllErrors property will return a string with each error separated by theEnvironment.NewLine string. Below is an example of an error

"DIFFERENT: MyEntity->Property 'MyString'. Expected = varchar(max), found = nvarchar(max)"

The error above says

• DIFFERENT: There is a difference between EF Core and the database (other settings areNotInDatabase,ExtraInDatabase)
• MyEntity->Property 'MyString', column type gives a description of what was checked
• Expected = varchar(max) says what EF Core thought it should be
• found = nvarchar(max) says what the database setting was

Here is another error coming from stage 2 where it checks the database side, i.e., Unused Tables, Columns and Indexes

EXTRA IN DATABASE: Table 'HeadEntries', column name. Found = DifferentColumnName

This says that there is a column calledMyEntityId in the tableMyEntites that hasn't got a property in the entity class mapped to theMyEntites table.

NOTE: When errors contain the wordTable it can be a SQL Table or View.

In a few cases you will get errors that aren't correct (see limitations) or not relevant. In these cases you might want to suppress those errors. There are two way to do this, with the first being the easiest. Both use theCompareEfSqlConfig class.

In this approach you capture the error strings you want to ignore and return them as a string, with each error separated by the newline,'\n', character. You feed the errors via the configuration'sIgnoreTheseErrors method. See an example below

publicvoidCompareTptContextSuppressViaIgnoreTheseErrors(){//SETUPvaroptions=this.CreateUniqueClassOptions<TptDbContext>();usingvarcontext=newTptDbContext(options);context.Database.EnsureClean();varconfig=newCompareEfSqlConfig();//This converts the error strings back CompareLog classes (see next example) which suppresses these errorsconfig.IgnoreTheseErrors(@"DIFFERENT: TptVer1->PrimaryKey 'PK_TptBases', constraint name. Expected = PK_TptBases, found = PK_TptVer1DIFFERENT: TptVer1->Property 'Id', value generated. Expected = OnAdd, found = NeverDIFFERENT: TptVer1->Property 'MyVer1Int', nullability. Expected = NULL, found = NOT NULLDIFFERENT: TptVer1->ForeignKey 'FK_TptVer1_TptBases_Id', delete behavior. Expected = ClientCascade, found = NoActionDIFFERENT: Entity 'TptVer1', constraint name. Expected = PK_TptBases, found = PK_TptVer1DIFFERENT: TptVer2->PrimaryKey 'PK_TptBases', constraint name. Expected = PK_TptBases, found = PK_TptVer2DIFFERENT: TptVer2->Property 'Id', value generated. Expected = OnAdd, found = NeverDIFFERENT: TptVer2->Property 'MyVer2Int', nullability. Expected = NULL, found = NOT NULLDIFFERENT: TptVer2->ForeignKey 'FK_TptVer2_TptBases_Id', delete behavior. Expected = ClientCascade, found = NoActionDIFFERENT: Entity 'TptVer2', constraint name. Expected = PK_TptBases, found = PK_TptVer2");varcomparer=newCompareEfSql(config);//ATTEMPTvarhasErrors=comparer.CompareEfWithDb(context);//VERIFYhasErrors.ShouldBeFalse(comparer.GetAllErrors);}}

The other approach is useful when you want to suppress a general set of errors, but it is a bit complicated. Here is an example where it suppresses any errors found on the default value set on a column.

[Fact]publicvoidCompareSuppressViaViaAddIgnoreCompareLog(){//SETUPvaroptions=this.CreateUniqueClassOptions<BookContext>();usingvarcontext=newBookContext(options);context.Database.EnsureClean();varconfig=newCompareEfSqlConfig    config.AddIgnoreCompareLog(newCompareLog(CompareType.Property,CompareState.Different,null,CompareAttributes.DefaultValueSql));varcomparer=newCompareEfSql(config);//ATTEMPTvarhasErrors=comparer.CompareEfWithDb(context);//VERIFYhasErrors.ShouldBeFalse(comparer.GetAllErrors);}

You have already seen the class calledCompareEfSqlConfig for suppressing errors, but there are two other configrations.

You have already seen the class calledCompareEfSqlConfig for suppressing errors. There is one other configuration property calledTablesToIgnoreCommaDelimited, which allows you to control what table/views in the database are considered.

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

• SetTablesToIgnoreCommaDelimited to "" (i.e. empty string)
  This will check all the tables/Views in the database.
• SetTablesToIgnoreCommaDelimited to a list of tables to ignore.
  If there are tables/views in your database that EF Core doesn't access then you need to tellCompareEfSqlabout 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 bedbo, - 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 tablesOrders andLineItem

varconfig=newCompareEfSqlConfig{TablesToIgnoreCommaDelimited="Orders,LineItem"};varcomparer=newCompareEfSql(config);

Getting all the errors in one go can be useful, for instance when you are creating a EF Core DbContext to match a given database. But by default, Stage 2 isn't run if Stage 1 found errors that haven't been register inconfig.IgnoreTheseErrors(... your error strings ...).

In version 8.2.0 a new boolean property calledAlwaysRunStage2 inCompareEfSqlConfig and if you you set this totrue then Stage 2 will always run, even if there are non-ignored errors. Seeissue #38 which made me add this new feature.