I have written a few tutorials on Entity Framework already, but I’ve always used SQL Server. The reason is simple: It’s easy to use. Then I got a few questions and requests on how to use SQLite with entity framework in a C# project.
In this tutorial, I am going to explain and show you how SQLite works with Entity Framework Core. Starting from an existing project that uses SQL Server and rewriting it so it uses SQLite.
To be honest: I have never done this before and I must say it’s pretty easy. It’s just another package and a different connection string.
Table of contents
Goals
This article is about SQLite and Entity Framework. I am not going to explain the basics of Entity Framework. If you are looking for a starter tutorial about Entity Framework, you can take a look at this article.
After this article you:
- Know what SQLite is.
- Change an existing application, that uses SQL Server, and make it use SQLite.
The boring theory
Before I am going to show you cool code, let’s take a moment to think about SQLite. What is it and why do we use it?
What is SQLite?
SQLite is a free and open-source software library that provides a relational database management system (RDBMS). It’s not a client-server database engine like SQL Server. SQLite is an embedded database engine that is self-contained and operates within the application. This means that the entire database resides in a single file on the disk, making it easy to move, copy, or backup.
SQLite’s reliability and consistency, even in the presence of system failures, works well and supports most of the SQL language. It is widely used in various software applications as an embedded database engine because of its small size, low memory footprint, and portability across multiple platforms, including Windows, Linux, macOS, and mobile operating systems such as Android and iOS. SQLite is popular among developers working with mobile or desktop applications and for small-scale web applications that don’t require complex relational databases.
Pros and cons
As to why we use it there are several things I can think of. First of all, it’s easy to set up. Especially when you use Entity Framework. It is the same setup and configuration as when you use SQL Server; there are entities, a data context, repository if you want. There isn’t much difference between those.
Another reason is that SQLite is lightweight. It requires minimal system resources. This makes it perfect for local development and runs on systems that don’t have many resources.
Despite its lightweight, it also has a great performance. You can use it for heavy-load data transactions. So if you are working on a project with a lot of data, SQLite might be a good choice.
SQLite is also cross-platform. Most of the software we use and create is cross-platform, but it’s still worth mentioning.
The integration of SQLite is getting better with time. Before Entity Framework using SQLite was a pain. But now, with Entity Framework, it works great. Especially the implementation.
And then we have the reasons why should not use it. SQLite is not built for concurrent write operations. This could even result in poor performance. If there are too many users writing to the database your application could run into problems.
There are no stored procedures and triggers. This can limit the functionality of your database. But if I am honest? I haven’t used stored procedures and triggers in a long time. But maybe you have.
Although SQLite is fast and lightweight, don’t expect it to handle really complex queries. If you expect your application to use them, change to another database engine.
It’s not built for distributed architectures. If you use load balancers and redundancies, you might not want to choose SQLite.
No user management system. While we can use SQL Management Studio, Azure Data Studio, and Visual Studio to inspect our database, SQLite doesn’t have such a tool. Yes, there are a few, like the DB Browser for SQLite, but they are not official.
When do we use SQLite with Entity Framework?
There are several reasons to use SQLite. For a small project for example. Projects that don’t require a big database. Or projects that don’t need to use a database with a lot of power and which is online.
If you are creating mobile apps, SQLite could be a very good candidate to store local data in a structured way. You can store everything you want and mobile apps don’t require big databases.
When you want to create a website that receives a lot of visitors each hour or you have a lot of work going from and to your SQLite database, I would advise switching to SQL Server or something. SQLite is not made for this.
So, use it when you can keep it simple.
Alright, let’s implement SQLite in a C# application!
Preparations
There are a few things I want to show you in this tutorial:
- How to implement SQLite
- How to use Entity Framework with SQLite
- Take an existing application that uses an SQL server and make it use SQLite with less effort.
To combine all these I created a start project. You can find it on my GitHub. Let’s walk through it first.
There are 3 folders. The first folder is called Data. This contains two class libraries:
MoveDatabase.Data.Domain
This project contains the entities and interfaces that can be shared in other libraries. The folder Entities contains one entity and the folder Interfaces contains one interface. These two elements are really important to separate the different data sources.MovieDatabase.Data.SQLServer
This library is all about connecting to a SQL server. The DataContext inherits from the DbContext and uses and configures everything for Entity Framework with a Microsoft SQL server.
This project also has a Repository that handles all basic calls from and to the DataContext.
The Business folder has just one project: MovieDatabase.Business. It contains all the logic needed to handle the movies. There is an Interfaces folder with one interface, a Models folder with a filter model, and a MovieService.cs.
The MovieService has all kinds of logic for the CRUD. This class is also being tested with unit tests in the solution folder Tests. The class gets an injection of the IRepository, which is connected to the implementation in MovieDatabase.Data.SQLServer. This is configured in MovieDatabase.API.
The last one is MovieDatabase.API. It’s a minimal API with some endpoints. Nothing special here, just something we can test the logic with real data. The Program.cs contains the mappings and configuration. This is also where we connect IRepository with the Repository of MovieDatabase.Data.SQLServer.
Ideally, we don’t need to change anything to the tests and business logic when we implement SQLite.
Install and use SQLite
Using SQLite is pretty easy to realize, but it does handle migrations a bit differently than SQL Server. I don’t want to delete my current migrations since I might want to use them again. Instead, I create a new data project that will handle all SQLite issues.
The first thing we are going to do is prepare the API with some configuration. We need to change the connection string, add a package, and change the dependency injection so it points to the correct implementation.
Install NuGet package
When you want to use an SQL server with Entity Framework, you need to install Microsoft.EntityFrameworkCore.SqlServer. The same goes for SQLite. To make Entity Framework be aware of SQLite and ‘learn’ how to use the data, you need to install Microsoft.EntityFrameworkCore.Sqlite. Let’s install this package in the API:
install-package Microsoft.EntityFrameworkCore.Sqlite
After installation, you have both the SQL server and the SQLite variant, which is fine.
Change the connection string
We can’t reuse the connection string used for the SQL server, because it’s different from the SQLite one. Let’s open the appsettings.Development.json and add another connection string:
"ConnectionStrings": {
"Default": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Movies;Integrated Security=True;Connect Timeout=30;Encrypt=False;",
"SQLiteDefault": "Data Source=LocalMovieDatabase.db"
}
New data library
Now we want to reconfigure the IRepository. In the API Program.cs the IRepository points to the implementation of the MovieDatabase.Data.SQLServer. We want to change that to MovieDatabase.Data.SQLite, but we don’t have that one yet.
We create a new class library in the folder “Data”. I will name that library MovieDatabase.Data.SQLite. I also added a new class with the name DataContext and inherited DbContext. Yes, the is pretty much the same as the DataContext from MovieDatabase.Data.SQLServer. In fact, you can just copy-paste it, but make sure the namespace is correct.
We also need a reference to MovieDatabase.Data.Domain so the new library has access to the entities and interfaces.
Copy-paste the Repository from the MovieDatabase.Data.SQLServer to the new project MovieDatabase.Data.SQLite. Again, be aware of the namespace. It should be namespace MovieDatabase.Data.SQLite.
Reconfigure the API
Back to the Program.cs of the API. We need to use the new connection string and let the API know we want to use SQLite and that the implementation of the IRepository is somewhere else.
First, the AddDbContext service. It’s now set to use SQL Server, we changed that to SQLite. We also set it to use the other connection string:
builder.Services.AddDbContext<DataContext>(options =>
options.UseSqlite(builder.Configuration["ConnectionStrings:SQLiteDefault"]),
ServiceLifetime.Scoped);
Find the line where the IRepository is connected to the implementation:
builder.Services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
Add a reference to the project MovieDatabase.Data.SQLite. There are two ways to reconfigure the API to use another implementation of the IRepository. We could remove the reference to the MovieDatabase.Data.SQLServer or we could spell out the whole namespace to the SQLite Repository. Here are both options:
builder.Services.AddScoped(typeof(IRepository<>), typeof(MovieDatabase.Data.SQLite.Repository<>));
// Or
builder.Services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
Personally, I like the second one. Simply because you can remove a reference to another project and the code looks a bit cleaner. You do need to add the correct using to your class.
Migrations
After all the above you can create the migrations and update the database.
PowerShell
add-migration Initial
update-database
It can be fun to take a quick look at the differences between the SQLServer and SQLite migration.
SQLite:
namespace MovieDatabase.Data.SQLite.Migrations
{
[DbContext(typeof(DataContext))]
[Migration("20230218143401_Initial")]
partial class Initial
{
/// <inheritdoc />
protected override void BuildTargetModel(ModelBuilder modelBuilder)
{
#pragma warning disable 612, 618
modelBuilder.HasAnnotation("ProductVersion", "7.0.3");
modelBuilder.Entity("MovieDatabase.Data.Domain.Entities.Movie", b =>
{
b.Property<int>("Id")
.ValueGeneratedOnAdd()
.HasColumnType("INTEGER");
b.Property<int>("Rating")
.HasColumnType("INTEGER");
b.Property<bool>("Seen")
.HasColumnType("INTEGER");
b.Property<string>("Title")
.IsRequired()
.HasColumnType("TEXT");
b.HasKey("Id");
b.ToTable("Movies");
b.HasData(
new
{
Id = 1,
Rating = 0,
Seen = false,
Title = "Shrek"
},
new
{
Id = 2,
Rating = 1,
Seen = true,
Title = "Inception"
},
new
{
Id = 3,
Rating = 2,
Seen = true,
Title = "The Green Latern"
},
new
{
Id = 4,
Rating = 5,
Seen = true,
Title = "The Matrix"
},
new
{
Id = 5,
Rating = 5,
Seen = true,
Title = "The Muppets"
});
});
#pragma warning restore 612, 618
}
}
}
SQL Server:
namespace MovieDatabase.Data.SQLServer.Migrations
{
/// <inheritdoc />
public partial class Initial : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Movies",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Title = table.Column<string>(type: "nvarchar(max)", nullable: false),
Rating = table.Column<int>(type: "int", nullable: false),
Seen = table.Column<bool>(type: "bit", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Movies", x => x.Id);
});
migrationBuilder.InsertData(
table: "Movies",
columns: new[] { "Id", "Rating", "Seen", "Title" },
values: new object[,]
{
{ 1, 0, false, "Shrek" },
{ 2, 1, true, "Inception" },
{ 3, 2, true, "The Green Latern" },
{ 4, 5, true, "The Matrix" },
{ 5, 5, true, "The Muppets" }
});
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Movies");
}
}
}
There are clearly differences in how Entity Framework creates the migrations and knows how to translate our entity, with properties, to SQLite and SQL Server.
Conclusion
Well, that’s it! We successfully “rebuilt” the application to use SQLite with little effort. Unit tests still work without any change. You can start the API and it will work just as with SQL Server.
One of the benefits of keeping the SQL Server project is that you can easily switch back. The entities are in a different project and shared between the SQLite and SQL Server projects. If you work on the SQLite variant and change the entities, you can create new migrations for the SQL Server project when you need it.
The repository has an interface, which promises what the implementation will and can do. So change the interface, and change all implementations. This is a safe way of working and makes it possible to switch back and forth between the SQLite and SQL Server projects.
Of course, you don’t create two projects for SQL Server and SQLite. You usually choose one and continue with that. But with this tutorial, I want to show you how easy it is to rewrite something and use SQLite.