How do we make EF 6 with MSSQL and PostgresSQL

Once upon a time there was a project on EF 6 with MSSQL DBMS. And there was a need to add the ability to work with PostgreSQL. We did not expect problems here, because there are a large number of articles on this topic, and on the forums you can find a discussion of similar problems. However, in reality, not everything turned out to be so simple, and in this article we will tell about this experience, about the problems that we encountered during the integration of the new provider, and about our chosen solution.

Introductory

We have a boxed product, and it has an established structure. Initially, it was configured to work with one DBMS - MSSQL. The project has a data access layer with EF 6 implementation (Code First approach). We work with migrations through EF 6 Migrations. Migrations are created in manual mode. The initial installation of the database occurs from the console application with context initialization by the connection string passed as an argument:

static void Main(string[] args) { if (args.Length == 0) { throw new Exception("No arguments in command line"); } var connectionString = args[0]; Console.WriteLine($"Initializing dbcontext via {connectionString}"); try { using (var context = MyDbContext(connectionString)) { Console.WriteLine("Database created"); } } catch (Exception e) { Console.WriteLine(e.Message); throw; } } 

At the same time, the EF infrastructure and domain domain are described in another project that is connected to the console application as a library. The context constructor in the infrastructure project looks like this:
')

 public class MyDbContext : IdentityDbContext<User, Role, Key, UserLogin, UserRole, UserClaim>, IUnitOfWork { public MyDbContext(string connectionString) : base(connectionString) { Database.SetInitializer(new DbInitializer()); Database.Initialize(true); } } 

First start

The first thing we did was connect two packages to the project via nuget: Npgsql and EntityFramework6.Npgsql.

And also registered in the App.config of our console application settings for Postgres.

In the entityFramework section, you specified the postgres factory as the default connection factory:

 <entityFramework> <!--<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />--> <defaultConnectionFactory type="Npgsql.NpgsqlConnectionFactory, EntityFramework6.Npgsql" /> <providers> <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" /> </providers> </entityFramework> 

In the section DbProviderFactories have registered a factory of a new provider:

 <system.data> <DbProviderFactories> <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql" type="Npgsql.NpgsqlFactory, Npgsql" /> </DbProviderFactories> </system.data> 

And immediately we tried to initialize the database, specifying the address of the Postgres server and the credentials of the server admin in the connection string. It turned out this line:

“Server = localhost; DataBase = TestPostgresDB; Integrated Security = false; User Id = postgres; password = pa $$ w0rd ”

As expected, thanks to the EF Migrations manual mode, initialization failed, and an inconsistency error occurred in the database snapshot of the current model. To circumvent the creation of a primary migration with a new provider and test the database initialization on Postgres, we adjusted our infrastructure a bit.

First, we enabled “auto-migration” - a useful option if changes in the domain models and EF infrastructure in a team are led by one developer:

 public sealed class Configuration : DbMigrationsConfiguration<MyDbContext> { public Configuration() { AutomaticMigrationsEnabled = true; ContextKey = "Project.Infrastructure.MyDbContext"; } } 

