Some months ago I wrote an article on Simple-Talk ‘Deploying an Entity Framework Database into Production‘, with a more detailed series on this blog site (see list below). I have finally found time to release the package I mentioned in the articles as an open-source (MIT) project and also on NuGet as EfSchemaCompare.EF6.
In this article I explain where EfSchemaCompare is useful and give three real-world users of the package in my own e-commerce application development. The whole series deals with the issues of creating/updating a database schema in a robust and testable way. The list of articles are:
- Part 1 – Applying updates to a live, production database.
- Part 2 – Keeping EF’s database model in line with the actual database schema.
- Part 3 – How to take a ASP.NET MVC web site offline to allow data migrations to be added.
- Part 4 – Release of EfSchemaCompare to Nuget (this article).
- Part 5 – Documentation for EfSchemaCompare (GitHub project Readme.md file)
UPDATE: I have released a version of EfSchemaCompare for Entity Framework Core – see this article.
Where would you find EfSchemaCompare useful
The main reason to use EfSchemaCompare is if you use EF with a database but also want to:
- Take over the creation, definition or migration of the database rather than having EF handle it.
- You want to build a EF model that works with an existing database.
If you work this way then you have a problem: you need to make sure any changes to the database are matched by changes to the EF classes/configuration, or vise-versa. For instance, if you added a foreign key relationship in your database, or a new column to a table then you need to make the correct changes to the EF classes/configuration.
The important thing to see is that, other than when using EF reverse engineering (see next paragraph), EF never looks at the schema of the database, even when it is doing migrations. It just assumes the database in the state that EF’s metadata model says it is. (For more on how this works under the hood then see Max Vasilyev’s interesting article about how EF migrations are calculated and stored).
So one way to overcome any mismatch between EF and SQL is to re-import the database via EF’s reverse engineer the database option. However I have found that to be a rather blunt instrument as it adds all the possible relationships and also has fixed names for properties.
However now EfSchemaCompare gives you a second option. EfSchemaCompare is designed to check that EF’s view of the database is in line with what SQL says the database schema is. You can use it in your Unit Tests, or in your deployment scripts. It returns a go/no-go response with useful error/warning messages to spot what is wrong. It can also help you work out how to set up SQL tables to handle some of the more complex EF table relationships, like Many-to-Many tables (see example 3 near the end).
Let me give you three examples of how I use EfSchemaCompare.
Examples of how I use EfSchemaCompare
I was building an e-commerce web site on Azure and I wasn’t comfortable that EF’s Code First Migrations approach was robust enough in my situation (read the first article for more details on why). I therefore swapped over to using DbUp, which is a script-based database deployment/update tool.
However it turns out matching a database change to the form that EF needs is quite complex! I therefore wrote EfSchemaCompare . I will detail three different ways I use EfSchemaCompare in my Unit Tests and deployment tests.
1. General checking in Unit Tests
The NUnit Test below is part of my standard tests and it simply checks that EF’s current model of the database matches the database my Unit Test has.
[Test]
public void CompareEfSqlTestLabelAppDbOk()
{
using (var db = new MyDbContext())
{
var comparer = new CompareEfSql();
var status = comparer.CompareEfWithDb(db);
Assert.True(status.IsValid, status.GetErrors());
Assert.False(status.HasWarnings, string.Join("\n", status.Warnings));
}
}
The method it uses, CompareEfSql.CompareEfWithDb, is fairly quick, assuming the MyDbContext has been used in the Unit Tests, and finds 90% of the issues that can occur. It also gives error messages that talk about the EF classes rather than in SQL terms, so its easier for software developers to understand.
Note: The above version assumes the EF POCO classes are in the same assembly as the EF DbContext. There is another version if you have your EF POCO classes in a different assembly to the EF DbContext (I do!). There is also another version if you have multiple DbContexts covering one database.
2. Detailed checking before I release to production
The NUnit Test below checks my production SQL database against the development database. The SQL to SQL checking is more thorough, but only checks things that EF cares about (see list of limitations).
[Test]
public void CompareLabelAppDbWithAzureTestLabelAppDbOk()
{
using (var db = new MyDbContext())
{
var comparer = new CompareSqlSql();
var status = comparer.CompareSqlToSql( "LabelAppDb", "AzureLabelAppDb");
Assert.True(status.IsValid, status.GetErrors());
Assert.False(status.HasWarnings, string.Join("\n", status.Warnings));
}
}
The method it uses, CompareSqlSql.CompareSqlToSql which is very quick. It takes either a connection string name, or a full connection string. The error messages are all about SQL, so can be harder to interpret if your aren’t use to SQL, but its comprehensive.
Note that I use the more strict version where my Unit Test fails on warnings (see line 10 test). Warning are things that should not affect EF, but are worth knowing about. You may not want to be so strict, but in my application I am. See the documentation on the difference between errors and warning.
3. When I am making complex changes to the database
Some changes are really easy, like adding a new column to a table. However when you change relationships it can be a challenge to get the SQL and EF classes in step. There is another command, CompareEfGeneratedSqlToSql, which is very useful.
This generates a brand new database using EF, with the name of the DbContext but with ‘.EfGenerated’ appended on the end. It then compares this with your current database using SQL-to-SQL testing.
[Test]
public void CompareLabelAppDbWithAzureTestLabelAppDbOk()
{
using (var db = new MyDbContext())
{
var comparer = new CompareSqlSql();
var status = comparer.CompareEfGeneratedSqlToSql(db, "LabelAppDb");
Assert.True(status.IsValid, status.GetErrors());
Assert.False(status.HasWarnings, string.Join("\n", status.Warnings));
}
}
The method it uses, CompareSqlSql.CompareEfGeneratedSqlToSql, is quite slow because it gets EF to create a new database. However it does provide a way to a) compare exactly what EF would have done against what your SQL database looks like and b) you get a EF generated database from your current EF classes/configuration to inspect yourself.
Some things, like EF’s Many-to-Many relationships, require the SQL database to have a new table with a specific name and setup. Failing to follow the style that EF needs will mean EF won’t fill in the many-to-many links. Adding the right tables is often a case of seeing what EF does by inspecting the ‘.EfGenerated’ database and copying the SQL EF generated into your new database (or implementing your own many-to-many handling of course, which I cover in Part 2 of this article).
The other thing I noticed is that EF is somewhat forgiving if you make mistakes when you use EF’s Data Annotations or EF’s Fluent API to alter the way the database works. For instance if you configure a required to optional relationship but give it a non-nullable foreign key it will add its own hidden nullable foreign key. In fact I found about three errors like this in my 22-table application, all around relationships.
EfSchemaCompare will point out these discrepancies and you can fix them. Your minor configuration mistakes can be found mainly by the column names, which normally contain the name with a _ in the middle. I have also found places where the Cascade delete option was different, again through me misconfiguring a relationship.
Conclusion
Hopefully this article will give you a feel for why you might find the NuGet package EfSchemaCompare.EF6 useful, plus some tips on how to use it in your application. Its certainly a niche tool, but if you are using EF on a database that is not controlled by EF then its a life-saver. Do have a look at the project on GitHub for documentation and Unit Tests that might give you more ideas on how to use it.
Finally I wanted to thank and Hakon Thomas who field tested the first version. Their feedback has shaped the tool and hopefully made the documentation a bit clearer too.
Happy coding.
Want some help with EF?
Are you looking for someone to help you use Entity Framework more effectively in your business? I am a contract software developer / architect with many years of experience and I am happy to work remotely.
Have a look at my Hire Me page and drop me a email via my contact me form to see if I can help.