I have an ASP.NET Core Web API and services. To abstract EF Core's DbContext from service layer, I decided to use the repository pattern.

However, the repository contains logic that cannot be unit tested with mock libraries (I tried Moq) or with in-memory database provider.

So my question: is it actually a unit test if it uses the real database or is it an integration test? Am I using repository pattern wrong since I cannot unit test it with mock or in-memory database?

Here is some code from my repository which is problematic to mock or to use in-memory provider:

public class UrlRepository(AppDbContext dbContext, IMemoryCache memoryCache) : IUrlRepository
{
    private readonly AppDbContext _dbContext = dbContext;
    private readonly IMemoryCache _memoryCache = memoryCache;

    public virtual async Task<int> DeleteUrlAsync(Guid urlId)
    {
        int rows = await _dbContext.Urls
            .Where(u => u.Id == urlId)
            .ExecuteDeleteAsync(); // Cannot mock it and cannot use InMemory provider since exception will be thrown (See here ).

        if (rows > 0)
        {
            _memoryCache.Remove($"{CacheKeys.UrlId}-{urlId}");
        }

        return rows;
    }

    public virtual async Task<PaginationDto<UrlDto>> GetAllUrlDtoAsync(int pageNumber, int pageSize)
    {
        var query = _dbContext.Urls.AsNoTracking(); // Cannot mock it since it is an extension method

        var totalCount = await query.CountAsync();
        var totalPages = (int)Math.Ceiling((double)totalCount / pageSize);

        var urls = await query
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .Select(UrlEntityProjection.UrlDto)
            .ToListAsync();

        return urls.ToPagination(u => u, totalCount, totalPages, pageNumber, pageSize);
    }
}

As you can see the repository contains code that cannot be mocked or used with in-memory provider.

For Integration testing I'm using the TestContainers library. But should I use it just to test this one class? For now I came up with solution to use:

SQLite database -> apply pending migrations -> delete database at the end of the test

I have an ASP.NET Core Web API and services. To abstract EF Core's DbContext from service layer, I decided to use the repository pattern.

However, the repository contains logic that cannot be unit tested with mock libraries (I tried Moq) or with in-memory database provider.

So my question: is it actually a unit test if it uses the real database or is it an integration test? Am I using repository pattern wrong since I cannot unit test it with mock or in-memory database?

Here is some code from my repository which is problematic to mock or to use in-memory provider:

public class UrlRepository(AppDbContext dbContext, IMemoryCache memoryCache) : IUrlRepository
{
    private readonly AppDbContext _dbContext = dbContext;
    private readonly IMemoryCache _memoryCache = memoryCache;

    public virtual async Task<int> DeleteUrlAsync(Guid urlId)
    {
        int rows = await _dbContext.Urls
            .Where(u => u.Id == urlId)
            .ExecuteDeleteAsync(); // Cannot mock it and cannot use InMemory provider since exception will be thrown (See here https://stackoverflow/a/74907535/23143871).

        if (rows > 0)
        {
            _memoryCache.Remove($"{CacheKeys.UrlId}-{urlId}");
        }

        return rows;
    }

    public virtual async Task<PaginationDto<UrlDto>> GetAllUrlDtoAsync(int pageNumber, int pageSize)
    {
        var query = _dbContext.Urls.AsNoTracking(); // Cannot mock it since it is an extension method

        var totalCount = await query.CountAsync();
        var totalPages = (int)Math.Ceiling((double)totalCount / pageSize);

        var urls = await query
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .Select(UrlEntityProjection.UrlDto)
            .ToListAsync();

        return urls.ToPagination(u => u, totalCount, totalPages, pageNumber, pageSize);
    }
}

As you can see the repository contains code that cannot be mocked or used with in-memory provider.

For Integration testing I'm using the TestContainers library. But should I use it just to test this one class? For now I came up with solution to use:

SQLite database -> apply pending migrations -> delete database at the end of the test