Secondly, we specified a new provider in the redefined InitializeDatabase method of the inherited CreateDatabaseIfNotExists class, where we run migrations:

 public class DbInitializer : CreateDatabaseIfNotExists<MyDbContext> { public override void InitializeDatabase(MyDbContext context) { DbMigrator dbMigrator = new DbMigrator(new Configuration { //TargetDatabase = new DbConnectionInfo(context.Database.Connection.ConnectionString, "System.Data.SqlClient") TargetDatabase = new DbConnectionInfo(context.Database.Connection.ConnectionString, "Npgsql") }); // There some code for run migrations } } 

Next, we again launched our console application with the same connection string as an argument. This time, the initialization of the context passed without errors, and our domain models successfully formed a new database on Postgres. In the new database, a “__MigrationHistory” sign appeared, in which there was a single record of the first automatically created migration.

Let us sum up the subtotal: we were able to connect the new provider to the existing project without any problems, but at the same time we changed the settings of the migration mechanism.

Enable manual migration mode

As already mentioned above, when automatic migrations is enabled, you are depriving your parallel development team in the domain and data access areas. For us, this option was unacceptable. Therefore, we needed to configure the manual mode of migrations in the project.

We first returned the AutomaticMigrationsEnabled field to false. Then it was necessary to deal with the creation of new migrations. We understood that migrations for different DBMSs, as a minimum, should be stored in different project folders. Therefore, we decided to create a new folder under Postgres migration in the infrastructure project called PostgresMigrations (the MsSql migration folder, for clarity, we renamed MsSqlMigrations), and copied the MsSql migration configuration file into it. At the same time, we did not copy all existing MsSql migrations into PostgresSql. Firstly, because all of them contain a snapshot of the configuration under the MsSql provider and, accordingly, we cannot use them on the new DBMS. Secondly, for a new DBMS, the history of changes is not important to us, and we can get by with the last snapshot of the state of domain models.

We thought that everything was ready for the formation of the first migration to Postgres. The database created during context initialization with the automatic migration mode enabled was deleted. And, guided by the fact that for the first migration, you need to create a physical database based on the current state of domain models, we happily beat the Update-Database command in the Package Manager Console, specifying only the connection string parameter. As a result, we received an error related to connecting to the DBMS.

Additionally, having studied the principle of the Update-Database command, we did the following:

• added the following code to the migration configuration settings:
  
  for MsSql:
  
  

   public Configuration() { AutomaticMigrationsEnabled = false; ContextKey = "Project.Infrastructure.MyDbContext"; MigrationsDirectory = @"MsSqlMigrations"; } 

  
  for postgres:
  
  

   public Configuration() { AutomaticMigrationsEnabled = false; ContextKey = "Project.Infrastructure.MyDbContext"; MigrationsDirectory = @"PostgresMigrations"; } 

• indicated the necessary parameter of the Update-Database command, which provides the name of the provider
• added parameters that indicate the project containing the description of the ef infrastructure and the folder with the configuration of the migrations of the new provider

As a result, we got the following command:

Update-Database -ProjectName "Project.Infrastructure" -ConfigurationTypeName Project.Infrastructure.PostgresMigrations.Configuration -ConnectionString "Server = localhost; DataBase = TestPostgresDB; Integrated Security = false; User Id = postgres; password = pa $$ w0rd "-ConnectionProviderName" Npgsql "

After executing this command, we were able to execute the Add-Migration command with the same parameters, calling the first migration InitialCreate:

Add-Migration -Name "InitialCreate" -ProjectName "CrossTech.DSS.Infrastructure" -ConfigurationTypeName CrossTech.DSS.Infrastructure.PostgresMigrations.Configuration -ConnectionString "Server = localhost; DataBase = TestPostgresDB; Integrated Security = false; User Id = postgres; password = pa $$ w0rd "-ConnectionProviderName" Npgsql "

A new file has appeared in the PostgresMigrations folder: 2017010120705068_InitialCreate.cs

Then we deleted the database created after the Update-Database command was executed and launched our console application with the connection string specified above as an argument. And here we have a database already on the basis of a manually created migration.

Let us sum up the subtotal: we were able with minimal effort to add the first migration for the Postgres provider and initialize the context through the console application, receiving the output of the new database, into which changes were made from our first manual migration.

Switching between providers

We still have one unanswered question: how to set up the initialization of the context so that you can access a specific DBMS at runtime?

The task was to make it possible to select one or another target database of the required provider at the stage of context initialization. As a result of repeated attempts to customize this switch, we came to a solution that looks like this.

In the console application of the project in app.config (and if you do not use app.config, then machine.config) we add a new connection string indicating the provider and the name of the connection, and in the context designer we “throw” the name of the connection instead of the connection string. In this case, the connection string itself is associated with the context via the singleton instance DbConfiguration. As a parameter, we pass the instance of the inherited class from DbConfiguration.

The resulting inherited DbConfiguration class is:

 public class DbConfig : DbConfiguration { public DbConfig(string connectionName, string connectionString, string provideName) { ConfigurationManager.ConnectionStrings.Add(new ConnectionStringSettings(connectionName, connectionString, provideName)); switch (connectionName) { case "PostgresDbConnection": this.SetDefaultConnectionFactory(new NpgsqlConnectionFactory()); this.SetProviderServices(provideName, NpgsqlServices.Instance); this.SetProviderFactory(provideName, NpgsqlFactory.Instance); break; case "MsSqlDbConnection": this.SetDefaultConnectionFactory(new SqlConnectionFactory()); this.SetProviderServices(provideName, SqlProviderServices.Instance); this.SetProviderFactory(provideName, SqlClientFactory.Instance); this.SetDefaultConnectionFactory(new SqlConnectionFactory()); break; } } } 

And the context initialization itself now looks like this:

 var connectionName = args[0]; var connectionString = args[1]; var provideName = args[2]; DbConfiguration.SetConfiguration(new DbConfig(connectionName, connectionString, provideName)); using (var context = MyDbContext(connectionName)) { Console.WriteLine("Database created"); } 

And who watched carefully, he probably noticed that we had to make another change in the code. This is the definition of the target database during database initialization, which occurs in the InitializeDatabase method described earlier.

We added a simple switch to define the configuration of the migrations of a particular provider:

 public class DbInitializer : CreateDatabaseIfNotExists<MyDbContext> { private string _connectionName; public DbInitializer(string connectionName) { _connectionName = connectionName; } public override void InitializeDatabase(MyDbContext context) { DbMigrationsConfiguration<MyDbContext> config; switch (_connectionName) { case "PostgresDbConnection": config = new PostgresMigrations.Configuration(); break; case "MsSqlDbConnection": config = new MsSqlMigrations.Configuration(); break; default: config = null; break; } if (config == null) return; config.TargetDatabase = new DbConnectionInfo(_connectionName); DbMigrator dbMigrator = new DbMigrator(config); // There some code for run migrations } } 

And the context constructor itself looked like this:

 public MyDbContext(string connectionNameParam) : base(connectionString) { Database.SetInitializer(new DbInitializer(connectionName = connectionNameParam)); Database.Initialize(true); } 

Next, we launched the console application and specified the MsSql application as a DBMS provider. We set the following application arguments:

“MsSqlDbConnection” “Server = localhost \ SQLEXPRESS; Database = TestMsSqlDB; User Id = sa; password = pa $$ w0rd »« System.Data.SqlClient »

The MsSql database was created without errors.

Then we specified the application arguments:

“PostgresDbConnection” “Server = localhost; DataBase = TestPostgresDB; Integrated Security = false; User Id = postgres; password = pa $$ w0rd "" Npgsql "

The Postgres database was also created without errors.

So, another subtotal - in order for EF to initialize the database context for a particular provider, at runtime it is necessary:

• “Point” to the migration mechanism on this provider
• configure the database connection strings before context initialization

We work with migrations of two DBMS in a team

As we have seen, the most interesting begins after the appearance of new changes in the domain. It is necessary for you for two DBMS to generate migrations taking into account the specific provider.

So, for MSSQL Server, you need to execute sequential commands (for Postgres, the commands described above, when creating the first migration):

• update the database according to the last snapshot
  

  Update-Database -ProjectName "Project.Infrastructure" -ConfigurationTypeName Project.Infrastructure.MsSqlMigrations.Configuration -ConnectionString "Server = localhost; DataBase = TestMsSqlDB; Integrated Security = false; User Id = sa; password = pa $$ w0rd "-ConnectionProviderName" System.Data.SqlClient "

• add new migration
  

  Add-Migration -Name "SomeMigrationName" -ProjectName "Project.Infrastructure" -ConfigurationTypeName Project.Infrastructure.MsSqlMigrations.Configuration -ConnectionString "Server = localhost; DataBase = TestMsSqlDB; Integrated Security = false; User Id = sa; password = pa $$ w0rd "-ConnectionProviderName" System.Data.SqlClient "

When developers make changes to the domain in parallel, we get multiple conflicts when merging these changes into the version control system (for simplicity, we'll call git). This is due to the fact that migrations to EF follow one after another. And if one developer creates a migration, then another developer simply adds the migration successively. Each subsequent migration stores information about the previous one. Thus, it is necessary to update the so-called snapshots of models in the migration to the last one created.

At the same time, resolving conflicts over EF migrations in a team is reduced to prioritizing the significance of changes of a particular developer. And whose changes are higher in priority, they should be the first to flood them into git, and the rest of the developers, according to the agreed hierarchy, should do the following:

1. delete created local migrations
2. pull up changes from the repository where other high-priority colleagues have already poured their migrations
3. create a local migration and upload the resulting changes back to git

As far as we are closely acquainted with the EF migration mechanism, we can judge that the described team development approach is the only one at the moment. We do not consider this solution ideal, but it has the right to life. And for us the question of finding an alternative to the EF Migrations mechanism has become urgent.

Finally

Working with several DBMS using EF6 in conjunction with EF Migrations is real, but in this version the guys from Microsoft did not take into account the possibility of the team working in parallel using version control systems.

There are many alternative EF Migrations solutions on the market (both paid and free): DbUp, RoundhousE, ThinkingHome.Migrator, FluentMigrator, etc. And judging by the reviews, they are more to the developers than the EF Migrations.

Fortunately, we have the opportunity to do some upgrade in our project. And in the near future we will be switching to EF Core. We weighed the pros and cons of the EF Core Migrations mechanism and concluded that it would be more convenient for us to work with a third-party solution, namely Fluent Migrator.

We hope you were interested in our experience. We are ready to accept comments and answer questions, well!