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
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!