Skip to content

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

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:

System.Environment.CurrentDirectory

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

Summary

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.

Published in.NET CoreC#Entity Framework

Be First to Comment

Leave a Reply

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