C# / .NET: Entity Framework Core

Saves an entity through a DbContext and counts with a LINQ query translated to SQL. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DB_CONNECTION="Host=old-postgres;Port=5432;Database=app;Username=app;Password=..."
DB_CONNECTION="Host=machsql-host;Port=5432;Database=app;Username=app;Password=..."

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• .NET 10
• Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 (EF Core 10)

Run

export DB_CONNECTION="Host=machsql-host;Port=5432;Database=app;Username=app;Password=..."
dotnet run

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

Program.cs

// Entity Framework Core with the Npgsql provider.
using Microsoft.EntityFrameworkCore;

var connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION")
    ?? throw new InvalidOperationException("set DB_CONNECTION");

await using var db = new TodoContext(connectionString);
await db.Database.ExecuteSqlRawAsync(
    "CREATE TABLE IF NOT EXISTS ef_todo (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title text NOT NULL, done boolean NOT NULL DEFAULT false)");

var todo = new Todo { Title = "write the examples" };
db.Todos.Add(todo);
await db.SaveChangesAsync();
var open = await db.Todos.CountAsync(t => !t.Done);

Console.WriteLine($"inserted todo {todo.Id}, {open} open");

sealed class TodoContext(string connectionString) : DbContext
{
    public DbSet<Todo> Todos => Set<Todo>();

    protected override void OnConfiguring(DbContextOptionsBuilder options) =>
        options.UseNpgsql(connectionString);

    protected override void OnModelCreating(ModelBuilder model) =>
        model.Entity<Todo>(e =>
        {
            e.ToTable("ef_todo");
            e.Property(t => t.Id).HasColumnName("id");
            e.Property(t => t.Title).HasColumnName("title");
            e.Property(t => t.Done).HasColumnName("done");
        });
}

sealed class Todo
{
    public long Id { get; set; }
    public required string Title { get; set; }
    public bool Done { get; set; }
}

Example.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.3" />
  </ItemGroup>

</Project>