EfSchemaCompare

EfSchemaCompare is useful if you using Microsoft's Entity Framework (EF) and want to either:

1. Take over the creation, definition or migration of the database.
2. You want to build a EF model that works with an existing database.

If you are already doing this, or want to move away from EF's own database mirgrations then you will find the SchemaCompareDb package useful for checking everything is OK.

EfSchemaCompare provides a way to check EF's internal metadata againts an actual SQL database. The idea is you include these tests in your Unit Tests so that you get error messages when EF and your development database, and more importantly your production databases, are out of step.

If you include a Unit Test of your production database using EfSchemaCompare before deploying a new software update it could save you from potentially nasty problem of forgetting to update your production database.

Read an article about EfSchemaCompare here.

The rest of this Readme file contains documentation of all the methods and settings.

EfSchemaCompare is an open-source project (MIT licence),
and is available on NuGet as EfSchemaCompare.EF6

Why I built this project?

I was working on an e-commerce web site and was thinking through the problems of applying database migrations to a production site. My concern was that EF's built-in data migration approach is fine for development and some projects, but in a production environment, where an error during a database migration could cost you some serious down time, I needed a better approach.

You can see how I used EfSchemaCompare in my own application in this article. I wrote a long article called 'Deploying an Entity Framework Database into Production' where I describe why and how I build SchemaCompareDb. This is a good article to read to get an overview of SchemaCompareDb.

I have also have started a series on database migrations on my own blog site which covers the same area, but with a bit more detail.

How to use SchemaCompareDb

There are three main ways of comparing EF and databases:

1. CompareEfWithDb: Compare EF's DbContext(s) against an actual SQL database.
2. CompareEfGeneratedSqlToSql: Compare a database created by EF against an actual SQL database.
3. CompareSqlToSql: Compare one SQL database against another SQL database.

I use all three (see this article for examples): The first gives the best error messages but cannot check all possible combinations (in EF6 anyway). The second covers 100% of the EF differences, but the errors are more SQL-centric so sometimes harder to relate to the EF code. The last one, CompareSqlToSql, is really quick and useful to check that all of your databases are at the same level.

The difference between Errors and Warnings

All the methods return an Interface called ISuccessOrErrors. This has two components, Errors and Warnings, and its really important to understand what each is used for so that you can interpret the output sensibly.

Errors

The returned ISuccessOrErrors has a boolean property called IsValid that is true if there are no errors (but there could be warnings). If there are errors then they can be accessed via the Errors property, which is a IReadOnlyList<ValidationResult>. The actual error message on each entry can be accessed by either .ErrorMessage or .ToString();

Errors are things that EfSchemaCompare believes will stop EF working properly with your database. Typical issues are missing tables, columns, relationships, indexes or a mismatch in type, size etc of a column or relationship.

Warnings

The returned ISuccessOrErrors has a boolean property called HasWarnings that is true if there are warnings. If there are warnings then they can be accessed via the Warnings property, which is a IReadOnlyList<string>

Warnings are differences between the two databases which EfSchemaCompare believes should not cause problems to EF. Typically they are:

• Extra tables in SQL that EF does not refer to - these are normally safe.
• Columns in a SQL table that EF does not refer to (in some cases these can cause problems, especially on create/update).
• The size of a string (varchar, nvarchar) or other type with a length is at max in SQL but not at max in EF. This can happen when you have a [MaxLength(nn)] setting on an EF column, but the size is over the point where SQL makes it max length.
• When doing a SQL-to-SQL compare then indexes in the second, database to be checked, but not in the reference database.

Later you will also see that you can relegate differences in Indexes from errors to warnings, as EF adds lots of Indexes. (see CompareSqlSql ctor)

However, if you want check for an exact match between two databases you should check that .IsValid is true and .HasWarnings is false.

Detailed description of each of the commands.

1. CompareEfSql

The CompareEfSql class is used to compare EF's DbContext(s) againts a SQL database. This catches 90% of issues and gives good, EF centric, error messages. It is slower than the CompareSqlSql, especially when calling the CompareEfGeneratedSqlToSql version, but the errors they produce provide superior feedback to where the mismatch is.

CompareEfSql can work with a single DbContext that covers the whole of the database, or multiple DbContexts that cover different parts of the database.

The CompareEfSql ctor has an optional parameter which takes a comma delimited list of tables in the SQL database to ignore when looking for missing tables. Its default value is "__MigrationHistory,SchemaVersions", which ignores the "__MigrationHistory" that EF uses and the "SchemaVersions" table that DbUp adds. Note: DbUp is my chosen way of handling data migrations.

1.a: EF classes in the same assembly as DbContext

The code below show a call to CompareEfWithDb that compares the EF internal model with the database that YourDbContext points to:

using (var db = new YourDbContext())
{
    var comparer = new CompareEfSql();

    var status = comparer.CompareEfWithDb(db);
    //status.IsValid is true if no errors.
    //status.Errors contains any errors.
    //status.Warnings contains any warnings
}

1.b: EF classes in a different assembly

If you have your EF data classes in an separate assembly to your DbContext (I do) then you need to use the form that takes a Type, which should be one of your EF data classes. It uses this type to find the right assembly to scan for the data classes. Note: this cannot handle data classes in multiple assembly.

var status = comparer.CompareEfWithDb<AnEfDataClass>(db);

1.c: Compare EF with different database

If you want compare EF with another database then you provide a second parameter, which should be the name of a connection string in your App.Config/Web.Config, or a actual connection string, e.g.

var status = comparer.CompareEfWithDb(db, AConnectionStringName);

1.d: Compare multiple EF DbContexts with a database

Sometimes you may want to split the coverage of the database over multiple DbContexts. In this case we can support this (with some limitations that I explain later). This works in three stages:

1. Setup: call CompareEfPartStart(db) or CompareEfPartStart(AConnectionStringName) to setup the compare.
2. Compare: call CompareEfPartWithDb(db) (or other variants) for each DbContext
3. Finalise: call CompareEfPartFinalChecks() to do a final check for unused tables.

Below is an example where two DbContexts, DbContext1 and DbContext2, cover the same database. In this example I combine all the errors so I can check them at the end, but you can check each one as you go if you like.

var comparer = new CompareEfSql();
ISuccessOrErrors status;

using (var db = new DbContext1())
{
    comparer.CompareEfPartStart(db);
    status = comparer.CompareEfPartWithDb(db);
}
using (var db = new DbContext2())
{
    status.Combine(comparer.CompareEfPartWithDb(db));
}
status.Combine(comparer.CompareEfPartFinalChecks());
//Now check the errors and warnings

Limitations on CompareEfParts

The only limitation is that the standard calls shown above assume that there is no overlap of the classes/tables that each DbContext references, e.g. DbContext1 and DbContext2 cannot both have an EF data class called MyClass. This is because one reference to a class by one DbContext removes it from the list of available tables, so the second reference will fail.

If you do share classes between DbContexts then you should create a new CompareEfSql for each DbContext and call just CompareEfPartStart and CompareEfPartWithDb and not call CompareEfPartFinalChecks. You will miss out on some tests, like unused tables, but other than that it will work OK.

1.e. CompareEfGeneratedSqlToSql

T