Limitations

SQL Server Compact Limitations

When using the SQL Server Compact provider, there are a few limitations you should be aware of. Most of these are a result of limitations in the underlying database engine and are not specific to EF Core.

Modeling Limitations

The common relational library (shared by Entity Framework relational database providers) defines APIs for modelling concepts that are common to most relational database engines. A couple of these concepts are not supported by the SQL Server Compact provider.

• Schemas
• Sequences

Migrations Limitations

The SQL Server Compact database engine does not support a few schema operations that are supported by the majority of other relational databases. As you can see, the only relevant affected migration operation is RenameColumn. If you attempt to apply one of the unsupported operations to a SQL Server Compact database then a NotSupportedException will be thrown.

Operation	Supported?
AddColumn	✔
AddForeignKey	✔
AddPrimaryKey	✔
AddUniqueConstraint	✔
AlterColumn	✔
AlterSequence	✗
CreateIndex	✔
CreateSchema	✗
CreateSequence	✗
CreateTable	✔
DropColumn	✔
DropForeignKey	✔
DropIndex	✔
DropPrimaryKey	✔
DropSchema	✗
DropSequence	✗
DropTable	✔
DropUniqueConstraint	✔
RenameColumn	✗
RenameIndex	✔
RenameSequence	✗
RenameTable	✔
RestartSequence	✗

Tip

You can workaround some of these limitations by manually writing code in your migrations to perform a table rebuild. A table rebuild involves renaming the existing table, creating a new table, copying data to the new table, and dropping the old table. You will need to use the Sql(string) method to perform some of these steps.

IsUnique Limitations

Uniqueness on a nullable column with the SQL Server Compact provider means: A single row with the value NULL, and unique non-null values.

(Unlike the SQL Server, SQLite and progress provider, where unique means multiple rows with NULL values and unique non-null values)

This also means that uniqueness is not enforced for nullable foreign key columns