How we made friends with EF 6 MSSQL and PostgresSQL

    image

    Once upon a time there was a project on EF 6 with the 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 talk about this experience, about the problems that we encountered during the integration of the new provider, and about the solution we chose.

    Introductory


    We have a boxed product, and it has an already 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 manually. The initial installation of the database occurs from the console application with the initialization of the context on the connection string, passed as an argument:

    staticvoidMain(string[] args)
    {
        if (args.Length == 0)
        {
            thrownew 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, which is connected to the console application as a library. The context constructor in the infrastructure project looks like this:

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

    First start


    The first thing we did was to 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.

    The entityFramework section specified the default postgres factory as the connection factory:

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

    In the DbProviderFactories section, the factory of the new provider was registered:

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

    And right away, they tried to initialize the database forehead, indicating the Postgres server address and the server admin credentials in the connection string. The result is the following line:
    “Server = localhost; DataBase = TestPostgresDB; Integrated Security = false; User Id = postgres; password = pa $$ w0rd ”
    As expected, thanks to the manual EF Migrations mode, the initialization did not work, and an error occurred mismatching the database image of the current model. To circumvent the creation of the primary migration with the new provider and test the database initialization on Postgres, we slightly adjusted our infrastructure configuration.

    Firstly, we turned on “auto-migrations” - a useful option if one developer makes changes to domain models and EF infrastructure in a team:

    publicsealedclassConfiguration : DbMigrationsConfiguration<MyDbContext>
    {
        publicConfiguration()
        {
            AutomaticMigrationsEnabled = true;
            ContextKey = "Project.Infrastructure.MyDbContext";
        }
    }
    

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

    publicclassDbInitializer : CreateDatabaseIfNotExists<MyDbContext>
    {
        publicoverridevoidInitializeDatabase(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 launched our console application again with the same connection string as an argument. This time, the initialization of the context went without errors, and our domain models safely fit into the new Postgres database. The “__MigrationHistory” label has appeared in the new database, in which there was a single record of the first automatically created migration.

    To summarize: we were able to connect the new provider to an existing project without any problems, but at the same time changed the settings of the migration mechanism.

    Turn on manual migration mode


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

    First, we 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 should, at a minimum, be stored in different project folders. Therefore, we decided to create a new folder for Postgres migrations in an infrastructure project called PostgresMigrations (the folder with MsSql migrations, for clarity, we renamed it MsSqlMigrations), and copied the MsSql migration configuration file to it. At the same time, we did not copy all existing MsSql migrations to PostgresSql. Firstly, because they all contain a snapshot of the configuration for the MsSql provider and, accordingly, we will not be able to use them on the new DBMS. Secondly, the history of changes is not important for the new DBMS, and we can get by with the latest 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 the initialization of the context with the automatic migration mode turned on 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 scored the Update-Database command in the Package Manager Console, specifying only the connection string parameter. As a result, we got an error related to connecting to the DBMS.

    Having additionally studied how the Update-Database team works, we did the following:

    • added the following code

      to the migration configuration settings: for MsSql:

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

      for Postgres:

      publicConfiguration()
      {
          AutomaticMigrationsEnabled = false;
          ContextKey = "Project.Infrastructure.MyDbContext";
          MigrationsDirectory = @"PostgresMigrations";
      }
      
    • indicated the necessary parameter of the Update-Database command passing the name of the provider
    • added parameters that indicate the project containing the description of the ef infrastructure, and the folder with the migration configuration of the new provider

    As a result, we got this 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 similar parameters, naming 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 appeared in the PostgresMigrations folder: 2017010120705068_InitialCreate.cs

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

    To summarize: we were able, with minimal effort, to add the first migration for the Postgres provider and initialize the context through the console application, getting a new database, into which the changes from our first manual migration came.

    Switch between providers


    We still had one open question: how to configure context initialization so that it was possible to access a specific DBMS in runtime?

    The task was that at the initialization stage of the context it was possible to select one or another target database of the desired provider. As a result of repeated attempts to configure this switch, we came up with 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 with the provider and name of the connection, and in the context constructor we “roll through” the name of the connection instead of the connection string. At the same time, we connect the connection string itself to the context through the singleton of the DbConfiguration instance. We pass the instance of the inherited class from DbConfiguration as a parameter.

    The resulting legacy DbConfiguration class:

    publicclassDbConfig : DbConfiguration
    {
        publicDbConfig(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 followed carefully, he probably noticed that we had to make one more 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 determine the migration configuration of a particular provider:

    publicclassDbInitializer : CreateDatabaseIfNotExists<MyDbContext>
    {
        privatestring _connectionName;
        publicDbInitializer(string connectionName)
        {
            _connectionName = connectionName;
        }
        publicoverridevoidInitializeDatabase(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 began to look like this:

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

    Next, we launched the console application and specified the MsSql application parameter as the DBMS provider. We set the arguments for the application as follows:
    "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, one more subtotal - in order for EF to initialize the database context for a specific provider, in runtime you need:

    • “Indicate” the migration mechanism to this provider
    • configure DBMS connection strings before context initialization

    We work with the migrations of two DBMSs in a team


    As we saw, the most interesting part begins after the appearance of new changes in the domain. You need to generate migrations for two DBMSs taking into account a specific provider.

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

    • updating 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 "
    • adding 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 in the version control system (for simplicity we will call git). This is due to the fact that migrations to EF go sequentially one after another. And if one developer creates a migration, then another developer simply cannot add migration sequentially. Each subsequent migration stores information about the previous one. Thus, it is necessary to update the so-called snapshots of models in migration to the last created.

    At the same time, resolving conflicts on EF migrations in a team comes down to prioritizing the significance of changes in a particular developer. And whose changes are higher in priority, those should be the first to fill them in git, and the rest of the developers according to the agreed hierarchy need to do the following:

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

    As far as we are familiar 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 the question of finding an alternative to the EF Migrations mechanism has become urgent for us.

    Finally


    Working with several DBMSs 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 parallel work of the team 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 like developers than EF Migrations.

    Fortunately, we have the opportunity to make some kind of upgrade in our project. And in the near future we will switch to EF Core. We weighed the pros and cons of the EF Core Migrations mechanism and came to the conclusion 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. Ready to accept comments and answer questions, Wellcome!

    Also popular now: