If (Not )Exists checks in migrations

Required Nuget Package:

Usage

SQL Server

Register ThinktectureSqlServerMigrationsSqlGenerator with EF.

var services = new ServiceCollection()
   .AddDbContext<DemoDbContext>(builder => builder
                                           ...
                                           .UseSqlServer("conn-string", sqlOptions =>
                                                                                    {
                                                                                       sqlOptions.UseThinktectureSqlServerMigrationsSqlGenerator();
                                                                                    })

PostgreSQL

Register ThinktectureNpgsqlMigrationsSqlGenerator with EF.

var services = new ServiceCollection()
   .AddDbContext<DemoDbContext>(builder => builder
                                           ...
                                           .UseNpgsql("conn-string", npgsqlOptions =>
                                                                                    {
                                                                                       npgsqlOptions.UseThinktectureNpgsqlMigrationsSqlGenerator();
                                                                                    })

Use IfExists / IfNotExists

Use IfExists or IfNotExists to add checks to operations.

migrationBuilder.CreateIndex("IX_OrderItems_ProductId", "OrderItems", "ProductId").IfNotExists();

Supported operations

• CreateTable - IfNotExists
• DropTable - IfExists
• AddColumn - IfNotExists
• DropColumn - IfExists
• CreateIndex - IfNotExists
• DropIndex - IfExists
• AddUniqueConstraint - IfNotExists
• DropUniqueConstraint - IfExists

Remarks

On SQL Server, the implementation of IMigrationsSqlGenerator will be replaced by ThinktectureSqlServerMigrationsSqlGenerator (derives from SqlServerMigrationsSqlGenerator).

On PostgreSQL, the implementation uses ThinktectureNpgsqlMigrationsSqlGenerator (derives from NpgsqlMigrationsSqlGenerator). Because PostgreSQL does not offer IF [NOT] EXISTS for every DDL operation (e.g. ADD CONSTRAINT), each operation is wrapped in a PL/pgSQL block that performs an explicit catalog existence check before running the DDL, e.g.:

DO $$
BEGIN
   IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'Products') THEN
      CREATE TABLE ...;
   END IF;
END
$$;

The checks query information_schema.tables, information_schema.columns, information_schema.table_constraints, and pg_indexes depending on the operation.