• c#
• unit-testing
• entity-framework-core
• moq
• repository-pattern

• 2 Just as an aside: EF already implements the repository/unit-of-work pattern. Why would you wanna wrap it with the same? – Good Night Nerd Pride Commented Mar 9 at 21:13
• 1 @GoodNightNerdPride When I write my Unit tests for service layer which contains EF Core DbContext I always need to mock the DbContext, DbSets and etc... and then mock the other stuff of the service. I came to repository pattern because it easier to mock it in service layer (just mock the method) and stick with service layer logic rather than on both: dbContext and service. – Quikler Commented Mar 9 at 21:19
• I am a newbie and of course any tips would be appreciated. – Quikler Commented Mar 9 at 21:20
• As you can see the repository contains code that cannot be mocked or used with in-memory provider. Be explicit about which code you think can't be mocked. – mjwills Commented Mar 9 at 22:11
• 2 integration test the repository, unit test stuff higher up that depends on the repository – Ivan Petrov Commented Mar 9 at 22:42

3 Answers 3

So my question: is it actually a unit test if it uses the real database or is it an integration test?

No-one can answer that question, because it presupposes that there's a universally agreed definition of what a unit test is. There isn't. There are well-established, respected experts in automated testing that will call this a unit test, and others who will insist that it isn't.

Personally, I tend towards the latter, but acknowledge that there are people with at least as much experience as mine who hold the opposite position.

What may be more relevant is this question: How does this kind of test impact me? Are there downstream consequences of my decisions of which I should be aware?

The answer to the latter of these two questions is: Yes, you should be aware of the following.

A test that touches the real database tends to be slower, more brittle, and in general require more maintenance.

It's not wrong to have such tests. In fact, the Test Pyramid encourages having a few of these. On the other hand, you probably don't want to have too many of such tests, because they are slow, brittle, and require more maintenance than a test that runs entirely in memory.

As you can see the repository contains code that cannot be mocked or used with in-memory provider.

No, it doesn't, really. It contains code that's difficult to unit test in memory because you made a particular implementation choice. Specifically, you've chosen to use Entity Framework, which is not inescapable.

You may find Entity Framework useful for your particular context, but it's a piece of technology that comes with a certain set of trade-offs. No-one forces you to use it.

I rarely do, because I consider ORMs detrimental to productivity, but the bottom line is that it's a choice. There are always alternatives.

When a repository pattern is introduced to help facilitate unit testing then the repository abstraction should be kept thin so there is no business logic within it. An example would be a repository that returns IQueryable<TEntity>. The business logic of "I want DTOs", "I expect this sorting", "I want this paginated" exists outside of the repository abstraction, meaning you can test them. This begs the question "why use a repository at all? Just use the DbSet<TEntity>." Which the answer is that having the repository exposing IQueryable<TEntity> makes it simple to substitute real data with a list. (See MockQueryable for ensuring the returned data is suited to async operations). A lot simpler than trying to mock DbSet.

If you want to centralize data access to enable caching or avoid references to EF in order to work with IQueryable then you can introduce a service around it to provide that common implementation such as to satisfy CQRS or such (Define the commands and queries) rather that building that into the repository and making them difficult to test.

Otherwise, testing that the repository does actually serve the data expected in the way expected would fall to an integration test which should be running with the same RDBMS as you are using in production. These tests naturally will be slower, needing more setup, and likely running sequentially to avoid data collisions and/or multiple database instances.

So my question: is it actually a unit test if it uses the real database or is it an integration test? Am I using repository pattern wrong since I cannot unit test it with mock or in-memory database?

