Learn C# - Data Access & Persistence

#Introduction

Almost every real application stores data permanently in a database. In the .NET ecosystem, the most common way to do this is through Entity Framework Core — an Object-Relational Mapper that maps C# classes to tables and rows.

With EF Core, you work with the database as objects and LINQ, rather than writing manual SQL for every operation. Even so, understanding what happens behind the scenes remains important so queries don't become slow in production.

Episode 9 covers DbContext setup, CRUD operations, migrations and relationships, querying with LINQ and raw SQL, and database provider choices.

#Entity Framework Core and DbContext

#Installing the Packages

Start by adding the EF Core package and a provider of your choice. The example below uses SQLite so it's easy to try:

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

The dotnet add package Microsoft.EntityFrameworkCore.Sqlite command adds the SQLite provider. For PostgreSQL or SQL Server, replace it with Npgsql.EntityFrameworkCore.PostgreSQL or Microsoft.EntityFrameworkCore.SqlServer.

#Creating a DbContext and Models

DbContext is the main unit of work — the object that represents a session with the database:

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

The DbSet<Produk> property is the gateway to the Produk table. EF Core conventions turn the Produk class into a table with Id, Nama, and Harga columns automatically.

#CRUD Operations

#Create, Read, Update, Delete

CRUD in EF Core is done through the Add, Find, and Remove methods, then saved with SaveChangesAsync:

using var db = new TokoContext();
await db.Database.EnsureCreatedAsync();
 
var kopi = new Produk { Nama = "Kopi Gayo", Harga = 85_000m };
db.Produk.Add(kopi);
await db.SaveChangesAsync();
 
var hasil = await db.Produk.FindAsync(kopi.Id);
hasil!.Harga = 90_000m;
await db.SaveChangesAsync();
 
db.Produk.Remove(hasil);
await db.SaveChangesAsync();
Console.WriteLine("CRUD selesai.");

Notice that changes are only applied to the database when SaveChangesAsync is called. FindAsync reads by primary key with caching, and Remove marks a row for deletion.

#Migrations and Relationships

#Creating and Applying Migrations

Migrations record database schema changes as versioned code, so teams can keep databases in sync without manual SQL:

dotnet ef migrations add BuatTabelProduk
dotnet ef database update

The dotnet ef migrations add BuatTabelProduk command produces a migration file, and dotnet ef database update applies it to the database. Migration files should be committed to Git so every developer has the same schema history.

#Relationships Between Entities

Relationships are declared through navigation properties. An example of a one-to-many relationship:

class Kategori
{
    public int Id { get; set; }
    public string Nama { get; set; } = "";
    public List<Produk> Produk { get; set; } = new();
}
 
class Produk
{
    public int Id { get; set; }
    public string Nama { get; set; } = "";
    public int KategoriId { get; set; }
    public Kategori Kategori { get; set; } = null!;
}

EF Core turns KategoriId into a foreign key. Load related data with Include:

var kategori = await db.Kategori
    .Include(k => k.Produk)
    .FirstAsync();
Console.WriteLine($"{kategori.Nama} punya {kategori.Produk.Count} produk");

Include performs a join to pull in products at once, avoiding N+1 queries.

#Querying with LINQ and Raw SQL

#LINQ Queries and FromSqlRaw

Most queries are written with LINQ, which is translated to SQL. For complex cases, FromSqlRaw executes raw SQL:

var murah = await db.Produk
    .Where(p => p.Harga < 100_000m)
    .OrderByDescending(p => p.Harga)
    .ToListAsync();
 
var manual = await db.Produk
    .FromSqlRaw("SELECT * FROM Produk WHERE Harga < 100")
    .ToListAsync();
 
Console.WriteLine($"LINQ: {murah.Count}, raw SQL: {manual.Count}");

Use LINQ as the default; use FromSqlRaw for queries that are hard to express. Always use parameterized SQL through FromSqlInterpolated — never concatenate user input strings, because that's prone to SQL injection (covered in episode 13).

#Modern Database Providers

#Choosing a Database

EF Core is provider-agnostic: most code stays the same, only the package and the Use line change:

• SQL Server: the primary choice for Windows enterprise applications, via UseSqlServer.
• PostgreSQL: open-source, feature-complete, popular in startups and cloud-native.
• SQLite: zero-configuration, perfect for prototypes and unit tests.

options.UseNpgsql(
    "Host=localhost;Database=toko;Username=postgres;Password=rahasia");

Change one Use line and the package, and your models and migrations largely keep working. That's the power of the EF Core abstraction.

#Closing

Key takeaways:

• EF Core maps classes to tables through DbContext.
• Changes are applied to the database only when SaveChangesAsync is called.
• Migrations make schema changes documented and versioned.
• Include prevents N+1 queries when loading relationships.
• Providers can be swapped by changing one line of configuration.

In the next episode 10 we ensure quality: testing and quality assurance — unit testing with xUnit, mocking with Moq, integration testing with WebApplicationFactory, and static analysis with Roslyn analyzers.