dotConnect for MySQL Documentation
Support for Entity Framework Code-First Migration Operations

dotConnect for MySQL 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 Yes Yes Yes Yes Yes
DropTable Yes Yes Yes Yes Yes
RenameTable Yes Yes Yes Yes Yes
MoveTable No No No No No


Column operations


Operation Oracle MySQL PostgreSQL SQLite   DB2   
AddColumn Yes Yes Yes Yes Yes
AlterColumn Yes/No* Yes Yes Yes/No** Yes
DropColumn Yes Yes Yes Yes/No** Yes
RenameColumn Yes Yes Yes Yes/No** Yes

* 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 Yes Yes Yes Yes/No* Yes
DropPrimaryKey Yes Yes Yes Yes/No* Yes
AddForeignKey Yes Yes Yes Yes/No** Yes
DropForeignKey Yes Yes Yes Yes/No* Yes

* 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 Yes Yes Yes Yes Yes
DropIndex Yes Yes Yes Yes Yes
RenameIndex* Yes Yes/No** Yes No Yes
AddUniqueConstraint*** Yes Yes Yes Yes Yes
DropUniqueConstraint*** Yes Yes Yes Yes Yes

* 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* Yes No Yes No Yes
AlterSequence* Yes No Yes No Yes
RestartSequence* No No Yes No Yes
DropSequence* Yes No Yes No Yes

* This operation is available only in Entity Framework Core.



SQL Operations


Operation Oracle MySQL PostgreSQL SQLite   DB2   
SQL Yes Yes Yes Yes Yes


Data Manipulation Operations


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

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



Schema Operations*


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

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



See Also

Entity Framework Support |  Entity Framework Tutorial