Welcome to today’s blog.
In this post I will show you show to make changes to SQL database schema using Entity Framework Core within your .NET Core application.
Before covering the procedures for changes to Entity Framework schemas, I will give an overview of how Entity Framework Core tracks and applies changes to a SQL database.
To understand how Entity Framework Core is installed and setup within a .NET Core application, you can refer to my previous post, where I introduced the use of Entity Framework Core within a .NET Core application.
How Entity Framework Core Tracks Database Changes
Each table in your schema is defined in the application data context class.
Below you see an example of a data context defining a data schema:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using BookLoan.Models;
namespace BookLoan.Data
{
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext (DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<BookViewModel>().ToTable("Books");
builder.Entity<ReviewViewModel>().ToTable("Reviews");
builder.Entity<LoanViewModel>().ToTable("Loans");
builder.Entity<ReviewViewModel>().
HasOne(p => p.Book).
WithMany(q => q.Reviews).
HasForeignKey(p => p.BookID).HasConstraintName("FK_Book_Review");
builder.Entity<LoanViewModel>().
HasOne(p => p.Book).
WithMany(q => q.Loans).
HasForeignKey(p => p.BookID).HasConstraintName("FK_Book_Loan");
}
public DbSet<BookViewModel> Books { get; set; }
public DbSet<LoanViewModel> Loans { get; set; }
public DbSet<ReviewViewModel> Reviews { get; set; }
}
}
Any customizations to the schema (tables, keys, constraints) are defined within the overridden method:
OnModelCreating()
Each table has an associated Model definition, which defines a table schema. When the initial data context is migrated, Entity Framework applies the definition of each model to create tables within the SQL database.
An example of a model is shown below:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace BookLoan.Models
{
public class LoanViewModel
{
public int ID { get; set; }
public string LoanedBy { get; set; }
public DateTime DateLoaned { get; set; }
public DateTime DateReturn { get; set; }
public bool OnShelf { get; set; }
public DateTime DateCreated { get; set; }
public DateTime DateUpdated { get; set; }
public int BookID { get; set; }
public BookViewModel Book { get; set; }
}
}
In the next section I will show how changes to the above model classes can be applied to generate migration classes, which can then be subsequently applied to migrate changes to the underlying SQL database.
Data Changes Tracked Through Migration Classes
When a change is made to one of our models, the equivalent schema change is generated as an Entity Framework Core migration class as shown under the \Data\Migrations\ folder:
An example of one of the migration classes is shown below:
using Microsoft.EntityFrameworkCore.Migrations;
using System;
using System.Collections.Generic;
namespace BookLoan.Data.Migrations
{
public partial class add_bookloan_newfields_12_11_2019 : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AddColumn<DateTime>(
name: "DateDue",
table: "Loans",
nullable: false,
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(
name: "DateDue",
table: "Loans");
}
}
}
When a migration change is initially generated, the underlying database has not yet had the changes applied to it. The migration changes are staging the changes to our database. When the migrations are executed against the database, the changes to the schema will then be applied.
I will show how a basic schema change can be applied using Entity Framework Core:
Applying Schema Changes to Migration Classes
From an existing model, it is straightforward; we add an additional field DateDue to the model as shown:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace BookLoan.Models
{
public class LoanViewModel
{
public int ID { get; set; }
public string LoanedBy { get; set; }
public DateTime DateLoaned { get; set; }
public DateTime DateDue { get; set; }
public DateTime DateReturn { get; set; }
public bool OnShelf { get; set; }
public DateTime DateCreated { get; set; }
public DateTime DateUpdated { get; set; }
public int BookID { get; set; }
public BookViewModel Book { get; set; }
}
}
Save the change.
Rebuild. Open the package manager console (which is PowerShell):
Entered and ran the following command:
Add-Migration add_bookloan_newfields_12_11_2019
What the above add-migration Entity Framework Core command did was to add the migration class file as shown:
Also, the data context model snapshot class will have been updated with any new schema definitions:
namespace BookLoan.Data.Migrations
{
[DbContext(typeof(ApplicationDbContext))]
partial class ApplicationDbContextModelSnapshot : ModelSnapshot
{
protected override void BuildModel(
ModelBuilder modelBuilder)
{
#pragma warning disable 612, 618
modelBuilder
.HasAnnotation("ProductVersion",
"2.0.3-rtm-10026")
.HasAnnotation( "SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);
..
..
modelBuilder.Entity
("BookLoan.Models.LoanViewModel", b =>
{
b.Property<int>("ID")
.ValueGeneratedOnAdd();
..
..
b.Property<DateTime>("DateDue");
..
b.HasKey("ID");
b.HasIndex("BookID");
b.ToTable("Loans");
});
#pragma warning restore 612, 618
}
}
}
Rolling Back a Migration
Note: Before applying the update to the database, the above migration can be reversed by running the command:
Remove-Migration
After a migration has been removed, you can amend your model(s) and re-apply the migration.
Note: After a migration is applied to database, the remove-migration command cannot rollback the change! You can apply a rollback to a previous migration by using the command:
Update-Database LastMigration
Where LastMigration is your last successful migration.
What this will do is reverse your schema changes (and __EFMigrationHistory changes) applying the Down() method of each migration back to migration following the LastMigration migration.
Rolling Back a Migration
To apply the migration to the database, we ran the following command:
Update-database
What this does is to generate the equivalent SQL DDL and apply it to the target database.
The __EFMigrationsHistory is used by Entity Framework to keep track of schema changes to the database and determining if a migration has already been applied.
Also, a record showing the migration (if successful) is inserted:
Generation of a SQL Deployment Script
To generate a script that can be applied directly to the database offline or through a deployment pipeline, you can use the following command:
Script-Migration -Idempotent -Output [output file name]
The Idempotent switch will generate a script that can be run to bring any database schema up to date with the last migration. Entries in the script will be of the form:
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20191112075920_add_bookloan_newfields_12_11_2019')
BEGIN
ALTER TABLE [Loans] ADD [DateDue] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.000';
END;
GO
The above commands have shown you how to make changes to Entity Framework database schemas within .NET Core to generate C# migration classes, then apply the changes to these migration classes to the SQL database with migration updates using PowerShell commands.
That’s all for today’s post.
I hope you found it useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
