Entity Framework Code-First Migrations support for Oracle, MySQL, PostgreSQL and SQLite

ADO.NET EF providers Devart dotConnect for Oracle, dotConnect for MySQL, dotConnect for PostgreSQL, and dotConnect for SQLite implement support for Entity Framework 4.3 Beta 1 (Entity Framework Code-First Migrations). To use it in your applications, you need to download and install the corresponding Entity Framework NuGet package first. You can read about Entity Framework Code-First Migrations functionality in the corresponding MSDN articles.

• Code-First Migrations Features
  • Database Initialization Strategies
  • EdmMetadata and __MigrationHistory Tables
  • dbo
  • ColumnTypeCasingConvention
• Configuration and Deployment of Entity Framework Provider
  • New Assembly
  • Registration of Code-First Migrations SQL Generator
  • EntityFramework.dll Assembly Versions
• Migration Operation Support
• Additional Database-specific Customization of Migrations
  • Creating Tables
  • Creating Columns
  • Creating Indexes
  • Modifying Columns
  • Dropping Tables
• Ideas for Future

Code-First Migrations Features

Entity Framework Code-First Migrations continues the development of Entity Framework Code-First functionality from Entity Framework 4.1 and Entity Framework 4.2, extending the functionality of dynamic database creating and deleting with the possibility of dynamic database schema modification (adding new columns/tables/foreign keys, creating and modifying existing database objects).

Devart Entity Framework providers support basic Entity Framework Code-First functionality for a long time. This support was described in the following blog articles:

• Entity Framework Code-First support for Oracle, MySQL, PostgreSQL and SQLite – general article on Code-First functionality with an example of creating an application.
• Entity Developer – EF Code First DbContext Template – this article describes how to ease application development with Entity Developer – a powerful visual Entity Framework model designer, which is included in our providers.

The information in these articles is applicable to Entity Framework Code-First Migrations development as well, just some new features were added. We won’t duplicate the information from these articles in the current one, and we recommend to take a look at them if you have not read them earlier.

Database Initialization Strategies

When using Code-First functionality of Entity Framework 4.1 and Entity Framework 4.2, only three database initialization strategies are available: DropCreateDatabaseAlways, DropCreateDatabaseIfModelChanges and CreateDatabaseIfNotExists. They allow delete and re-create the entire database schema. Entity Framework 4.3 adds the MigrateDatabaseToLatestVersion migrations database initializer that allows updating database schema according to the latest Entity Framework model version automatically.

EdmMetadata and __MigrationHistory Tables

Entity Framework 4.3 does not use the “EdmMetadata” table, so, unlike Entity Framework 4.1 or 4.2, you don’t need to remove the IncludeMetadataConvention convention explicitly if you don’t want to use this table. However, there is a new table “__MigrationHistory” in Entity Framework 4.3 and you cannot enable or disable its presence in the database with some convention.

dbo

In Entity Framework 4.1 and Entity Framework 4.2 the problem of automatic mapping database objects to the dbo schema could be solved easily. If you needed the EdmMetadata table, the IgnoreSchemaName had to be used, otherwise, you could remove IncludeMetadataConvention and specify the schema name for each class explicitly when mapping classes.

In Entity Framework 4.3 there is the __MigrationHistory table, that cannot be excluded, and this is the reason why an attempt to create the “dbo”.”__MigrationHistory” table occurs. So, there is only one solution for Entity Framework 4.3 – always use the IgnoreSchemaName mode (set it in a constructor or static constructor of your DbContext-descendant or in the OnModelCreating method):

var config=Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;  
config.Workarounds.IgnoreSchemaName = true;

ColumnTypeCasingConvention

This case is specific for dotConnect for Oracle and users of dotConnect providers for MySQL, PostgreSQL and SQLite may skip it.

You should explicitly remove ColumnTypeCasingConvention for DbContext in its overridden OnModelCreating method to use dotConnect for Oracle with Code-First (Entity Framework 4.1 and Entity Framework 4.2).

