22

For my surprise, using the CreateDatabaseIfNotExists context initializer, the line

context.Database.Initialize(true)

doesn't throw an exception if the schema does not match my code first schema.

Is there a way to validate if the current database matches our schema before, for instance, we try to access a entity, whose table doesn't exist on the database anymore, and an exception is thrown by EF?

Eduardo Brites
  • 4,597
  • 5
  • 36
  • 51

3 Answers3

24

You can call CompatibleWithModel to determine if the database matches the model. If you set the parameter to true it will throw an exception if no model data is found in the database.

bool isCompatible = context.Database.CompatibleWithModel(true);
AxdorphCoder
  • 1,104
  • 2
  • 15
  • 26
  • 1
    What type if the thrown exception? – Jerther Dec 18 '14 at 18:35
  • 1
    I've checked EF internals and it seems that NotSupportedException will be trown in case of no metadata in the database. – Alexander Feb 08 '16 at 15:31
  • 2
    As a note, I tried to use this on a database-first application, and it can only work with Code-first Databases. Thank you, though. – Laki Politis Jun 07 '17 at 04:04
  • Code-first really means 'code only'. You can't go changing the DB manually, unless you add a migration for every change - which can be manual SQL or a DBA provided script. You just can't expect EF to compare the actual live DB schema with the model. – Simon_Weaver Dec 05 '17 at 12:00
  • 14
    Just FYI, this appears to be removed in EF Core :\ Does anyone know of a replacement in EF Core? – pbristow Jan 10 '18 at 21:16
  • In the EF (not core) this does not throw an exception. It just returns false if model is not compatible. – hakan Mar 04 '20 at 07:46
  • I'm not sure this really checks the database structure. I think it compares EF metadata, regardless of the actual DB structure. It's more like checking if the code model is still up to date compared with the last migration that was applied (if any). – Dave Cousineau Jul 10 '22 at 00:10
8

EF does not cross check database schema with model each time you start your application. Instead it is looking for the model that is saved to the database (__MigrationsHistory table and before EdmMetadata) and compare this saved model with the model you are using. If models match then database will be used. If models don't match an exception will be thrown. If you have neither __MigrationHistory nor EdmMetadata table in your database EF will assume that you are using Database first approach with DbContext and your database matches the model. If you want to compare the database with your model you could dump Edmx for your model (using EdmxWriter.WriteEdmx) and use the Visual Studio and EF designer get the Edmx from Database and compare SSDL parts.

Pawel
  • 31,342
  • 4
  • 73
  • 104
  • "EF will assume that you are using Database first approach with DbContext and your database matches the model". So everytime my application start I have to check if all tables and all columns exist in the database? – Eduardo Brites Oct 26 '12 at 15:53
  • 3
    If you change neither model nor the database and they matched before then you probably don't need to do this. I think that regardless of the access technology you use this problem exists and the development process should take care of this. Rediscovering the database at runtime seems like a wrong approach to me. – Pawel Oct 26 '12 at 16:20
  • 4
    @Pawel you don;t seem to have shipped products then! Customer installs product 1.0, then you update it which requires a DB change. Customer then installs new code but fails (for whatever reason) to correctly update the schema - then they run the product and you get a nasty exception when the code attempts to read from the missing columns. So it's better to check first, or at least attempt some validation so you can pop up a better error message and then quit. – gbjbaanb Sep 06 '13 at 12:41
  • 1
    If your application consists of multiple dlls do you check if they all exist each time before you start your app? For whatever reason one of them might be missing when you deploy your app and you will get a nasty exception. What you describe is in my mind a similar situation - you failed to install your application properly. Once the application is installed properly (i.e. all the dlls are in place and the database is updated correctly) is the missing check the problem? Practically - how would you handle such a check? What if a column type was changed? Length? Index? New table? – Pawel Sep 06 '13 at 13:48
  • 2
    @Pawel The framework really ought to handle this sort of check since it has all of this information available to compare against the database within the EDMX. I would also argue that an application consisting of multiple DLLs which it loads in at runtime should check to see if they're there before it tries to use them; a `FileNotFoundException` with an explanation of what file is missing and where to get it is far more useful than a `NullReferenceException` resulting from the file not being found and the code continuing to execute. – Matt Arnold Aug 05 '19 at 10:23
5

There are two tools to do this. This first one is popular and highly developed:

the developer's explanation: https://www.thereformedprogrammer.net/ef-core-taking-full-control-of-the-database-schema/

As you'll see, the developer's own explanation includes a thorough overview of Code-First, Database-First, and SQL-First approaches. He discusses pros and cons of all approaches. And shows why the Schema Compare tool is necessary to use a SQL-First approach.

the github project: https://github.com/JonPSmith/EfCore.TestSupport/wiki/9.-EfSchemaCompare

And there is a lesser-known second one mentioned by another commentor above: https://github.com/reckface/EntityFramework.Verify

The second developer also suggests DbUp, which offers a Philosophy article that I think is a worthwhile read on why the "Microsofty" code-first and db-first approaches are problematic, and why the idea of viewing database changes as a state system is arguably a poor choice. https://dbup.readthedocs.io/en/latest/philosophy-behind-dbup/

JamesHoux
  • 2,999
  • 3
  • 32
  • 50