Create/ensure compatible db structures at runtime

[MatthewMWR]

Feature request: Create/ensure compatible db structures at runtime

In EF6 there were APIs along the lines of Database.EnsureCompatible() (don't remember the exact names). These we useful for scenarios not covered by EFCore implementation of Migrations or EnsureCreated() . For example:

1. My end-users are often enterprise IT admins collaborating across groups. They often have full control within a database provided to them by DBAs, but they do not have permission to create new databases within shared production infrastructure. For these scenarios EnsureCreated() isn't helpful, but something like EnsureCompatible() would be. For these same users Migrations could potentially be an option, but...
2. Migrations tooling doesn't work well with extensible models that span loosely coupled solutions and are defined at run-time. Imagine an override of OnModelCreating where the model is built dynamically based on classes in currently loaded assemblies which implement some interface (or insert any other run-time model building approach). In such cases Migrations tooling doesn't appear to fit because Add-Migration expects to know the shape of the db at design time from a single VS solution. Also Migrations may be overkill for a scenario where no migrating is actually desired.

Please consider for future development either adding an EnsureCompatible() like API, or extending Migrations infrastructure with runtime flexibility such that migrations can be created and used at runtime rather than via Visual Studio and design time artifacts.

Thanks.

[MatthewMWR]

Possibly related to #6214

[ajcvickers]

@MatthewMWR The compatibility checking in EF6 was based on having metadata for your model in the database, typically in the MigrationsHistory table. But it seems like you are asking to be able to check compatibility without having control over how the database is created/maintained--i.e. with just a database schema to work with. If this is the case, then I don't think it is something we are going to implement. It would essentially be a reverse-engineer of the database to a model and then a diff of that model against the current model. We have discussed doing this in the past, but while it would work in simple cases it is hard to make it robust enough to be really useful.

On the other hand, if you are OK with creating a database with model metadata in it, then I believe Migrations has all the infrastructure to do the compatibility check although I would have to ask for help for how to wire it all up to work at runtime.

[MatthewMWR]

Thanks for the context. I see now that my fictitious method name implied a much broader two way set of logic than what I had in mind. The two way compatibility matrix logic you described would be nifty, but what I want is considerably less ambitious. AllI really want is a version of EnsureCreated() that will still try to create the tables if the db already exists. The code to create the tables from current model already exists, but currently there is no way to call it outside of EnsureCreated() which bails if the db already exists.
By making it possible to separate these existing sub-routines (create db, vs create db internal layout) I can deal with separation of privilege where one team might create the db (as db creator at the instance level) while my caller can create the tables and whatnot (as db_owner or similar at the db level).

[ajcvickers]

@MatthewMWR You need to use IRelationalDatabaseCreator. You can get one from the context like this:

var creator = context.GetService<IRelationalDatabaseCreator>();

This gives you lower-level access over the database operations.

[ackava]

@MatthewMWR I created a simple tool for the same, I need to rewrite using IRelationalDatabaseCreator, but this tool is working well against SQL Server with simple data structure. https://github.com/neurospeech/ef-core-live-migration , I would be happy to have some contribution to improve it. I have also created a mock library to mock Sql LocalDB per instance at https://github.com/neurospeech/ef-core-localdb-mock