EF Core Migrations

In my Getting Started with Entity Framework Core and SQLite article I wrote a simple .NET Core Application that performs CRUD operations on a SQLite Database. Rather than using EF Core Migrations to create migration files to create and update the database, I instead opted to perform these operations in the code using various methods on my custom DbContext Class, called SqliteDbContext. The code is the following:

using (var context = new SqliteDbContext()) {

    // Start with a clean database
    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();

    ...
}

Although entirely acceptable for my trivial example, most applications probably don't want to drop and re-create the database through code. Once you have a database and it has data you typically want to keep it around for awhile.

An alternative to creating the SQLite database in code is to use migrations in EF Core. EF Core Migrations give you the ability to make changes to the database in a more controlled and safe manner. I will walk through an example now.

EF Core and SQLite Tutorial

Here is the source code for the Getting Started with Entity Framework and SQLite tutorial without the code to drop and create the database. I am going to use EF Core Migrations for that. I bunched everything into a single file to keep it simple.

using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace DatabaseApplication {
    public class Reminder {
        [Key]
        public int Id { get; set;}
        [Required]
        public string Title { get; set;}
    }

    public class SqliteDbContext :  DbContext {
        public DbSet Reminders { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
            optionsBuilder.UseSqlite("Filename=./Reminders.sqlite");
        }
    }

    public class Program {
        public static void Main(string[] args) {
            using (var context = new SqliteDbContext()) {
                // Add reminders.
                context.Reminders.Add(new Reminder {
                    Title = "Meditate"
                });
                context.Reminders.Add(new Reminder {
                    Title = "Eat a nutritious breakfast"
                });
                context.SaveChanges();

                // Fetch Reminders
                var reminders = context.Reminders.ToArray();
                foreach(var reminder in reminders) {
                    Console.WriteLine($"{reminder.Title}");
                }

                // Remove a reminder
                context.Database.ExecuteSqlCommand(
                    "DELETE FROM Reminders WHERE Title = {0}", "Meditate");

                // Fetch Reminders
                var reminderz = context.Reminders.ToArray();
                foreach(var reminder in reminderz) {
                    Console.WriteLine($"{reminder.Title}");
                }
            }
        }
    }
}

Microsoft.EntityFrameworkCore.Tools

In order for the code to work, I need a SQLite database, called Reminders.sqlite. To create this database using migrations I need to add a couple of dependencies to my project: Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.Design. The new project.json file for the project looks like the following:

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "dependencies": {
    "Microsoft.EntityFrameworkCore.Design": "1.0.0-*",
    "Microsoft.EntityFrameworkCore.Sqlite": "1.0.0"
  },
  "tools": {
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-*"
  },
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.0"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

Either install the Nuget Packages using dotnet restore or choose 'yes' when Visual Studio Code detects the changes and missing dependencies and prompts you to install the dependencies for Microsoft.EntityFrameworkCore.Design and Microsoft.EntityFrameworkCore.Tools.

As an FYI, all my code for ASP.NET Core and EF Core thus far has been using Visual Studio Code and macOS. If you were to build this example in Visual Studio you will probably have a huge project.json file.

EF Core Migrations

The custom Reminder Class decorated with Data Annotations and custom DbContext Class, called SqliteDbContext, tell the Entity Framework Core Tools everything they need to know to properly create the Reminders.sqlite SQLite Database and Reminders Table. I just need to run the proper commands to do it. Using CTRL-`, I open up the terminal window in Visual Studio Code and execute the following command.

dotnet ef migrations add InitialMigration

By default this creates a new Migrations directory in the project and the following files:

{timestamp}_InitialMigration.cs
{timestamp}_InitialMigration.Designer.cs
SqliteDbContextModelSnapshot.cs

If you look in the InitalMigration.cs File you will see code to perform the creation of the Reminders Table during EF Core Migrations as well as deletion of the table in case you want to rollback those changes.

public partial class InitialMigration : Migration {
    protected override void Up(MigrationBuilder migrationBuilder) {
        migrationBuilder.CreateTable(
            name: "Reminders",
            columns: table => new {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Autoincrement", true),
                Title = table.Column<string>(nullable: false)
            },
            constraints: table => {
                table.PrimaryKey("PK_Reminders", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder) {
        migrationBuilder.DropTable(
            name: "Reminders");
    }
}

Not all databases support all rollbacks, however. In fact, you can find a list of SQLite limitations in the EF Core Documentation.

Now that the InitialMigration has been created and verified, I can run the migration by executing the database update command:

dotnet ef database update

This creates a new Reminders.sqlite SQLite Database and an empty Reminders Table. EF Core Migrations also added its own table, called _EFMigrationsHistory, to manage migrations.

Now that a SQLite database exists, the .NET Core Application will run accordingly, adding, deleting, and fetching reminders from the SQLite Database.

Where to Go From Here

EF Core Migrations isn't just about creating the initial database and tables, however. One can use migrations to manage the complete lifecycle of the database as one makes changes to it. I will cover that in future articles, but I recommend looking at the help for migrations at the command line to see the options.

dotnet ef migrations --help

Usage: dotnet ef migrations [options] [command]
Options:
  -h|--help     Show help information
  -v|--verbose  Enable verbose output
Commands:
  add     Add a new migration
  list    List the migrations
  remove  Remove the last migration
  script  Generate a SQL script from migrations

I also recommend reading the EF Core Documentation.

I can be found on twitter as @KoderDojo.

Best wishes to you!

Related Posts: