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 your 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:
- The code EF will use to connect to SQLite
- The common EF commands. For example running and creating migrations.
- Only needed if you plan to compile your code on non windows platforms (in my case a Mac)
Add a EF command to the Commands Object
If you haven’t already ensure you have a ef command listed in your commands object – this is used when running entity framework commands via a command prompt.
Create Models to Represent Tables in the Database
This isn’t specific to using SQLite with EF. But for the sake of completeness create a model class for each table that will be in the database. The EF Core Documentation has an example.
Create a DBContext for your Database
Again this isn’t specific but create a DBContext which represents the properties and relationships of the tables in the database. Again the EF Core Documentation has an example.
Add Entity Framework to the Application in ConfigureServices in Startup.cs
The final part of 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:
Create and Run a 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
If all has gone well you will now have a 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.