dotConnect for SQLite Documentation
In This Topic
    Support for Entity Framework Code-First Migration Operations
    In This Topic

    dotConnect for SQLite 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