concept database schema in category entity framework

This is an excerpt from Manning's book Entity Framework Core in Action. Login to get full access to this book.

Figure 11.7 The steps in changing a database schema that requires data to be copied to ensure no loss of data. In this case, the application software is stopped while the database schema change is applied and data is copied to the new Addresses table. When that has finished, the new application software can start.

You can update the database schema and copy the data in two ways:

Listing 11.10 SQL change script to change the database schema and retain the data

EXEC sp_rename N'CustomerAndAddresses', N'Customers';    #1  
GO
CREATE TABLE [Addresses] (    #2  
    [Id] int NOT NULL IDENTITY,
    [Address] nvarchar(max),
    [CustFK] int NOT NULL,
    CONSTRAINT [PK_Addresses] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Addresses_Customers_CustFK] 
         FOREIGN KEY ([CustFK]) 
         REFERENCES [Customers] ([Id]) 
         ON DELETE CASCADE
);
GO
CREATE UNIQUE INDEX [IX_Addresses_CustFK]    #3  
   ON [Addresses] ([CustFK]);
GO
INSERT INTO [dbo].[Addresses]    #4  
   ([Address], [CustFK])
   SELECT Address, Id
   FROM [dbo].[Customers];
GO
ALTER TABLE [Customers] DROP COLUMN [Address];    #5  
GO

#1   Renames the CustomerAndAddresses table to Customers now, as you want the Addresses table to have a foreign-key relationship to the Customers table
#2   Creates the new Addresses table …
#3   … with a unique index for the foreign key
#4   Copies the Address part of the renamed CustomerAndAddresses table to the Addresses table
#5   Drops the Address column from the Customers table, as the data has been copied

copy

For breaking changes like this example, you need to stop the old version of software, apply your SQL change script, and then deploy/start your new application. That way, the database schema will be in the correct state for each version. The disadvantage is that your application is down (not responding to users) for longer than the EF Core’s migrations approach, but it’s easier to debug if it goes wrong.