Setting Up SQLite and Entity Framework Core in ASP.Net Core

For small applications such as prototypes and small side projects, SQLite is a great choice for a relational database. It is really easy to setup because:

Its self-contained

Its just one database file in your project.

No server

Because its just one database file in your project which holds all your data there is no extra server setup.

No configuration

All you need is to create your schema and the database is ready to store data.

Letting Entity Framework Core Manage Your Database Schema

For all its ease to setup, you still need a schema to define your tables and the relationships between them. Unless your a SQL expert this goes against being able to quickly spin up and database for your ASP.Net Core application – which is one of the main reasons to use SQLite! But its really easy to setup Entity Framework Core to do this for you.

Updating the project.json File to Gather the Needed Dependencies

The first step is to update your project.json dependencies to get some nuget packages that will be used. These are:

Create a DBContext for your Database

Add Entity Framework to the Application in ConfigureServices in Startup.cs

The final part of the setup is to let ASP.NET core know you want to use EF Core with SQLite you can do this by adding it as a service to ConfigureServices in Startup.cs with your context passed in as a generic type:

The code is conceptually straightforward it defines where the SQLite database file is on the file system and passes that information to EF when it is added to the services the ASP.NET application consumes.

PlatformServices only Needed When Compiling outside Windows

In this example, it uses PlatformServices to get the application base path as the code is being written to compile using .NET Core on OSX and Windows. Otherwise, use:

System.Environment.CurrentDirectory

Create and Run an Initial Migration

To create a migration in the command line type:

dnx ef migrations add Initial

Where Initial is the name of your migration. Once the migration has been created you can run your migration by typing:

dnx ef database update

Summary

If all has gone well you will now have an SQLite database managed by EF Core which means you can handle or future changes by updating your models / DB Context and using migrations to apply them to the database in a similar way to how the Initial migration was created.