Repository pattern in C#

Problem

Queries scattered over the page and the checkout

The shop gives 10% off to a customer with three orders or more. The customer's page shows whether there is a discount, and the checkout applies it when paying. Each one writes its own query to the database and its own rule:

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
ShowDiscount(conn, "Maria");
Console.WriteLine($"Maria pays €{Checkout(conn, "Maria", 50.00m):F2}");

// The customer's page
static void ShowDiscount(SqliteConnection conn, string customer)
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) FROM orders WHERE customer = $customer";
    cmd.Parameters.AddWithValue("$customer", customer);
    var count = Convert.ToInt32(cmd.ExecuteScalar());
    var text = count > 3 ? "10% off" : "no discount";
    Console.WriteLine($"{customer}: {text}");
}

// The checkout
static decimal Checkout(SqliteConnection conn, string customer, decimal total)
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT id FROM orders WHERE customer = $customer";
    cmd.Parameters.AddWithValue("$customer", customer);
    using var reader = cmd.ExecuteReader();
    var count = 0;
    while (reader.Read())
    {
        count++;
    }
    return count >= 3 ? total * 0.9m : total;
}

Maria: no discount
Maria pays €45.00

Maria has three orders. Her page tells her "no discount", because there the rule is count > 3. The checkout gives her the discount, because there it is count >= 3. Two places, two versions of one rule, and nobody notices until a customer complains.

There is a second problem. The rule is glued to the query. To check whether >= 3 is right, you need a database with a table and rows in it. The orders table and its rows are filled by the Setup function, as in the lessons about the database in a program. It is hidden here so it doesn't get in the way.

The idea

Repository: the data behind an interface

The queries go to one place: a repository. It has a few methods named in the language of the shop, not of the database: FindById, FindByCustomer, Save. Whoever needs orders calls them and doesn't know whether there is SQL, a file or a dictionary in memory behind them.

This is the Repository pattern. Around it the code is arranged in layers, and each layer talks only to the layer below it:

the controller (the page, the checkout) gets the request and shows the answer;
the service holds the rule: "three orders or more give 10%";
the repository gives and keeps data. There are no rules in it;
the data: memory, SQLite or another database.

The service gets the repository from outside, in its constructor. This is Dependency Injection from the lesson Singleton and Dependency Injection. That is why any repository with the same methods can sit under it.

See it

The contract and the repository in memory

The order and the contract of the repository: three methods, and not a single line of SQL.

record Order(int Id, string Customer, decimal Total);

interface IOrderRepository
{
    Order? FindById(int id);
    List<Order> FindByCustomer(string customer);
    void Save(Order order);
}

The first repository keeps the orders in memory, in a dictionary from the number to the order. Save with the same number replaces the old order.

class InMemoryOrderRepository : IOrderRepository
{
    private readonly Dictionary<int, Order> orders = new();

    public Order? FindById(int id) => orders.GetValueOrDefault(id);

    public List<Order> FindByCustomer(string customer)
    {
        var result = new List<Order>();
        foreach (var order in orders.Values)
        {
            if (order.Customer == customer)
            {
                result.Add(order);
            }
        }
        return result;
    }

    public void Save(Order order) => orders[order.Id] = order;
}

One function fills any repository with the shop's orders. It only knows the contract, so it works with memory and with the database:

static void Fill(IOrderRepository orders)
{
    orders.Save(new Order(1, "Maria", 80.00m));
    orders.Save(new Order(2, "Ivan", 25.50m));
    orders.Save(new Order(3, "Maria", 40.00m));
    orders.Save(new Order(4, "Maria", 12.90m));
}

See it

The service doesn't know where the data comes from

The rule is in the service, and the controller only shows the answer. Neither of them writes SQL:

class LoyaltyService
{
    private readonly IOrderRepository orders;

    public LoyaltyService(IOrderRepository orders)
    {
        this.orders = orders;
    }

    public int DiscountFor(string customer) => orders.FindByCustomer(customer).Count >= 3 ? 10 : 0;
}

class OrderController
{
    private readonly LoyaltyService loyalty;

    public OrderController(LoyaltyService loyalty)
    {
        this.loyalty = loyalty;
    }

    public void Show(string customer)
    {
        var discount = loyalty.DiscountFor(customer);
        var text = discount > 0 ? $"{discount}% off" : "no discount";
        Console.WriteLine($"{customer}: {text}");
    }
}

The same service and the same controller, first on top of memory, then on top of SQLite:

IOrderRepository memory = new InMemoryOrderRepository();
Fill(memory);
new OrderController(new LoyaltyService(memory)).Show("Maria");

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
IOrderRepository database = new SqliteOrderRepository(conn);
Fill(database);
new OrderController(new LoyaltyService(database)).Show("Maria");

