Implementing repository querying interface in EF Core DbContext

My last bold statement was that we don’t need custom unit of work and repository classes with Entity Framework Core. One issue remained unsolved and it was querying part of repositories (yeah, those custom querying methods). After some experiments to get querying interface of repositories to DbContext I worked out something that I kind of like. I managed to make DbContext to work like classic unit of work that hosts repository instances. Here’s my experiment and the solution I worked out.

NB! My goal here is to stay strictly with Entity Framework Core or some other mapper that supports LINQ queries. Replacing ORM is usually extreme case and during my 20+ years in software development I have seen only few cases when it was done. If you prefer NHibernate over EF Core then check out my post NHibernate on ASP.NET Core to see how imitate DbContext with NHibernate.

“Repositorifying” DbContext

Making DbContext similar to typical unit of work that contains repository instances is not easy. One weakness of repository pattern is that it can be easily abused as a querying methods store. I have seen repositories that are thousands of lines long thanks to querying methods. But hey, they are still easy to use in unit tests as they implement interfaces we can mock. DbContext is different beast. It can easily be injected to repositories and other classes but it’s not easy to inject querying part of repositories there.

It would be nice to have something like shown here.

public class SalesDbContext : DbContext
{
    public SalesDbContext(DbContextOptions<SalesDbContext> options) : base(options)
    {
    }
 
    public DbSet<Customer> Customers { get; set; }
 
    // Other DbSets
}

public void DoSomething()
{
    var debtors = dbContext.Customers.ListDebtors();
 
    // ...
 
    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.Customers.ListNewRegistrations(date);
}

There are some obstacles on our way that are hard – if not impossible – to remove with simple tricks. Let’s briefly explore our options:

• DbSet<T> is not for inheriting– internals of instantiating DbSet<T> are complex and luckily these complexities are hidden from us. Still these complexities are good enough reason for product group to not support extending of DbSet<T>. Let’s agree with them and abandon the idea of using digital violence to get our will.
• Extension methods for DbSet<T> – we can build extension methods for given DbSet<T> and host these methods in some static class in same namespace. It’s the easy way out but it comes with price – we can’t easily mock extension methods. We have to use type isolation and there is limited number of tools with this feature.
• Querying methods in DbContext – another easy way out but again there’s price to pay. Piling all querying methods to DbContext will bloat this class. It can get even worse if querying methods call each other. I wouldn’t go with this approach.
• Injecting querying interfaces to DbContext class – creates chicken-egg situation because DbContext needs querying classes to be injected and query classes need DbContext to be injected.

Of approaches mentioned above only to are worth to experiment: extension methods for DBSet<T> and querying classes that are injected to DbContext.

Extension methods for DbSet<T>

Extension methods are easiest ones to implement. They work until we stay with pure LINQ. In case of multiple database types and custom SQL queries this approach doesn’t work. Let’s write extension methods for DbSet<Customer>.

public static class SalesDbContextCustomersExtensions
{
    public static IQueryable<Customer> ListDebtors(this DbSet<Customer> customers)
    {
        return customers.Where(c => c.Balance < 0);
    }
 
    public static IQueryable<Customer> ListNewRegistrations(this DbSet<Customer> customers, DateTime fromDate)
    {
        return customers.Where(c => c.Created >= fromDate);
    }
}

We have separate class with extension methods and we don’t bloat DbContext with queries. Still these extension methods are easy to use.

public void DoSomething()
{
    var debtors = dbContext.Customers.ListDebtors();
 
    // ...
 
    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.Customers.ListNewRegistrations(date);
}

Those who want to isolate direct access to DbSets can’t use extension methods as marking DbSets protected restricts external access to them.

If you want to go with extension methods then here few more ideas what you can do in data layer: Implementing Query Specification pattern in Entity Framework Core and Readable fluent queries with Entity Framework Core.

Injecting querying classes to DbContext

If extension methods are not an option then we have to introduce querying classes that are injected to DbContext. Like mentioned above there’s a chicken and egg problem, but it’s not hard to solve. Let’s start with intrface and class for querying customers. We use interface because we probably want to use fake version of querying class when writing unit tests.

public interface ICustomerQueries
{
    void SetDbContext(SalesDbContext dbContext);
 
    IQueryable<Customer> ListDebtors();
    IQueryable<Customer> ListNewRegistrations(DateTime date);
}
 
public class CustomerQueries : ICustomerQueries
{
    private SalesDbContext _dbContext; 
 
    public virtual IQueryable<Customer> ListDebtors()
    {
        return _dbContext.Customers.Where(c => c.Balance < 0);
    }
 
    public virtual IQueryable<Customer> ListNewRegistrations(DateTime date)
    {
        return _dbContext.Customers.Where(c => c.Created >= date);
    }
 
    public void SetDbContext(SalesDbContext dbContext)
    {
        _dbContext = dbContext;
    }
}

Notice two things. SetDbContext() method – we have to assign DbContext when instance of ICustomerQueries is injected to DbContext. Otherwise there’s no connection between these two classes. Customer queries class has virtual methods. We can keep there pure LINQ methods and extend this class if we need let’s say SqlServerCustomerQueries class that overrides some methods to use SQL Server specific SQL commands.

Here’s our DbContext after changes.

public class SalesDbContext : DbContext
{
    public SalesDbContext(DbContextOptions<SalesDbContext> options, 
                          ICustomerQueries customerQueries) : base(options)
    {
        CustomerQueries = customerQueries;
        customerQueries.SetDbContext(this);
    }
 
    public DbSet<Customer> Customers { get; set; }
 
    // Other DbSets
 
    public ICustomerQueries CustomerQueries { get; private set; }
 
