Richiban

Testing the queries in your data access layer

Posted on 2014-02-26 Updated on 2016-04-06

When it comes to writing automated tests I have, in the past, tended to ignore the data access layer. Do you really need to test your queries? As long as the code was loosely coupled and I could test everything else, I was happy. After talking at work I was convinced that, in some cases, it might be worth testing your queries themselves in order to check that they’re returning the data that they should be.

This provided a dilemma, because I will always try to avoid writing integration/database tests if I can help it, but how do you test queries without a database? A possible answer is as follows, taken from my post on StackOverflow:

 

After some deliberation, my colleague and I came up with a way to separate the query from the persistence layer. I threw together a quick example below:

public class Invoice
{
    public int Id {get; set;}
    public DateTime DueOn {get; set;}
    public DateTime MadeOn {get; set;}
    public decimal Total {get; set;}
    public Customer Customer {get;set;}
}

public class Customer
{
    public int Id {get; set;}
    public string Name {get; set;}
}

public class OverdueInvoice
{
    public int CustomerId {get; set;}
    public int InvoiceId {get; set;}
    public string CustomerName {get; set;}
    public decimal Total {get; set;}
}

public class InvoiceQueries
{
    private readonly IReadRepository<Invoice> _invoices;

    public InvoiceQueries(IReadRepository<Invoice> invoices)
    {
        _invoices = invoices;
    }

    public IEnumerable<OverdueInvoice> GetAllOverdueInvoices(TimeSpan timeOverdue)
    {
        return _invoices
            .FindAll(invoice => invoice.DueOn - timeOverdue > DateTime.Now)
            .Select(Map);
    }

    private OverdueInvoice Map(Invoice invoice)
    {
        return new OverdueInvoice
        {
            CustomerId = invoice.Customer.Id,
            InvoiceId = invoice.Id,
            CustomerName = invoice.Customer.Name,
            Total = invoice.Total,
        };
    }
}

public class InMemoryInvoicesRepository : IReadRepository<Invoice>, IWriteRepository<Invoice>
{
    private List<Invoice> _backingStore;

    public InMemoryInvoicesRepository() : this(new List<Invoice>())
    {}

    public InMemoryInvoicesRepository(List<Invoice> backingStore)
    {
        _backingStore = backingStore;
    }

    public IEnumerable<Invoice> FindAll(Predicate<Invoice> predicate)
    {
        return _backingStore.FindAll(predicate);
    }

    public void Add(Invoice item)
    {
        _backingStore.Add(item);
    }
}

public interface IReadRepository<T>
{
    IEnumerable<T> FindAll(Predicate<T> predicate);
}

public interface IWriteRepository<T>
{
    void Add(T item);
}

Separating the data access into a ‘query’ object and a ‘repository’ object allows you to replace the repository at test time with an in-memory collection. This way, you can fill a List with whatever objects you want and then test your query layer by running it against that list. The repository is a really dumb object that takes your query and just passes it through to your ORM (to actually do this Func might have to be replaced by Expression>).

The StackOverflow question can be found here: http://stackoverflow.com/questions/22013850/is-it-worth-testing-data-access-queries-themselves/22048531#22048531