K Cartlidge

Adding SQLite EF Core 5 models and context to a .Net Core 5 project

2020-11-22

Entity Framework Core 5 is both light and performant.

It’s possible to create bad structures or queries with it, but that isn’t the fault of the tools and someone who is capable of doing that is probably capable of doing similar things in other toolchains.

In general, EF Core is a great option for rapid application development, and one that can carry over into production usage.

What database to use, though? EF Core supports the obvious candidates, SQL Server, Oracle, etc., and it also supports SQLite. This gives you a file-system database which is small, pretty efficient, and amenable to source control or even dropbox/email backups.

How to set up EF Core and SQLite

I’m assuming the use of Visual Studio 2019 (Community Edition is free). If you are using VS Code, Rider, or something else, the Package Manager Console commands may need to be done differently (eg dotnet add package ...). That’s out of scope of this post, though.

Dependencies

First you need to install the relevant packages into your solution using the Package Manager Console. If you are creating a new solution from scratch, there is nothing special to select during creation.

Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.Design
Install-Package Microsoft.EntityFrameworkCore.Sqlite

Data structures

Once the packages are installed, add some models. I usually place these in a new Data folder.

public class Account
{
    public int ID { get; set; }
    public string EmailAddress { get; set; }
    public virtual ICollection<Note> Notes { get; set; }
}

public class Note
{
    public int ID { get; set; }
    public int AccountID { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}

This gives an Account which contains a Note collection. Each table (account and note) will have an ID column.

The note table will also be given an AccountId column for the relationship automatically; by adding it manually we get the benefit of a navigational property, and for seed data it becomes easier to set the parent account.

Database context

Now you need to add a database context containing those models. I usually add this to the same Data folder as the models.

public class MyContext : DbContext
{
    public DbSet<Account> Accounts { get; set; }
    public DbSet<Note> Notes { get; set; }
}

This defines what your database will look like, but how does it know to use SQLite? In the same class, override the OnConfiguring method.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite(@"Data Source=MyNotes.db;");
}

This will ensure that whenever MyContext is created, it will be configured to use SQLite and a database file named MyNotes.db in the runtime folder. Obviously in reality you should site your local database file inside a folder that won’t get wiped/recreated during deploys, but this will do for development purposes.

Pulling it all together

Having done those few steps, you now need to create a new migration using the Package Manager Console.

Add-Migration InitialTables

This will create a migration and open it in the IDE. You’ll see the statements that would be run to generate the database.

Now you can apply those (and thereby create an actual database), again using the Package Manager Console.

Update-Database

Using it

You just need to register your DbContext in Startup.cs and you’re done. Place the following anywhere in the ConfigureServices method.

services.AddDbContext<MyContext>();

There’s no further work needed. You have a MyContext dependency which can be freely injected and used anywhere, and will operate against the MyNotes.db local database file.

The simplest way to test this is to add a controller (NotesController for this example) and choose the full EF Core scaffolding when asked. There won’t be any initial data, but you’ll be able to add some when you launch the UI (make it easy on yourself by adding another li in the logged-in navigation bit in _Layout.cshtml).

As an aside, in the first EF Core talk in .NetConf 2020 they recommend that as the context is so lightweight you should use a new instance each time - so don’t inject MyContext, inject a factory. That’s for another time; injecting the context directly also works fine.