protected override void OnModelCreating(DbModelBuilder modelBuilder) {

    modelBuilder.Conventions
        .Remove<System.Data.Entity.ModelConfiguration
            .Conventions.ColumnTypeCasingConvention>(); 

    // ...
}

It is not enough for Entity Framework 4.3 Beta 1 because an internal DbContext is created in the Code-First Migrations engine, and this DbContext is not aware of the removed conventions of the user’s one. That’s why we implemented a new Entity Framework provider configuration parameter – ColumnTypeCasingConventionCompatibility. This parameter allows you to avoid explicitly turning off the ColumnTypeCasingConvention for each DbContext when working with Devart dotConnect for Oracle. Just set it to true once in a constructor or static constructor of your DbContext-descendant or in the OnModelCreating method:

var config=Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;  
config.Workarounds.ColumnTypeCasingConventionCompatibility = true;

Entity Framework 4.1 and Entity Framework 4.2 Code-First users can also use this option instead of removing the convention explicitly.

Configuration and Deployment of Entity Framework Provider

New Assembly

A new assembly was added to our providers; you need to add references to this assembly to your projects, using Code-First Migrations:

ProviderAssembly

When deploying these projects, you should deploy this additional assembly as well.

Registration of Code-First Migrations SQL Generator

Registration of Code-First Migrations SQL Generator is performed in the Configuration class constructor – the descendant of the DbMigrationsConfiguration class, which is added to a project when executing the Enable-Migrations command in the Package Manager Console.

Specify the Using directive:

using Devart.Data.Oracle.Entity.Migrations;

If user’s DbContext stores its connection string in the application config file with the same name as the DbContext class, you need to register only the SQL generator for the specific provider:

public Configuration()
{
    this.SetSqlGenerator(OracleConnectionInfo.InvariantName, 
                                new OracleEntityMigrationSqlGenerator());

    // ...
}

If DbContext uses another way to create a connection, then you need to specify both SQL generator and connection string:

