C#.NET - day 09

day 09 : Local Database with EF Core (SQLite)

From in-memory experiments to persistent data

Introduction

All data lived in memory. Once the server stopped, everything disappeared.

Step 3.0 marks a clear transition:

from a learning-oriented, in-memory system
to a backend where data actually survives.

With this step, the API stops being a toy and starts behaving like a real backend service.

---

🌊 System Flow

1. Request: The Controller receives a request.
2. Delegation: The Service applies business rules.
3. Persistence: The Repository stores data via EF Core.
4. Storage: Data is written to a SQLite file (app.db).

---

0️⃣ Preparation: EF Core Packages

Entity Framework Core acts as the translator between C# objects and SQL.

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

---

1️⃣ Model Update: Adding a Primary Key

Databases require a unique identifier for each row. Without it, updates, deletes, and relationships are impossible.

using System.ComponentModel.DataAnnotations;

namespace HelloFlow.Models;

public class HelloResponse
{
    [Key]
    public int Id { get; set; }

    public string Message { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }
    public string Location { get; set; } = string.Empty;
}

This change signals that the class is no longer just a DTO, but a real database entity.

---

2️⃣ AppDbContext: The Bridge to the Database

AppDbContext is the bridge between C# code and the database.

using HelloFlow.Models;
using Microsoft.EntityFrameworkCore;

namespace HelloFlow.Data;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    public DbSet<HelloResponse> HelloResponses { get; set; }
}

The Repository no longer needs to know SQL. EF Core handles query generation and execution.

---

3️⃣ Repository: From Memory to Database

The Repository now depends on AppDbContext instead of an in-memory collection.

using HelloFlow.Data;
using HelloFlow.Models;

namespace HelloFlow.Repositories;

public class HelloRepository : IHelloRepository
{
    private readonly AppDbContext _context;

    public HelloRepository(AppDbContext context)
    {
        _context = context;
    }

    public void Save(HelloResponse data)
    {
        _context.HelloResponses.Add(data);
        _context.SaveChanges();
    }

    public List<HelloResponse> GetAll()
    {
        return _context.HelloResponses.ToList();
    }

    public List<HelloResponse> SearchAdvanced(string keyword, int pageNumber, int pageSize)
    {
        var query = _context.HelloResponses.AsQueryable();

        if (!string.IsNullOrWhiteSpace(keyword))
        {
            query = query.Where(x => x.Message.Contains(keyword));
        }

        return query
            .OrderByDescending(x => x.CreatedAt)
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .ToList();
    }
}

Calling SaveChanges() is critical. Without it, nothing is written to disk.

---

4️⃣ Program.cs: Database and Lifetime Configuration

With EF Core, lifetime configuration becomes important.

using HelloFlow.Services;
using HelloFlow.Repositories;
using HelloFlow.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Database configuration
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite("Data Source=app.db"));

// Lifetime alignment
builder.Services.AddScoped<IHelloRepository, HelloRepository>();
builder.Services.AddScoped<HelloService>();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.MapControllers();
app.Run();

Since DbContext is scoped per request, the Repository must also be scoped.

---

🛑 Database Creation: Migrations

The database file does not exist until migrations are applied.

dotnet tool install --global dotnet-ef
dotnet ef migrations add InitialCreate
dotnet ef database update

This process translates C# models into real database tables.

---

🧠 What Changed Fundamentally

• Data no longer disappears when the server stops.
• The system now meets a minimum real-world backend standard.
• The architecture remains unchanged while storage changes.

This is the payoff of the earlier steps: interfaces, repositories, and separation of concerns.

---

🧠 One-Sentence Summary

day 09 upgrades the system from an in-memory prototype to a backend with persistent, real-world storage.

---

✍️ My Notes & Reflections

• As more pieces are added, the system is becoming harder, but it also feels more organized.
• I am starting to sense how a single factory slowly comes together, with each component finding its place and role.