Strictly speaking when you test a method if you don't mock or stub all of it's external dependencies and test the method's logic in pure isolation this is an integration test. But in general this shouldn't be problem for you. After all the end goal of writing unit or integration tests is to allow you to confidently make changes (improvements) to your code as time goes by and at the same time to be relatively confident that these newly introduced changes don't break the existing functionality by running tests that correctly indicate if the system under test behaves as expected or not (Pass or Fail). And this should be achieved with no or minimal changes on the tests themselves since frequently amending tests most likely will lead to bugs or errors in the tests. This must be your main aim when testing your app not whether your tests are pure unit test. Pure unit tests e.g. testing all (or almost all) methods in isolation with each dependency mocked or stubbed out, are normally a lot fragile the smallest code changes lead to serious changes in the tests. Also frequently they end up testing an unrealistic test/use case when every dependency is mocked and the method runs in a total isolation. This is somewhat opposite to the main goal of testing which is solid and stable tests that correctly indicate if something is broken and that don't provide you with a ton of false negative or false positive results. To achieve this the best way is to take a more higher level integration approach of testing your app (especially it it is an asp core web application with a database) e.g. not to mock your database repositories but rather than that use sql server localdb with pre seeded test data in it.

For more insights on which is the correct testing approach you should follow when writing tests for web apps/web apis I strongly recommend you to read this article TDD is dead. Long live testing.

Just one quote from it

I rarely unit test in the traditional sense of the word, where all dependencies are mocked out, and thousands of tests can close in seconds. It just hasn't been a useful way of dealing with the testing of Rails applications. I test active record models directly, letting them hit the database, and through the use of fixtures. Then layered on top is currently a set of controller tests, but I'd much rather replace those with even higher level system tests through Capybara or similar.

and this is exactly how Microsoft recommends testing Web Apis on a higher integration level (i.e. testing each controller method together with the repository service method) with a database query invloved Testing against your production database system

public class TestDatabaseFixture
{
    private const string ConnectionString = @"Server=(localdb)\mssqllocaldb;Database=EFTestSample;Trusted_Connection=True;ConnectRetryCount=0";

    private static readonly object _lock = new();
    private static bool _databaseInitialized;

    public TestDatabaseFixture()
    {
        lock (_lock)
        {
            if (!_databaseInitialized)
            {
                using (var context = CreateContext())
                {
                    context.Database.EnsureDeleted();
                    context.Database.EnsureCreated();

                    context.AddRange(
                        new Blog { Name = "Blog1", Url = "http://blog1" },
                        new Blog { Name = "Blog2", Url = "http://blog2" });
                    context.SaveChanges();
                }

                _databaseInitialized = true;
            }
        }
    }

    public BloggingContext CreateContext()
        => new BloggingContext(
            new DbContextOptionsBuilder<BloggingContext>()
                .UseSqlServer(ConnectionString)
                .Options);
}

public class BloggingControllerTest : IClassFixture<TestDatabaseFixture>
{
    public BloggingControllerTest(TestDatabaseFixture fixture)
        => Fixture = fixture;

    public TestDatabaseFixture Fixture { get; }

[Fact]
public async Task GetBlog()
{
    using var context = Fixture.CreateContext();
    var controller = new BloggingController(context);

    var blog = (await controller.GetBlog("Blog2")).Value;

    Assert.Equal("http://blog2", blog.Url);
}

In short they use a LocalDB database instance, seed data in this instance using the test fixture and executing the tests on a higher integration level i.e. calling the controller method which calls a service(repository) method that queries the Blogs dbSet on the dbContext that executes a Sql query to LocalDB that returns the seeded data.

In conclusion: You should focus to write the type of tests that test your app's code the best and provide you with consistent and correct results no matter if they are called unit or integration tests. A solid approach I have seen in several asp core web api applications is to test most of your app's code in integration (the sample from Microsoft above) but to mock external dependencies such as calls to third party APIs, some third party services like for example mocking an Azure BlobStorage service, etc..

Another quote from TDD is dead. Long live testing.

I think that's the direction we're heading. Less emphasis on unit tests, because we're no longer doing test-first as a design practice, and more emphasis on, yes, slow, system tests. (Which btw do not need to be so slow any more, thanks to advances in parallelization and cloud runner infrastructure).