Automated Testing with Entity Framework 6

In software development, testing applications and troubleshooting takes up a large part of the time. If the modules of a software are clearly separated by loose coupling of dependencies, the software can be tested in isolation using Dependency Injection and Mock objects. However, if external resources are used by the software, such as USB devices or databases, automated testing becomes more difficult. So this blog post is about testing a database connection with Entity Framework 6.

A simple ASP.NET MVC application

Fig. 1 shows the rough software architecture of a simple ASP.NET MVC App. The app consists of several layers with their respective defined task areas. Each of the four layers only knows the layer above it and can only access the functionalities of this layer (symbolized by the direction of the arrows).

The Controller Layer receives web requests, validates input data, authorizes users, intercepts service layer exceptions, and generates error messages for the user.

The Service Layer is responsible for the actual app logic and uses the Repository Layer to query, store and edit records.

The Repository layer encapsulates the access to a database and abstracts the type of database (SQL- or document-based database) as well as the access to the database (e.g. via an ORM like EF6 or NHibernate).

Fig. 1: ASP.NET App Software Architecture 

This article is about testing the repository layers that access an SQL database through Entity Framework 6. Since we also want to implement complex queries using the Entity Framework, it is helpful to be able to test them automatically. The numerous advantages of unit tests are already discussed in detail in another blog post, which is why it will not be repeated here again. Strictly speaking, our repository layer tests are not unit tests, but integration tests because we access a database and test more than one layer of our software at a time.

A simple Test

Fig. 2 shows a very simple test, divided into the three areas Arrange, Act and Assert. MSTest is used here as the test framework, which can be seen from the attribute decorations of the [TestClass] class and the [TestMethod] method. In the first section Arrange all necessary preconditions for the test are generated - the creation and saving of a new ApplicationUser (lines 28 and 29).

Note: In order to display the logic of the test transparently, the repository layer is omitted here and the DbContext of the Entity Framework is accessed directly. When using the repository layer, the DbContext would be injected into this layer as a dependency.

The code to be tested is located in lines 32 and 40. An attempt is made to add and save a new document. However, the new document does not comply with the rules of the database because the required FilePath and Content properties are not set. Therefore, we expect an exception to be displayed when saving the changes. So here we test the schema of the database and the desired response to inserting incorrect data.

Note: The NuGet Package FluentAssertions is used here for the asserts.  

Fig. 2: A simple Test

The Test runs - Only once

The test in Fig. 2 is executable and the check mark between lines 18 and 19 symbolizes that the test was successful. Unfortunately, in this case the test is only successful once. Another test run leads to an erroneous result, since an exception is generated from the database in line 29. This happens because the "TestUser" was already added to the database during the previous test run and the UserName is provided with a unique index (see Fig. 3).

Fig. 3: Table of the database in SQL Server Management Studio

By executing our tests, the database is manipulated so that it is no longer in a defined state when a new test run is started. The database should therefore be completely empty at the beginning of each test.

Always with empty Database

In order for several competing tests to run simultaneously, data records should never be persistently stored in the database. This desired behavior can be achieved by running all database operations of a test in one transaction. At the end of the test, the transaction is rolled back so that none of the changes are visible outside the transaction. In order to keep the complexity of the individual tests as low as possible, we create a simple base class (Fig. 4) and inherit our test classes from it.

Fig. 4: Base Class for Tests with the Entity Framework

By inheriting from the base class, a TransactionScope object is created before each individual test run, so that all operations run through the Entity Framework in a transaction that is never persisted. In addition, two DbContext objects are created for testing and generating the required test scenario. Of course, further DbContext instances can also be used in the tests.

Create a Database

In order for the DbContext in our test project to be able to connect to a database at all, the connection string must still be configured in the App.config file (Fig. 5) of the test project. The LocalDB is completely sufficient for the automated tests. If the database is not yet available on the test system, the test project must ensure that the database is created and migrated to the current schema. This would of course be possible as a manual step via the PMC (Package Manager Console), but would mean that the tests would not run successfully with Continuous Integration on an agent.

Fig. 5: Connection String for LocalDB

To ensure that the local test database always schematically corresponds to the status of the Entity Framework, an additional class is introduced (see Fig. 6). The class contains only one method, which is executed once before each run of the test runner (method decorated with Assembly Initialize). In this method, our migrations from the Entity Framework are applied to the database.

Fig. 6: Creating and Migrating of the Database 

Conclusion

Our tests are running against an empty local database. So we can automatically test the behavior of our software, based on our database schema, via the Entity Framework. The tests are also executable in Continous Integration solutions and can be executed - for example with the "Hosted VS2017" Agent of the Visual Studio Team Services - successfully.

The automated testing of applications saves a lot of time, as the effort for manual test procedures is reduced. In addition, errors that occur, for example, when entering new changes, can be detected at an early stage.

Title Photo Credit: https://pixabay.com/de/chemie-lehrer-wissenschaft-labor-1027781/