Entity Framework

Table of Contents

SQLite

The example referenced above creates an SQLite database in the $HOME/.local/share folder.

I initially followed the guide to create the SQLite database and after that remove the SQLite package and adjust the code for a PostgreSQL database.

dotnet new console -n EF01 --verbosity normal
cd EF01

Because we’ll use version control on the project this will provide a nice .gitignore file.

dotnet new gitignore

Add the EntityFramework package for SQLite

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

Install EF tools EF tools reference

dotnet tool install --global dotnet-ef

export PATH=$HOME/.dotnet/tools:$PATH

# verify
dotnet ef -help

The Microsoft.EntityFrameworkCore.Design package is required for either command-line or Package Manager Console-based tooling, and is a dependency of dotnet-ef and Microsoft.EntityFrameworkCore.Tools. Reference.

dotnet add package Microsoft.EntityFrameworkCore.Design

Create the Model.cs file.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    public string DbPath { get; }

    public BloggingContext()
    {
        var folder = Environment.SpecialFolder.LocalApplicationData;
        var path = Environment.GetFolderPath(folder);
        DbPath = System.IO.Path.Join(path, "blogging.db");
    }

    // The following configures EF to create a Sqlite database file in the
    // special "local" folder for your platform.
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite($"Data Source={DbPath}");
}

public class Blog
{
    public int BlogId { get; set; }
    public required string Url { get; set; }

    public List<Post> Posts { get; } = new();
}

public class Post
{
    public int PostId { get; set; }
    public required string Title { get; set; }
    public required string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

This creates the Migrations folder.

dotnet ef migrations add InitialCreate

$ dotnet-ef migrations add InitialCreate

Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'

$ ls Migrations/

20260103182234_InitialCreate.cs
20260103182234_InitialCreate.Designer.cs
BloggingContextModelSnapshot.cs

dotnet ef database update

$ dotnet ef database update
Build started...
Build succeeded.
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Applying migration '20260103182234_InitialCreate'.
Done.

Update Program.cs to utilize Entity Framework.

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

using var db = new BloggingContext();

// Note: This sample requires the database to be created before running.
Console.WriteLine($"Database path: {db.DbPath}.");

// Create
Console.WriteLine("Inserting a new blog");
db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
await db.SaveChangesAsync();

// Read
Console.WriteLine("Querying for a blog");
var blog = await db.Blogs
    .OrderBy(b => b.BlogId)
    .FirstAsync();

// Update
Console.WriteLine("Updating the blog and adding a post");
blog.Url = "https://devblogs.microsoft.com/dotnet";
blog.Posts.Add(
    new Post { Title = "Hello World", Content = "I wrote an app using EF Core!" });
await db.SaveChangesAsync();

// Delete
Console.WriteLine("Delete the blog");
db.Remove(blog);
await db.SaveChangesAsync();

Then run the program.

dotnet run

$ dotnet run
Database path: /home/porky/.local/share/blogging.db.
Inserting a new blog
Querying for a blog
Updating the blog and adding a post
Delete the blog

Access the SQLite database directly

sudo apt update
sudo apt install sqlite3

sqlite3 --version

sqlite3 /home/porky/.local/share/blogging.db

In SQLite you can see the listed tables.

sqlite> .tables
Blogs                  __EFMigrationsHistory
Posts                  __EFMigrationsLock  

sqlite> .quit

Remove database and migrations

You can simply delete the db file.

rm /home/porky/.local/share/blogging.db

Then remove the migrations like this.

dotnet ef migrations remove

PostgreSQL - OnConfiguring

Remove the SQLite package and add PostgreSQL.

dotnet package remove Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

This will update the .csproj file

-    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.1" />
+    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.0" />

I needed to re-add the dotnet path after reinstalling the database package.

export PATH=$HOME/.dotnet/tools:$PATH

For PostgreSQL I updated the Model.cs file with my postgres database setup.

-     protected override void OnConfiguring(DbContextOptionsBuilder options)
-         => options.UseSqlite($"Data Source={DbPath}");

+    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
+        => optionsBuilder.UseNpgsql(@"Host=localhost;Username=sammy;Password=sammy;Database=postgres");

dotnet ef migrations add InitialCreate

dotnet ef database update

There was an error during the update. It seems like a simple SELECT that failed for some reason.

$ dotnet ef database update
Build started...
Build succeeded.
Failed executing DbCommand (28ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Applying migration '20260103223948_InitialCreate'.
Done.

I logged into postgres and executed the SQL command mentioned in the error above.

$ psql -d postgres
psql (17.6 (Debian 17.6-0+deb13u1))
Type "help" for help.

postgres=# SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
         MigrationId          | ProductVersion 
------------------------------+----------------
 20260103223948_InitialCreate | 10.0.1
(1 row)

postgres=# \d
                 List of relations
 Schema |         Name          |   Type   | Owner 
--------+-----------------------+----------+-------
 public | Blogs                 | table    | sammy
 public | Blogs_BlogId_seq      | sequence | sammy
 public | Posts                 | table    | sammy
 public | Posts_PostId_seq      | sequence | sammy
 public | __EFMigrationsHistory | table    | sammy
(5 rows)

Running dotnet run works fine.

$ dotnet run
Database path: /home/porky/.local/share/blogging.db.
Inserting a new blog
Querying for a blog
Updating the blog and adding a post
Delete the blog

PostgreSQL - ASP.NET / DI

The PostgreSQL implementation above used the OnConfiguring() function to configure the context. It is “…discouraged for most production applications”.

Per the npgsql documentation the configuration should be different for ASP.NET / DI applications.

I logged into the database and dropped the tables created by the migration and removed the migrations.

dotnet ef migrations remove

UNDER CONSTRUCTION

PostgreSQL - DbContext Pooling

To implement DbContextFactroy I added the BloggingContextFactory.

using System;
using System.Linq;
using System.Collections.Generic; // WebApplication
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;

using var db = (new BloggingContextFactory()).CreateDbContext( [] );

// Create
Console.WriteLine("Inserting a new blog");
db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
await db.SaveChangesAsync();

// Read
Console.WriteLine("Querying for a blog");
var blog = await db.Blogs
    .OrderBy(b => b.BlogId)
    .FirstAsync();

// Update
Console.WriteLine("Updating the blog and adding a post");
blog.Url = "https://devblogs.microsoft.com/dotnet";
blog.Posts.Add(
    new Post { Title = "Hello World", Content = "I wrote an app using EF Core!" });
await db.SaveChangesAsync();

// Delete
Console.WriteLine("Delete the blog");
db.Remove(blog);
await db.SaveChangesAsync();

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;

public class BloggingContextFactory : IDesignTimeDbContextFactory<BloggingContext>
{
    public BloggingContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<BloggingContext>();
        optionsBuilder.UseNpgsql(@"Host=localhost;Port=5432;Username=sammy;Password=sammy;Database=postgres");
        
        return new BloggingContext(optionsBuilder.Options);
    }

}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    public BloggingContext(DbContextOptions<BloggingContext> options) : base(options)
    {
    }

}

public class Blog
{
    public int BlogId { get; set; }
    public required string Url { get; set; }

    public List<Post> Posts { get; } = new();
}

public class Post
{
    public int PostId { get; set; }
    public required string Title { get; set; }
    public required string Content { get; set; }

    public int BlogId { get; set; }
    public Blog? Blog { get; set; }
}