scripts.sql

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200521183957_InitialMigration')
BEGIN
    IF SCHEMA_ID(N'EfCore') IS NULL EXEC(N'CREATE SCHEMA [EfCore];');
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200521183957_InitialMigration')
BEGIN
    CREATE TABLE [EfCore].[Customers] (
        [Id] int NOT NULL IDENTITY,
        [FirstName] nvarchar(max) NULL,
        [LastName] nvarchar(max) NULL,
        [AddressLine1] nvarchar(max) NULL,
        [AddressLine2] nvarchar(max) NULL,
        [Postcode] nvarchar(max) NULL,
        CONSTRAINT [PK_Customers] PRIMARY KEY ([Id])
    );
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200521183957_InitialMigration')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20200521183957_InitialMigration', N'3.1.3');
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200521184035_CustomerAddressAndOrders')
BEGIN
    CREATE TABLE [EfCore].[CustomerOrders] (
        [Id] int NOT NULL IDENTITY,
        [OrderDate] datetime2 NOT NULL,
        [CustomerId] int NULL,
        CONSTRAINT [PK_CustomerOrders] PRIMARY KEY ([Id]),
        CONSTRAINT [FK_CustomerOrders_Customers_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [EfCore].[Customers] ([Id]) ON DELETE NO ACTION
    );
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200521184035_CustomerAddressAndOrders')
BEGIN
    CREATE INDEX [IX_CustomerOrders_CustomerId] ON [EfCore].[CustomerOrders] ([CustomerId]);
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200521184035_CustomerAddressAndOrders')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20200521184035_CustomerAddressAndOrders', N'3.1.3');
END;

GO