Maria: 10% off
Maria: 10% off

class LoyaltyService
{
    private readonly IOrderRepository orders;

    public LoyaltyService(IOrderRepository orders)
    {
        this.orders = orders;
    }

    public int DiscountFor(string customer) => orders.FindByCustomer(customer).Count >= 3 ? 10 : 0;
}

class OrderController
{
    private readonly LoyaltyService loyalty;

    public OrderController(LoyaltyService loyalty)
    {
        this.loyalty = loyalty;
    }

    public void Show(string customer)
    {
        var discount = loyalty.DiscountFor(customer);
        var text = discount > 0 ? $"{discount}% off" : "no discount";
        Console.WriteLine($"{customer}: {text}");
    }
}

IOrderRepository memory = new InMemoryOrderRepository();
Fill(memory);
new OrderController(new LoyaltyService(memory)).Show("Maria");

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
IOrderRepository database = new SqliteOrderRepository(conn);
Fill(database);
new OrderController(new LoyaltyService(database)).Show("Maria");

Step by step, in words

A repository in memory, filled with the orders. The service gets it without knowing what it is.
The controller gets the request for Maria. It doesn't ask the database: it calls the service.
The service needs the orders of the customer and asks the repository for them.
FindByCustomer on IOrderRepository. The repository in memory answers: it walks through its dictionary.
Three orders of Maria come up. The rule is here and only here: 3 ≥ 3, so 10%.
The controller only shows the answer: “Maria: 10% off”.
The swap: SQLite takes the place of memory. It is the same IOrderRepository, so the service and the controller are not touched.
The same call. Only the object under the service is different.
FindByCustomer now sends SELECT … WHERE customer = ? to the orders table.
Three rows again, 10% again. The service never noticed that the source changed.
The same line, this time from a database.
Controller → service → repository → data. Only the bottom layer changed, and the rule stayed in one place.

Only the bottom layer changed. The service and the controller are the same classes, without a single changed line, and Maria now gets the discount both on the page and at the checkout, because there is one rule.

See it

The real database behind the same interface

Here is the repository in SQLite. Every method is one query with parameters ($customer), never text glued into the SQL. Why that matters is in the lesson SQL injection and parameters.

class SqliteOrderRepository : IOrderRepository
{
    private readonly SqliteConnection conn;

    public SqliteOrderRepository(SqliteConnection conn)
    {
        this.conn = conn;
        var cmd = conn.CreateCommand();
        cmd.CommandText = "CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)";
        cmd.ExecuteNonQuery();
    }

    public Order? FindById(int id)
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT id, customer, total FROM orders WHERE id = $id";
        cmd.Parameters.AddWithValue("$id", id);
        using var reader = cmd.ExecuteReader();
        return reader.Read() ? Read(reader) : null;
    }

    public List<Order> FindByCustomer(string customer)
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT id, customer, total FROM orders WHERE customer = $customer ORDER BY id";
        cmd.Parameters.AddWithValue("$customer", customer);
        using var reader = cmd.ExecuteReader();
        var result = new List<Order>();
        while (reader.Read())
        {
            result.Add(Read(reader));
        }
        return result;
    }

    public void Save(Order order)
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = "INSERT INTO orders (id, customer, total) VALUES ($id, $customer, $total) ON CONFLICT (id) DO UPDATE SET customer = excluded.customer, total = excluded.total";
        cmd.Parameters.AddWithValue("$id", order.Id);
        cmd.Parameters.AddWithValue("$customer", order.Customer);
        cmd.Parameters.AddWithValue("$total", order.Total);
        cmd.ExecuteNonQuery();
    }

    private static Order Read(SqliteDataReader r) => new(r.GetInt32(0), r.GetString(1), r.GetDecimal(2));
}

IOrderRepository knows nothing about SqliteConnection. Only this repository knows it, and the service sees the interface.

This is where the SQL for orders lives. Here you can fix a slow query, add an index or swap SQLite for PostgreSQL, and the service doesn't notice. If the repository becomes an ORM, watch out for a query per row: the lesson ORM and the N+1 problem shows what it looks like.

See it

The rule without a database

Since the service accepts any repository, the rule is checked with the repository in memory: no file, no server, in milliseconds.

var orders = new InMemoryOrderRepository();
var loyalty = new LoyaltyService(orders);
orders.Save(new Order(1, "Maria", 80.00m));
orders.Save(new Order(2, "Maria", 40.00m));
Console.WriteLine($"2 orders: {loyalty.DiscountFor("Maria")}%");
orders.Save(new Order(3, "Maria", 12.90m));
Console.WriteLine($"3 orders: {loyalty.DiscountFor("Maria")}%");

2 orders: 0%
3 orders: 10%

