The problem

We have created are data layer, and of course we want to test it.

It is very hard to use a (physical) database to test your code. There are some problems with this (commonly used) approach:

• Unit tests must be fast, fast, fast. If you include database access in the tests then the execution time of your unit tests will be much slower, hence your tests won’t be run regularly anymore and they become less useful.
• You don’t know the state of your test database. Maybe somebody has run some tests before you, and the database is not in the expected state for your tests. Or maybe the order that your tests are executed in is not always the same. Adding or removing tests can mess this order up easily. So your tests are not deterministic and again pretty much useless now.
• You don’t want to change the state of your database during the testing. Maybe someone in your team is using the same database at the time that you are running your tests, and they may not be pleased with it.

Of course there are solutions for these problems:

• From time to time you may want to run some integration tests. For this you can set up a dedicated database to work with. Before running your tests you can then restore the database in a known state so at least that part will be deterministic. You can then run your tests in a specific order (or cleanup after each test, which requires more code, but is more foolproof). There are (again) some problems with this approach:
  • Your databases will evolve. You will probably add tables, procedures and other objects to your database. The database that you have backed up will not have these changes yet, so some maintenance is required.
  • It can be slow to run all your tests. Typically integration tests are executed before going live with your project, and often this is done automatically at night, giving you a detailed report in the morning. If possible, it is a good idea to execute the integrations tests every night. You can then discuss the problems during your daily standups.
  • You do need integration tests anyway, because sometimes your database will act different from your mocked objects and you can’t afford to find this out in production.
• From EF7 on an in-memory database will be provided that will allow you to test your database code fast and deterministic. This article will not cover that (because otherwise I don’t have anything left to write about anymore😉
• You can mock your data tables to unit test your repositories. That will be the subject of my next post.

What (not) to test?

In this post I will assume that you use Entity Framework (EF) to implement your repositories. Some of the code in your repository will be very straightforward, and only involve some basic EF functionality (like LINQ queries, …). EF is a third party (Microsoft) library that has been tested by Microsoft, so you don’t have to test EF functionality. I’m usually using a very simple rule: do I get it a method working correctly from the first (or second😉 time? Then it probably doesn’t require a test.

So we want test the more complex methods in the repository that will perform calculations, update multiple tables, …

Setting up your repository

Design for testing (SoC)

Let’s say that you want to perform some wild calculations over a table. You get the rows from that table and then with the entities you perform your calculation.

But your calculation has nothing to do with the database, all it needs is a collection of objects (entities) to perform its work. So it may be a good idea to add a (private) function that will perform the calculations on an IEnumerable<T>. Testing the calculation is now easy (you just create some lists in your tests and perform the calculations). Testing the whole function may have become unnecessary now.

Separation of concerns, grasshopper😉

Design for testing (Dependency Injection – DI)

Initially we will write the repository like:

    public class EventsRepository : IEventsRepository

    {

        private readonly PlanningEntities db = new PlanningEntities();

      // …

   }

This will tie the EF context hard to the repository. The code is simple and clean, but very hard to test. So a better way would be to inject the EF context, and provide a default when you don’t. Example:

    public class EventsRepository : IEventsRepository

    {

        private readonly PlanningEntities db = new PlanningEntities();

        public EventsRepository()

        {

            _db = new PlanningEntities();

        }

 

        public EventsRepository(PlanningEntities db)

        {

            _db = db;

        }

      // …

   }

In this example there are 2 constructors. The default constructor will be used in your controller and will instantiate the repository using the EF context. The second constructor can be used in your test code to pass the mocked context into the repository. In the repository nothing further needs to be modified, unless you use more exotic functionalities (like stored procedures etc). In the functions using the repository nothing needs to be modified either (thanks to the default constructor).

Of course using a dependency injection container like Unity can help a lot with setting up your DI. It will depend on your needs if you need this or not. Usually when your project grows, you will need it at some point.

Caveats

You can’t mock stored procedures, user functions, triggers, … By nature these are written in some form of SQL (T-SQL, PL-SQL, …) and they usually contains multiple statements that will be executed directly against the database engine. If you use them, you’ll need to integration test your code. This doesn’t mean that stored procedures are bad and should not be used, just that you need to be aware of this limitation.

You can’t mock default values for fields, calculated fields, … Identity fields (sequences in Oracle, Autonumber in MS Acces) are a good example of this. They self increment with each newly created record. The same goes for unique identifiers (GUIDs) of course. There are also the typical fields like [CreationDate] that will get the current date and time by default when a record is created. You’ll need to write some extra code in your mocks to cope with these.

Sometimes fields are calculated when a record is updated. Typically there will be a [LastUpdated] field that will be set by an update trigger when the record is updated. For performance reasons some tables may be denormalized, and maintained by triggers. This will also require extra work in your mocks.

Foreign keys may be a problem as well. If you don’t have all the tables in your model, and a table has a foreign key to one of these not-included tables, your mocks can’t catch this either.

Conclusion

Writing testable code requires some architecture. You must think about how your code will be tested, and how this can be done as efficient as possible. You must also accept that you can’t mock everything, so integration tests will be necessary.

I actually started this post with the idea to introduce Effort to you, an nice library to mock your EF classes. But while writing I decided to postpone this to next week’s post.

References

https://en.wikipedia.org/wiki/Separation_of_concerns

Introduction to Unity