    // Other queries
}

In assigns itself to DbContext property of all injected querying classes. It’s easy but still something we have to remember to do when introducing new quering class for some DbSet<T>.  Our previous dummy demo method looks now just a little bit different than before.

public void DoSomething()
{
    var debtors = dbContext.CustomerQueries.ListDebtors();
 
    // ...
 
    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.CustomerQueries
                                .ListNewRegistrations(date);
}

For ASP.NET Core we have to add querying interface and class to dependency injection.

services.AddScoped<ICustomerQueries, CustomerQueries>();

Now we have a bit inconveniet DbContext that actually acts as an unit of work with local instances of repositories.

Supporting database-specific commands

As I previously mentioned we need sometimes database-specific commands. These commands can contain SQL that is understood only by one supported database. It’s clear we cannot run these commands against other types of databases.

Database-specific commands are usual in applications that have reporting. Some ideas about how to implement common reporting with Entity Framework Core are given in my posts Execute raw SQL commands in Entity Framework Core and DataSet and DataTable based ad-hoc reporting with ASP.NET Core.

This is customer queries class we created above with one little change – DbContext is now available for inheriting classes.

public class CustomerQueries : ICustomerQueries
{
    protected SalesDbContext dbContext { get; private set; }
 
    public virtual IQueryable<Customer> ListDebtors()
    {
        return dbContext.Customers.Where(c => c.Balance < 0);
    }
 
    public virtual IQueryable<Customer> ListNewRegistrations(DateTime date)
    {
        return dbContext.Customers.Where(c => c.Created >= date);
    }
 
    public void SetDbContext(SalesDbContext context)
    {
        dbContext = context;
    }
}

We marked querying methods as virtual and there was a reason to do so. Suppose ListDeptors() method is tricky and we need custom SQL for this when using MySQL.

public class MySqlCustomerQueries : CustomerQueries
{
    public override IQueryable<Customer> ListDebtors()
    {
        return dbContext.Customers.FromSql("MySQL specific SQL");
    }
}

Same way we can also build specialized classes for SQL Server, Oracle, Postgre etc.

Mocking querying classes in unit tests

Querying classes we inject using their interfaces can be easily mocked in unit tests. Suppose we have CustomersService class with kind method to zero all debts.

public class CustomersService : ICustomersService 
{
    private readonly SalesDbContext _salesDbContext;
 
    public CustomersService(SalesDbContext salesDbContext)
    {
        _salesDbContext = salesDbContext;
    }
 
    public void ClearDebts()
    {
        var debtors = _salesDbContext.CustomerQueries.ListDebtors();
 
        foreach(var debtor in debtors)
        {
            debtor.Balance = 0;
        }
 
        _salesDbContext.SaveChanges();
    }
}

Using xUnit and Moq we can write the following test class to check if ClearDebts() sets all debts to zero.

public class CustomerServiceTests
{
    private Mock<ICustomerQueries> _customerQueriesMock;
    private SalesDbContext _dbContext;
 
    public CustomerServiceTests()
    {
        _customerQueriesMock = new Mock<ICustomerQueries>();
 
        var options = new DbContextOptionsBuilder<SalesDbContext>()
                                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                                .Options;
 
        _dbContext = new SalesDbContext(options, _customerQueriesMock.Object);
    }
 
    [Fact]
    public void ClearDebths_should_set_debtors_balance_to_zero()
    {
        var service = new CustomersService(_dbContext);
        var debtors = new List<Customer>
        {
            new Customer { Id = 1, Name = "John Doe", Balance = -20 },
            new Customer { Id = 2, Name = "Jane Doe", Balance = -5 }
        };
        _customerQueriesMock.Setup(c => c.ListDebtors())
                            .Returns(debtors.AsQueryable());
 
        service.ClearDebts();
 
        Assert.Equal(0, debtors[0].Balance);
        Assert.Equal(0, debtors[1].Balance);
    }
}

In real world we should have more tests but for illustration this one is okay. We can create class-level mocks also for other querying classes we need in DbContext. Those who don’t like mocks can use stub classes. It’s easy because querying classes have interfaces.

How this approach works in practice?

After getting to point where I was kind of okay with the solution, of course I was eager to try it out on some real project. Luckily I have one application under development that is perfect candidate for test-drive. It’s very proccess-centric having complex service classes that make heavy querying.

Some characteristics to understand the system I’m using as lab rat:

• two web application projects
• one project with backgroud services
• shared projects for business logic and data layers
• database with ~40 tables (3 tables with many fields)
• ten service classes (few of them really big)

I’m using DbContext in applications through interface meaning that application see only what they need to see. I can restrict direct access to DbSet<T> properties and introduce generic Get, Save and Delete methods if needed. Querying classes form nice layer that handles all queries and I can build additional layers behind these if demands on querying happen to grow.

Leaving out the inconvenience on assigning DbContext to querying classes after they are injected to DbContext, I’m okay with my solution. Also it makes unit tests smaller and cleaner. I can easily mock querying objects and I don’t have to write code to insert correct test data to DbContext to make queries return the results that tests expect.

Wrapping up

Getting repositories querying interface to DbContext the way that we may have multiple implementations wasn’t very easy task. To be flexible we had to create querying classes, inject these to DbContext and assign DbContext to their data context property. Even if can safely extend DbSet<T> the problem will be the same – DbSet<T> needs to know DbContext. We created flexible query classes. There’s base class that hosts all pure LINQ-based querying methods. If we don’t have anything database specific then we can use these base classes. If we have something database-specific then we can extend database-specific querying classes from base class and override methods that need custom SQL. We have now DbContext that works as unit of wort containing set of repository instances.