Two orders are not enough, the third gives 10%. That is exactly the border where the page and the checkout disagreed. This is also what a test of the rule looks like: you fill the repository in memory and check what the service says.

Try it

Predict the output

Order number 1 is saved twice, with a different total. What does the program print?

IOrderRepository orders = new InMemoryOrderRepository();
orders.Save(new Order(1, "Maria", 80.00m));
orders.Save(new Order(1, "Maria", 50.00m));
Console.WriteLine(orders.FindByCustomer("Maria").Count);
Console.WriteLine($"€{orders.FindById(1)!.Total:F2}");

Answer

1 / €50.00

1 / €80.00

2 / €50.00

2 / €80.00

Try it

Find the bug

Someone decides that searching by customer is slow and writes a repository that keeps the orders by customer right away:

class FastOrderRepository : IOrderRepository
{
    private readonly Dictionary<string, List<Order>> byCustomer = new();

    public Order? FindById(int id)
    {
        foreach (var orders in byCustomer.Values)
        {
            foreach (var order in orders)
            {
                if (order.Id == id)
                {
                    return order;
                }
            }
        }
        return null;
    }

    public List<Order> FindByCustomer(string customer) => byCustomer.GetValueOrDefault(customer) ?? new List<Order>();

    public void Save(Order order)
    {
        foreach (var orders in byCustomer.Values)
        {
            orders.RemoveAll(o => o.Id == order.Id);
        }
        if (!byCustomer.ContainsKey(order.Customer))
        {
            byCustomer[order.Customer] = new List<Order>();
        }
        byCustomer[order.Customer].Add(order);
    }
}

A new method appears in the service: "will there be a discount if they buy this one too?". Then the page asks how many orders Ivan has:

class LoyaltyService
{
    private readonly IOrderRepository orders;

    public LoyaltyService(IOrderRepository orders)
    {
        this.orders = orders;
    }

    public int DiscountFor(string customer) => orders.FindByCustomer(customer).Count >= 3 ? 10 : 0;

    public int DiscountIfBuys(string customer, Order cart)
    {
        var list = orders.FindByCustomer(customer);
        list.Add(cart);
        return list.Count >= 3 ? 10 : 0;
    }
}

IOrderRepository orders = new FastOrderRepository();
Fill(orders);
var loyalty = new LoyaltyService(orders);
Console.WriteLine($"If Ivan buys one more: {loyalty.DiscountIfBuys("Ivan", new Order(5, "Ivan", 30.00m))}%");
Console.WriteLine($"Ivan's orders: {orders.FindByCustomer("Ivan").Count}");

If Ivan buys one more: 0%
Ivan's orders: 2

Ivan has one order, and the repository says two. Nobody called Save. On which line of the repository is the bug?

Pick the line with the bug.

The idea

When you don't need it

Repository adds an interface and at least one class. It isn't worth it when:

the program is a small script with one query. The layers will be longer than the work itself;
you use an ORM that already gives a repository (DbContext in Entity Framework). Your own interface that only forwards to it line by line is one more file with nothing new;
the repository grows a method for every page: FindByCustomerAndStatusOrderByTotal and twenty more like it. Then the queries are scattered again, just with longer names.

It pays off when the rules must be checked without a database, when the source can change (a database, a file, another service) or when the same queries are used from many places.

Exercise

Deleting from the repository

Solve the task in your editor.

Delete. Add Delete(id) to the contract and to both repositories. In SQLite it is DELETE FROM orders WHERE id = ?. Show Maria's discount, delete order 4 and show it again, once with memory and once with the database.

Maria: 10% off
Maria: no discount
Maria: 10% off
Maria: no discount

More tasks

A rule by total. The shop changes the rule: 10% after €150 spent or more. Which class do you touch? Check it with the repository in memory, without a database.
A third repository. Write a repository that keeps the orders in a file, one per line. The service and the controller must stay unchanged.
A copy when searching. Fix the repository from "Find the bug" and add a check: after DiscountIfBuys Ivan still has 1 order.

Test

Check yourself

1. Where does the rule "3 orders or more give 10% off" live?In the controller, which shows the answerIn the service, which gets the repository from outsideIn the repository, next to the query

2. Why can the rule be checked without a database?It can’t: the rule always needs the real databaseBecause SQLite is fastBecause the service gets the repository as an interface, and the repository in memory is enough

3. The repository returns the list it keeps inside, and the caller adds to it. What happens?The added order shows up in the repository, without anyone calling saveNothing: the list is a copyThe program doesn’t compile

4. The project uses an ORM that already gives a repository for every table. Is it worth adding your own interface with the same methods on top?Yes, there is always room for one more layerNo, ORMs forbid interfacesUsually not: an interface that only forwards to the ORM adds code without adding anything

Repository

Cookies