How to unit test database access logic?

Question

I have a project based on EF Code First using MSSQL server. In one of my repositories I'm dynamically building a query using PredicateBuilder. To test all possible outcome manually every time something changes in the code, is very time consuming.

For that reason, I'd like to automate this by unit testing it. I was thinking about using sql compact for unit test and MSSQL server for production. But how would one enable migrations for sql compact server?

This is dbContext class:

public partial class ApplicationDbContext :
        IdentityDbContext<ApplicationUser, ApplicationRole, int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>
{
    public ApplicationDbContext() : base("name=DefaultConnection") { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<ApplicationDbContext, Configuration>());
        base.OnModelCreating(modelBuilder);
     }
}

Repository:

public class FilterRepository : IFilterRepository
{
    private ApplicationDbContext _dbContext;

    public FilterRepository(ApplicationDbContext dbContext)
    {
        _dbContext = dbContext;
        DbInterception.Add(new CommandInterceptor());
    }

    public IEnumerable<Person> GetPersons(Filter filter)
    {
        try
        {
            var persons = PredicateBuilder.False<Person>();

            _dbContext.Configuration.AutoDetectChangesEnabled = false;

            var result = _dbContext.Persons
                            .AsNoTracking() 
                            .Where(persons)
                            .OrderBy(x => x.Name)
                            .Skip(filter.Skip)
                            .Take(10)
                            .ToList();

            _dbContext.Configuration.AutoDetectChangesEnabled = true;

            return result;
        }
        catch (Exception ex)
        {

        }
    }
}

Answer 1

ok, so switching to a different database for testing is (imo) a bad idea.

Here is some terminology for you, that might help you to organize your tests better.

A Unit test is for testing your business functionality, generally you would just mock your data for this, as you want to test what happens to your inputs (data) within your domain classes, not where they came from.

An Integration test is for testing how your business domain layer interacts with your service layer (your data layer is a service layer), I would also regard testing the correctness of a query as an integration test for purposes of running them.

So in your case, don't confuse things and potentially add unexpected behavior caused by differences in how the 2 databases work. If you are testing your business functionality mock your data for that. Its faster for a start, and you will be testing, just what you want to be testing.

With your integration testing, you are testing that your interaction with the service layer is correct, in this case your test is not really testing that the business logic works, that should be covered in the unit tests; Test that your queries return the correct data for its predicates, and that any data that is to be persisted is persisted correctly. Also that any transactions in play are working as you expect. End to end testing of a scenario is also a valid integration test.

You absolutely need to be doing this on the same database platform as your production, don't expect SQL Compact and SQL Server to behave the same way.

Edit...For the comment.

So the normal way that you would Mock your repository call is to use dependency injection, you don't have to do it this way, but its easier and pretty much best practice.

The idea is that in your domain class that uses your data, you would first get your repository, or query class from the DI container that was either injected into the constructor or pulled from the DI container

// in your domain class you would have something like...
    var repo = container.Get<IRepository>();
    var myList = repo.GetMyObjects(predcate);

So with Moq your could now simply mock that call

//Where you do your container registration..
var repo = Mock<IRepository>
repo.Setup( o => o.GetMyObject(predecate)).Returns( (predecate) => <your dummy list>));
container.Register(repo.Object);
// Then later on your business domain object gets the dummy repo instead.

Note this is pseudo code, will be slightly different per DI and Mocking frameworks used.