Learn .NET - Data Access & Persistence

#Introduction

Real applications need storage. Episode 9 covers Entity Framework Core (EF Core) — the built-in .NET ORM and the primary way to access relational databases. With EF Core, you work with C# objects instead of writing SQL over and over, while still being able to drop down to raw SQL when needed.

In this episode you will configure a DbContext, create and run migrations, query with LINQ, and understand the differences between database providers. The result is a data foundation that the web API in phase 4 will build on.

#Entity Framework Core Fundamentals

#Installing EF Core

EF Core is added as a NuGet package. Choose the provider that matches your database:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

dotnet add package Microsoft.EntityFrameworkCore.Sqlite adds the SQLite provider together with the core EF Core dependencies. For SQL Server use Microsoft.EntityFrameworkCore.SqlServer, and for PostgreSQL use Microsoft.EntityFrameworkCore.Npgsql.

#Adding the Migrations Tool

A CLI tool is required to create migrations:

dotnet tool install --global dotnet-ef

dotnet tool install --global dotnet-ef installs the dotnet-ef tool, which provides the dotnet ef commands. Make sure it is installed before continuing to the migrations section.

#DbContext, Entity Mapping, and Migrations

#Creating a DbContext

A DbContext represents a working session with the database — the place where entities are mapped:

public class Produk
{
    public int Id { get; set; }
    public string Nama { get; set; }
    public decimal Harga { get; set; }
}
 
public class AppDbContext : DbContext
{
    public DbSet<Produk> Produk { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=app.db");
}

DbSet<Produk> exposes the Produk table as a queryable collection. EF Core conventions guess table and column names from property names; custom mapping goes in the OnModelCreating method.

#Migrations for the Database Schema

Migrations keep the database schema in sync with the model:

dotnet ef migrations add InitialCreate
dotnet ef database update

dotnet ef migrations add InitialCreate creates a migration file containing the schema changes, and dotnet ef database update applies them to the database. Migrations are C# files that go into Git — so schema changes can be reviewed during code review.

#Querying with LINQ and Raw SQL

#LINQ for Type-Safe Queries

Queries are written with LINQ, which is type-safe and compiled:

using var db = new AppDbContext();
 
var murah = await db.Produk
    .Where(p => p.Harga < 1_000_000m)
    .OrderByDescending(p => p.Harga)
    .ToListAsync();
 
foreach (var p in murah)
{
    Console.WriteLine($"{p.Nama} - {p.Harga}");
}

db.Produk.Where(p => p.Harga < 1_000_000m) is translated into SQL by EF Core. ToListAsync executes the query asynchronously. LINQ checks types at compile time — a wrong column produces a compile error, not a runtime one.

#Raw SQL for Complex Queries

For queries that are hard to express with LINQ, EF Core provides FromSqlRaw:

var hasil = await db.Produk
    .FromSqlRaw("SELECT * FROM Produk WHERE Harga > {0}", 500_000m)
    .ToListAsync();

FromSqlRaw executes raw SQL and maps the results to entities. Use parameterization (the {0} placeholder) to prevent SQL injection — never concatenate values into SQL strings manually.

#Database Providers

#Choosing a Provider

EF Core uses a provider to translate LINQ into each database's SQL dialect:

• SQL Server: UseSqlServer, full-featured, common in enterprises.
• PostgreSQL: UseNpgsql, open-source, with JSONB and full-text support.
• SQLite: UseSqlite, file-based, ideal for dev and prototypes.

dotnet add package Microsoft.EntityFrameworkCore.Npgsql

Switching providers only requires adding the package and changing the configuration method in OnConfiguring. The SQL syntax differences are handled by EF Core, so your query code stays the same.

#Connection Strings in Configuration

Connection strings should live in configuration, not hardcoded in code. Move them out of OnConfiguring by reading IConfiguration:

var koneksi = builder.Configuration.GetConnectionString("Default");
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite(koneksi));

AddDbContext registers the DbContext with a scoped lifetime — a perfect match for the DI from episode 8. The Default value can come from appsettings.json or the ConnectionStrings__Default environment variable.

#Data Practice Summary

• Register the DbContext with a scoped lifetime via AddDbContext.
• Write queries with LINQ; use FromSqlRaw for complex SQL.
• Always parameterize raw SQL to prevent injection.
• Choose the provider for your target: SQL Server, PostgreSQL, or SQLite.
• Keep connection strings in configuration, not in code.

#Closing

Key takeaways:

• EF Core maps C# entities to database tables.
• DbContext is the unit of work and the source of queries.
• Migrations keep the schema in sync and can be reviewed in Git.
• LINQ translates type-safe queries into SQL.
• FromSqlRaw requires parameterization for security.
• The SQL Server, PostgreSQL, and SQLite providers are chosen per need.

In the next episode 10 we will discuss configuration and application settings — the options pattern and binding to POCOs, multi-environment settings, secrets management, JSON, env var, and command line configuration providers, plus strongly typed configuration and validation.