.Net Core – Entity Framework Migration and Sqlite

In previous post, I showed you how to implement a custom migration without using Entity Framework for Sqlite database in .Net core. In this post, I’ll use Entity Framework Migration to manage (upgrade / downgrade) our database structure. Both of approaches will work, you can choose one of 2 solutions as you prefer.

1. Prerequisites

Please read the previous post first if you don’t know how to create a .Net core project. I’ll use a Console app for this demo. Let’s create yourself a .Net core app with this command

dotnet new -t console

2. Buiding simple Console app connecting with Sqlite database

2.1 AppSettings

For app using database I always want to customize the connection string to database out of my code so that I can change it easily when deployment. Therefore let’s add a appsettings.json into code folder

{
"ConnectionStrings": {
"DefaultConnection": "Data Source=WebApplication.db"
}
}

The section says that the app would like to define multiple connection strings. One of them is named as DefaultConnection and connects to a Sqlite database WebApplication.db.

2.2 CourseraContext

Next, I need a DbContext to reflect my model as well as my database structure. I will use the CourseraContext as same as in the previous post. However the context is much more compact than the previous one because it contains only the definitions of tables.

public class CourseraContext : DbContext
{
	public CourseraContext(DbContextOptions<CourseraContext> options)
		:base(options)
	{
		
	}
	
	public DbSet<Course> Courses { get; set; }
}

2.3 Migrations

After creating DbContext, I need to add migrations so that the database can be updated with the lastest model changes. Execute this command to add new migration.

dotnet ef migrations add {MigrationName}

Or remove the last added one

dotnet ef migrations remove

If you want to update database manually, you can run this command.

dotnet ef database update

To run those commands above, you’ll have to install Microsoft.EntityFrameworkCore.Tools.DotNet locally. So be sure that your project.json has dependency of Microsoft.EntityFrameworkCore.Tools.DotNet.

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true,
    "copyToOutput":{
      "includeFiles": ["appsettings.json"]
    }
  },
  "dependencies": {
    "Microsoft.EntityFrameworkCore.Tools.DotNet":"1.1.0-preview4-final",
    "Microsoft.EntityFrameworkCore.Design":"1.1.0",
    "Microsoft.EntityFrameworkCore.Sqlite":"1.1.1",
    "Microsoft.Extensions.Configuration.Abstractions":"1.1.1",
    "Microsoft.Extensions.Configuration":"1.1.1",
    "Microsoft.Extensions.Configuration.FileExtensions": "1.1.1",
    "Microsoft.Extensions.Configuration.Json":"1.1.1"
  },
  "tools": {
    "Microsoft.EntityFrameworkCore.Tools.DotNet":"1.1.0-preview4-final"
  },
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.1.0"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

I prefer to update database when starting the application. I’ll discuss in next section.

2.4 Startup

Now I have to connect the context with physical database file. Let’s create a Startup.cs file with following code

public class Startup
{

	public Startup()
	{            
		var configurationBuilder = new ConfigurationBuilder()
				   .SetBasePath(Directory.GetCurrentDirectory())
				   .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
		Configuration = configurationBuilder.Build();
	}

	public IConfigurationRoot Configuration { get; }

	public void ConfigureServices(IServiceCollection services)
	{           
		services.AddDbContext<CourseraContext>(options =>
			options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));
	}
}

This Startup class will load settings from appsettings.json and set the DefaultConnection as connection string for CourseraContext.

2.5 Program

Combine everything together, I can enable Entity Framework Migration for Sqlite database as following

static CourseraContext db;
public static void Main(string[] args)
{
	IServiceCollection services = new ServiceCollection();
	Startup startup = new Startup();
	startup.ConfigureServices(services);
	IServiceProvider serviceProvider = services.BuildServiceProvider();
	db = serviceProvider.GetService<CourseraContext>();
	db.Database.Migrate();

	int option = -1;
	while (option != 0)
	{
		Console.WriteLine("Choose an option (0, 1, 2, 3, 4):" + Environment.NewLine +
			"1. Enter a course" + Environment.NewLine +
			"2. Enter a student" + Environment.NewLine +
			"3. List all courses" + Environment.NewLine +
			"4. List all students" + Environment.NewLine +
			"0. Exit" + Environment.NewLine);
		if (int.TryParse(Console.ReadLine(), out option))
		{
			switch (option)
			{
				case 1:
					EnterCourse();
					break;

				case 2:
					EnterStudent();
					break;

				case 3:
					ListAllCourses();
					break;

				case 4:
					ListAllStudents();
					break;
			}
		}
	}

}

3. Source code

It’s pretty simple to use Entity Framework Migration within .Net core project. In compare to my custom one, the EF Migration can create himself a Sqlite database without using any template. Meanwhile my custom one need to use a template file because my code can’t create one. Which one you prefer is your choice.

Source code: https://bitbucket.org/hintdesk/dotnetcore-entity-framework-migration-and-sqlite

Leave a Reply

Your email address will not be published. Required fields are marked *