public Configuration()
{
   var connectionInfo = OracleConnectionInfo.CreateConnection("User Id=SCOTT; 
Password=TIGER; Server=ORA;");
    this.TargetDatabase = connectionInfo;
    this.SetSqlGenerator(connectionInfo.GetInvariantName(), 
                               new OracleEntityMigrationSqlGenerator());

    // ...
}

These registrations configure only Code-First Migrations. You still need to register ADO.NET provider in the global machine.config file or in a local application config file. See the Deployment topic in the documentation of the corresponding provider for the registration examples.

EntityFramework.dll Assembly Versions

Our Devart.Data.(Xxx).Entity.Migrations.dll assemblies are built with the current release version of the EntityFramework.dll assembly, that is installed with the Entity Framework by the following NuGet package command:

Install-Package EntityFramework

The current EntityFramework.dll version is 4.3.1. This is the version we build our assemblies with. Users of the current Entity Framework Code-First Migrations version should have no problems. However, users of the previous 4.3.0 version or those who have installed and earlier version of Entity Framework Code-First Migrations on some purpose with, for example, the following command

Install-Package EntityFramework -Version 4.3.0.0

and users of Entity Framework beta versions, using the command

Install-Package EntityFramework -IncludePrerelease

may face assembly version conflict because our assembly requires EntityFramework.dll exactly of the 4.3.1 version.

This problem can easily be solved by specifying binding redirect in the user’s config file. For example, for binding with 4.3.0.0 version to the current 4.3.1.0 you need to add the following lines to the config file:

  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="EntityFramework" 
publicKeyToken="b77a5c561934e089" />
      <bindingRedirect oldVersion="4.3.1.0" newVersion="4.3.0.0" />
    </dependentAssembly>
    </assemblyBinding>
  </runtime>

Migration Operation Support

Entity Framework Code-First Migrations offer many operations that can be performed with database objects. However, not all of them can be supported for every DBMS. The following table lists these operations and contains information on their support in dotConnect providers for different DBMSs.

Operation	Oracle	MySQL	PostgreSQL	SQLite
Table operations
CreateTable
DropTable
RenameTable
MoveTable	*	*	*	*
Column operations
AddColumn
AlterColumn	/**			***
DropColumn				***
RenameColumn				***
Constraint operations
AddPrimaryKey				***
DropPrimaryKey				***
AddForeignKey				/****
DropForeignKey				***
Index operations
CreateIndex
DropIndex
RenameIndex		/*****
SQL operations
Sql

* MoveTable operation is not supported because of using the IgnoreSchemaName workaround.

** The ALTER COLUMN statement that changes the column type cannot be executed for a LOB column (CLOB/NCLOB/BLOB) in Oracle. See details in the next section.

*** The ALTER TABLE statement is very limited in SQLite (see here). Most of the table modification operations are impossible to implement.

**** The ADD FOREIGN KEY constraint operation for a foreign key, consisting of a single column, is supported for the case when a new column for the foreign key is created.

***** Index renaming is supported starting from MySQL 5.7 and higher. For MySQL 5.6 or lower, delete and recreate the necessary index instead. We cannot automatically drop/recreate index (with 5.6 or lower) because Code-First Migrations don’t provide the necessary metadata to the underlying EF-provider. So we have only the names of old and new indexes but the corresponding columns are not known.

Additional Database-specific Customization of Migrations

Migrations can be customized. Our first release with Entity Framework Code-First Migrations support includes customizations for Oracle only. Our customization allow specifying tablespace for tables, creating expression-based indexes, virtual columns, etc.

We are considering the possibility to support such functionality for other databases if users of these databases are interested in this functionality.

Creating Tables

CreateTable operation can be customized with the CreateTableConfiguration class. It has the following public properties:

• Tablespace – specifies TABLESPACE for the table.
• Unlogged – if set to True, an unlogged table is created. False by default.

If the primary key is specified as database-generated (Identity option), then a sequence and an insert trigger will be generated for this table to make this column autoincrement. You can specify custom names for this sequence and trigger instead of auto-generated default ones with the following CreateTableConfiguration class properties:

• IdentityTriggerName – specifies the name of the trigger.
• IdentitySequenceName – specifies the name of the sequence. The sequence and the insert trigger will be generated only if the primary key is specified as database-generated (Identity option).

An example of the generated migration code before the customization:

    public partial class CreateBlogTable : DbMigration
    {
        public override void Up()
        {
          CreateTable(
              "Blogs",
              c => new {
                BlogId = c.Int(nullable: false, identity: true),
                Name = c.String(unicode: false),
              })
              .PrimaryKey(t => t.BlogId);

        }

        public override void Down()
        {
            DropTable("Blogs");
        }
    }

The example of the migration code with the customization code added:

    using Devart.Data.Oracle.Entity.Migrations;
    public partial class CreateBlogTable : DbMigration
    {
        public override void Up()
        {
          CreateTable(
              "Blogs",
              c => new {
                BlogId = c.Int(nullable: false, identity: true),
                Name = c.String(unicode: false),
              },
              anonymousArguments: new CreateTableConfiguration() {
                Tablespace = "MY_TABLESPACE"
              })
              .PrimaryKey(t => t.BlogId);

        }

        public override void Down()
        {
            DropTable("Blogs");
        }
    }

Creating Columns

AddColumn operation can be customized with the AddColumnConfiguration class. It has the following public properties:

• NotNullConstraintName – the name of the NOT NULL constraint. By default, an unnamed NOT NULL constraint is created.
• CheckConstraintName – the name of the check constraint.
• CheckConstraintExpression – the expression of the сheck constraint (for example, “COLUMN_NAME IN (‘A’, ‘B’, ‘C’)”).
• VirtualColumnExpression – the expression for the virtual column (for example, “FIRST_COLUMN * SECOND_COLUMN – THIRD_COLUMN”).

Creating Indexes

CreateIndex operation can be customized with the CreateIndexConfiguration class. It has the following public properties:

• IsBitmap – determines whether to create a bitmap index. False by default.
• Reversed – determines whether to create a reverse ordered index. False by default.
• Unsorted – determines whether to create an unsorted index. False by default.
• Unlogged – determines whether to create an unlogged index. False by default.
• Expression – specifies the expression for the expression-based (function-based) index.
• Tablespace – specifies TABLESPACE for an index.

Modifying Columns

Oracle has a set of limitations on column modification operations. It is not allowed to modify data type for CLOB/NCLOB/BLOB columns even for an empty table. It is also not allowed to specify the NOT NULL constraint for a column that already has one, and to specify that the column is nullable if it is already nullable. We have made a workaround for the the latter limitation by generating more complex DDL, however there is no workaround for the LOB column modification limitation because it is impossible to determine whether the user wants to change data type. That’s why any ALTER’s for LOB columns are not allowed by default.

However user may customize ALTER COLUMN behaviour with the AlterColumnConfiguration class by passing the configured instance of this class to the AlterColumn method of migration.

AlterColumnConfiguration class has the following public properties:

• LobModificationAllowed – determines whether to allow LOB column modification. Data type is not modified, only other column parameters are modified. False by default.
• ErrorCatchingForNullableAltering – determines whether to generate an additional DDL code for catching and processing errors that occur in case of unsuccessful modification of the NULL/NOT NULL constraint for columns that have the same nullability as the one applied by this modification. True by default.
• DataTypeAlteringEnabled – determines whether to modify data type. True by default.
• DefaultValueAlteringEnabled – determines whether to modify the default value. True by default.
• NullableAlteringEnabled – determines whether to modify NULL/NOT NULL constraint. True by default.

An example of the generated migration code before the customization:

    public partial class AlterColumn : DbMigration {

      public override void Up() {
        AlterColumn("Blogs", "Title", c => c.String(nullable: false));
      }

      public override void Down() {
        AlterColumn("Blogs", "Title", c => c.String(nullable: false));
      }
    }

The example of the migration code with the customization code added:

    using Devart.Data.Oracle.Entity.Migrations;

    public partial class AlterColumn : DbMigration {

      public override void Up() {
        AlterColumn("Blogs", "Title", c => c.String(nullable: false),
          anonymousArguments: new AlterColumnConfiguration() {
            LobModificationAllowed = true,
          });
      }

      public override void Down() {
        AlterColumn("Blogs", "Title", c => c.String(nullable: false));
      }
    }

Dropping Tables

If the primary key is specified as database-generated (Identity option), then a sequence and an insert trigger will be generated for this table to make this column autoincrement. When deleting such a table, the trigger is deleted automatically, but the sequence should be deleted explicitly. Devart dotConnect for Oracle tries to delete a sequence with the default name automatically. However, if the sequence name was customized with the CreateTableConfiguration class, you need to specify the custom sequence name in the DropTableConfiguration class for the DropTable operation.

DropTableConfiguration class has the following public properties:

• IdentitySequenceName – the sequence name.
• IdentitySequenceDroppingEnabled – determines if the identity sequence should be deleted. When not specified, dotConnect for Oracle tries to drop the sequence safely when dropping the table, and it ignores errors if there are no such sequence. If this property is set to false, the sequence is not deleted. If this property is set to true, dotConnect for Oracle attempts to drop the sequence, but it does not ignore errors.

Ideas for Future

We are considering the possibility to implement the following features in future:

• As for additional database-specific customizations of migrations:
  • Support database-specific customizations for MySQL, PostgreSQL and SQLite.
  • Extend the implementation for Oracle.
  • Implement setting some options with the global configuration options to avoid the necessity to specify them for all migrations.
• As for the Entity Framework Code-First Migrations support in Entity Developer:
  • The possibility to generate migrations with powerful and flexible Update To Database Wizard.
  • The possibility to specify database-specific customizations for entities/database objects in design time in Entity Developer without typing code.

Comment these suggestions and add your own ones in comments here or on our forum or on our UserVoice forum if you want these features to be implemented in the future versions of our products.