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.

EF Core supports the obvious database candidates such as SQL Server, Oracle, Postgres, etc. It also supports SQLite, which gives you a file-system database that’s small, pretty efficient, and amenable to source control or even dropbox/email backups. As I’m detailing the code side and not how to install and lock down database servers, I’ll be using SQLite here. Other than the bit in setting up the context that specifies connection details this process works with the others too.

How to set up EF Core with SQLite

To install and use EF Core on the command line/terminal, run the following (only needed once):

dotnet tool install --global dotnet-ef

Dependencies

First you need to add the relevant packages to your solution. If you are creating a new solution from scratch, there is nothing special to select during creation.

From the command line/terminal (make sure you run the commands from within the project folder):

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

To do this using Package Manager Console instead, replace dotnet add package with Install-Package.

Data structures

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

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace Whatever.Data
{
  public class Account
  {
    [Key]
    public int ID { get; set; }
    public virtual ICollection<Note> Notes { get; set; }

    [Required(ErrorMessage = "The Email Address is required.")]
    [MinLength(3, ErrorMessage = "The Email Address must be at least 3 characters.")]
    [MaxLength(100, ErrorMessage = "The Email Address cannot be more than 100 characters.")]
    public string EmailAddress { get; set; }
  }
}

using System;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;

namespace Whatever.Data
{
  public class Note
  {
    [Key]
    public int ID { get; set; }
    public virtual Account Account { get; set; }

    [DisplayName("Created")]
    [Required(ErrorMessage = "The Created Date is required.")]
    [DisplayFormat(DataFormatString = "{0:d}")]
    public DateTime CreatedAt { get; set; }

    [Required(ErrorMessage = "The Title is required.")]
    [MaxLength(100, ErrorMessage = "The title cannot be more than 100 characters.")]
    public string Title { get; set; }

    [Required(ErrorMessage = "Content is required.")]
    [MaxLength(50000, ErrorMessage = "The content cannot be more than 50,000 characters.")]
    public string Content { get; set; }
  }
}

This gives an Account which contains a Note collection. Each table (account and note) will have an ID column. Columns for the relationship will be added automatically.

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.

using Microsoft.EntityFrameworkCore;

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.

Optional seed data

You can choose to pre-populate the new tables with seed data by adding the following into the same context class.

using System;

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<Account>().HasData(
    new Account {
      ID = 1,
      EmailAddress = "email@example.com"
    });
  modelBuilder.Entity<Note>().HasData(
    new Note
    {
      ID = 1,
      AccountID = 1,
      CreatedAt = DateTime.UtcNow,
      Title = "Sample Note",
      Content = "This is a sample pre-populated note."
    });
}

Pulling it all together

Having done those few steps, you now need to create a new migration.

In the command line/terminal run the following:

dotnet ef migrations add InitialMigration

To do this with VS and the Package Manager Console instead (select the right project from the drop-down):

Add-Migration InitialTables

Now you can apply those migrations (which for SQLite also creates the database file):

dotnet ef database update

If you prefer using the Package Manager Console:

Update-Database

Using it in your code

Register your DbContext in Startup.cs by adding it to 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 an MVC with EF views controller (NotesController for this example) and choose the full EF Core scaffolding when asked. There won’t be any initial data (unless you included some seed data as above) but you’ll be able to add some when you run the site and navigate to the new views. Make it easy on yourself by adding another li in the logged-in navigation links in the default _Layout.cshtml linking to, for example, https://localhost:5001/Notes.

Generating scaffolded views often seems to add the Microsoft.EntityFrameworkCore.SqlServer package the first time, but feel free to remove it.

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