EfCore.SchemaCompare

EfSchemaCompare validates that your EF Core Model (the schema defined in your code) is in sync with the actual database schema. It uses two stages of verification:

1. Stage 1 (EF Core vs Database): Checks if the EF Core DbContext matches the database. It verifies:

   • Tables/Views: Existence, name, and schema.
   • Properties/Columns: Existence, data type (size/precision), nullability, computed column settings, and default values.
   • Primary Keys: Constraint names and properties.
   • Foreign Keys: Constraint names, delete behavior, and properties.
   • Indexes: Constraint names, uniqueness, and properties.

2. Stage 2 (Database Unused Items): Checks the database for items that are not mapped in your EF Core model, specifically:

   • Unused tables or views
   • Unused columns
   • Unused indexes

To use EfCore.SchemaCompare, install the NuGet package EfCore.SchemaCompare.

Important for Version 8 and above: You MUST also add the Microsoft.EntityFrameworkCore.Design NuGet package to your application. This is a breaking change required to support multiple database providers.

The TablesToIgnoreCommaDelimited property in CompareEfSqlConfig controls which database tables or views are included in the comparison.

• Default (null): Only checks tables/views that your EF Core entity classes are mapped to.
• Empty string (""): Checks all tables and views in the database.
• Comma-delimited list: Ignores specific tables/views. Use this if your database contains tables that EF Core does not access to prevent 'extra table' errors.

Naming formats:

• MyTable: Uses the database default schema.
• dbo.MyTable: Specifies the dbo schema.

Note: The comparison is case-insensitive.

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

By default, CompareEfSql stops after Stage 1 if errors are found that have not been suppressed via IgnoreTheseErrors. If you need to see all errors (including those from Stage 2) even when Stage 1 errors exist, set the AlwaysRunStage2 boolean property to true in CompareEfSqlConfig.

Note: This property requires version 8.2.0 or later.

var config = new CompareEfSqlConfig
{
    AlwaysRunStage2 = true
};

For a more granular and programmatic approach, use AddIgnoreCompareLog on the CompareEfSqlConfig instance. This allows you to suppress entire categories of errors based on their CompareType, CompareState, and CompareAttributes. This is useful when you want to ignore all errors related to a specific property type, such as default value SQL settings, regardless of the specific entity or property name.

var config = new CompareEfSqlConfig();
// Suppresses any errors found on the default value set on a column
config.AddIgnoreCompareLog(new CompareLog(CompareType.Property, CompareState.Different, null, CompareAttributes.DefaultValueSql));

var comparer = new CompareEfSql(config);
var hasErrors = comparer.CompareEfWithDb(context);

If you encounter specific error messages that are incorrect or irrelevant (due to known limitations), you can suppress them by passing their exact error strings to the IgnoreTheseErrors method of CompareEfSqlConfig. Each error string should be separated by a newline ('\n') character. This is the easiest way to handle specific, known discrepancies.

var config = new CompareEfSqlConfig();
// Provide error strings separated by newlines
config.IgnoreTheseErrors(@"DIFFERENT: Entity 'TptVer1', constraint name. Expected = PK_TptBases, found = PK_TptVer1
DIFFERENT: TptVer1->Property 'Id', value generated. Expected = OnAdd, found = Never");

var comparer = new CompareEfSql(config);
var hasErrors = comparer.CompareEfWithDb(context);

The supported database providers depend on the version of the library you are using:

Version 8 and above

Supports all EF Core database providers that can be scaffolded. Note: Some providers that do not follow the SQL Server style (e.g., CosmosDB) may report incorrect match errors.

Versions below 8

• SqlServer
• Sqlite
• Npgsql.EntityFrameworkCore.PostgreSQL

Errors are retrieved via the comparer.GetAllErrors property as a single string with errors separated by Environment.NewLine.

Error Prefixes

• DIFFERENT: Indicates a mismatch between EF Core and the database (e.g., different data types).
• NotInDatabase: Indicates something defined in EF Core is missing from the database.
• ExtraInDatabase: Indicates something exists in the database (like an unused column or table) that is not in the EF Core model.

Example Formats

• Mismatch: "DIFFERENT: MyEntity->Property 'MyString'. Expected = varchar(max), found = nvarchar(max)"
• Unused Item: "EXTRA IN DATABASE: Table 'HeadEntries', column name. Found = DifferentColumnName"

Note: When an error mentions Table, it refers to either a SQL Table or a View.