Entity Framework Core Sqlite

Entity Framework is a framework abstracting away all the complexity of dealing with storage. This abstraction is also known as ORM ~ object-relational mapping.
There is a number of provider which are implementation of the storage like SQL server or MySql or also SQLite, the one we will be seeing in this post.
SQLite is a embedded database. The whole database is contained within a single .db file which makes it highly portable, so portable that it is the default database installed in mobile OS like iOS and Android. It is extremely easy to use and to maintain. It also offer a powerful implementation of SQL. Today we will see how we can make use of Entity Framework with SQLite provider in a ASP.NET Core application.

Install EF and create a new DbContext

Create migrations

Use in ASP NET Core

1. Install EF and create a new DbContext

Start by installing the packages:

1

Install-Package Microsoft.EntityFrameworkCore.SQLite

Once we have installed EF Core SQLite, we will create our first DbContext:

Also installing EF Core SQLite gives us access to the .UseSqlite extension on the DbContext builder which we can register by overriding the OnConfiguring function of the DbContext.
With our first Person model:

Now that we have configured our first DbContext, what we need to do is to create the database.

2. Create migrations

While we could create our database manually, EF provides a set of tools which can be used to create migrations via the dotnet CLI.
Migrations are a big advantage of EF, in the event of us having to change the database after data have already been added, we will be in measure to use the migration to automate the process.

This way of developing ~ creating the object model, generating migrations out of the object model, creating database by running migrations ~ is also known as Code first design.

In order to generate the migration code we will start by configuring the EF tools. An example of command is:

1

dotnet ef migrations add InitialMigration

If we run that we will get the error No executable found matching the command dotnet-ef. What we need is to configure the project to use dotnet ef.
Start by adding the EF design library:

1

Microsoft.EntityFrameworkCore.Design

The library is needed to use the tools otherwise it will throw an exception System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.EntityFrameworkCore.Design, Culture=neutral, PublicKeyToken=null'.
Then add the EF tools as a DotNetCliToolReference in the .csproj as followed to make the dotnet ef command line available:

After accessing the endpoint, we should be able to see the value added to our database using the SQLite compact toolbox:

Conclusion

Today we saw how to install and configure Entity Framework with a SQLite storage. Entity Framework makes it very handy to work in OOP and making the solution storage agnostic. SQLite as a backup storage makes the solution very portable and easy to maintain thanks to its small size and its embedded nature. Hope you enjoyed this post as much as I enjoyed writing it! If you have any question, leave it here or hit me on Twitter @Kimserey_Lam. See you next time!