Support for Entity Framework Code-First Migration Operations

In This Topic

dotConnect for DB2 implements support for Entity Framework Code-First Migrations. 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.

Table operations

Operation	Oracle	MySQL	PostgreSQL	SQLite	DB2
CreateTable
DropTable
RenameTable
MoveTable

Column operations

Operation	Oracle	SQLite
AddColumn
AlterColumn	/*	/**
DropColumn		/**
RenameColumn		/**

* 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). Some operations in dotConnect for SQLite are implemented via re-creating a table and copying data from an old table to the new one.

Constraint operations

Operation	Oracle	MySQL	PostgreSQL	SQLite	DB2
AddPrimaryKey				/*
DropPrimaryKey				/*
AddForeignKey				/**
DropForeignKey				/*

* The ALTER TABLE statement is very limited in SQLite (see here). Some operations in dotConnect for SQLite are implemented via re-creating a table and copying data from an old table to the new one.

** The ADD FOREIGN KEY constraint operation for a foreign key, consisting of a single column, is supported in two cases: when a new column for the foreign key is created in the same migration, or when the whole table is created in the same migration.

Index operations

Operation	Oracle	MySQL	PostgreSQL	SQLite	DB2
CreateIndex
DropIndex
RenameIndex*		/**
AddUniqueConstraint***
DropUniqueConstraint***

* This operation is available only in Entity Framework 6 and Entity Framework Core.

** 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.

*** This operation is available only in Entity Framework Core.

Sequence Operations

Operation	Oracle	MySQL	PostgreSQL	SQLite	DB2
CreateSequence*
AlterSequence*
RestartSequence*
DropSequence*

* This operation is available only in Entity Framework Core.

SQL Operations

Operation	Oracle	MySQL	PostgreSQL	SQLite	DB2
SQL

Data Manipulation Operations

Operation	Oracle	MySQL	PostgreSQL	SQLite	DB2
InsertData*
UpdateData*
DeleteData*

* This operation is available only in Entity Framework Core 2.0.

Schema Operations*

Operation	Oracle	MySQL	PostgreSQL	SQLite	DB2
EnsureSchema**	***		****	***	***
DropSchema**

* As an alternative to these operations, you may use the approach with Database.EnsureCreated()/Database.EnsureDeleted() and behavior configuration using the config.DatabaseScript.Schema.DeleteDatabaseBehaviour option.

** This operation is available only in Entity Framework Core.

*** The operation is simply ignored, no exception is thrown.

**** Behavior of the EnsureSchema operation can be configured with the config.DatabaseScript.Schema.EnsureSchemaBehaviour option.