How to test methods for working with DB?

Question

In my application, I use the entity-framework version 6.1.3 to work with the mssql database.

The question of covering your code with tests is ripe.

Tell me, please, testing methods for this bundle.

It occurs to me to create a local test database, fill it with data and test methods on this database, with this approach I will be able to test both the data acquisition methods and the CRUD methods.

I also heard that to test the methods for working with the database, you can create a generic repository of the public interface IRepository<T> where T: class { } and for testing slip a fake repository with test data.

I still lean more towards the first scenario, i.e. creating a local test database that will be subject to the same changes as the real database (due to the migration mechanism in ef).

Tell me how correct will be testing in the way I described? I will be glad to hear about other ways.

Accepted Answer · 2016-08-26T06:36:32

Testing is a process of research and testing of software (software) that has two main objectives: to make sure that the software is working and meets the requirements, and also to identify situations in which the behavior of the software is wrong, undesirable or does not meet the initial requirements.

You are considering two options for testing. In order to identify more suitable, it is necessary to find out the pros and cons of both approaches and starting from the results obtained to make a choice. I will only advise you, and you will choose.

Option 1

Creating a local test database that will be subject to the same changes as the real database (due to the migration mechanism in EF)

This approach may exist, but looking at it there are questions, are you ready to sacrifice some of the shortcomings of this approach? The disadvantages I would consider:

Direct dependence of tests on data stored in the database
Intersect data for multiple tests. There are such situations in tests, when we imitate a mistake or expect wrong behavior - this is quite normal. But what to do in this case? We will have data both good and bad .
Dependence on migrations. There are several sub-items. First : if we forget to do the migration, the tests will fall. Second : you must always remember that you need to make a migration for tests. Third : support of such tests. What will the new programmer do?
What if the database suddenly fell ? Tests do not work again.
This approach can not be called simple, there are difficulties.
I think that this should also be entered, I saw in the comments: To check the migration, you can always keep a backup of the required version

Option # 2

To test the methods for working with the database, you can create a generic repository of the public interface IRepository where T: class {} and for testing, slip the fake repository with test data.

This is a more convenient test option, in my opinion. Let's look at why:

We are not dependent on the data in the database, since the data is prepared in the tests themselves.
The data does not overlap, we always know that tests only work with what we give them.
Imitation of situations when we expect an error does not break our tests and does not lead to problems.
Ease of use. No need to do migrations. There is a lot of documentation that will illustrate examples of using mocks (fakes).
Speed testing There is a very big advantage in mocks (fakes). They work much faster.

There is a certain lack of this approach, as I was told - this is testing logic that is embedded in the database: indices, competitive access, etc.

Option # 3

You can combine these approaches, to bring something intermediate. For example, before starting, writing data to the database is data preparation. Then work with a specific database in which our data lie, that is, a kind of database for tests. The main thing to remember to clean up after you is to delete the data used for the test. This approach is rather cumbersome and also not easy.

Using this approach involves writing auxiliary bun, which will help . This will help test CRUD methods. But here another question arises: who will test the auxiliary utility? That is, this approach is the place to be.

Well, one more question: are you going to test your code or EF?

If you lead the development of Test Driven Development , the use of fakes greatly simplifies development.

I do not want to specifically focus my attention on one option, I just want to advise you to make a choice that will be more correct and will not cause problems in the future. So that after a while you do not look back at the work done and say to yourself, “ Why didn’t I go the other way? ”.

The choice must be made based on the task. Using only one approach is unlikely to work out - most likely you need to use several approaches.

and if you combine these two approaches as written by @Monk, i.e.
for each test, insert test data into the database and go after it, but this test will take longer, but in some cases it will be more complete?
The dependence on the database remains (the network used to be cut off and there was no access - everything fell), and the speed is also less than mocks.
As a minus of the second method, the fact that you are not testing EF does not mean that everything is fine with EF.
Sometimes any ORM cache can mess up a completely valid fake code.
life example var currentActState = _context.Set<Act>().Where(x=>x.Id==actid).Include(x=>x.History).Single().Select(x=>x.History.Last()) when testing on the test data returns the correct result, the same query in the database behaves incorrectly in some cases, adding explicit sorting before Last() problem is solved, so I want to test it in conjunction with the database
@DenisBubnov main minus option # 2 - minor differences in the work of Linq to SQL / EF and Linq to Objects (moki).
And vice versa - the Linq to Objects code will quietly swallow Func instead of Expression, which, at the desired level of bad luck, subtracts half the base into memory.
the problem of this method is not so much in the absence of coverage for the indices (nothing will cover them), but in the leakiness of LINQ abstraction.

Answer 2 · 2016-08-26T07:42:19

The methods listed by you relate to different types of testing. Creating a database and requests for it - integration testing. Creating a database, filling it up, passing tests will all take a lot of time and you don’t want to run these tests every minute. On the other hand, these tests can be included in the build process (integration, continuous integration). If you do not have a continuous integration server, run them manually, just less often. To do this, mark them with the TestCategory attribute, or put everything in one assembly: Visual Studio will allow you to run tests from a single project or just one category.

The second option is unit testing. It is the unit tests that are run continuously and they, naturally, do not have to perform lengthy operations: access files, access network resources, and the database server. It is in these tests that mock and stub objects are used (they are fake).

Therefore, my answer is: you have to do those and other tests. Now how to do it.

Unit tests

Very often, programmers ask the question - how do I modularly test the Entity Framework? Answer: in any way, its developers write unit tests on the Entity Framework, and you write tests on your code. It is also a mistake to write complex tests that are much more complicated than your code.

 public class EfUserRepository : IUserRepository { private readonly IDbContextFactory dbContextFactory; public EfUserRepository(IDbContextFactory dbContextFactory) { this.dbContextFactory = dbContextFactory; } public UserData ReadById(int id) { using (var dbContext = _dbContextFactory.Create()) { return dbContext.Users.Single(u => u.Id == id); } } }

What can you really test in this code? Immediately I want to test the work of Single and make sure that the method returns a single answer or throws an exception. But Single is not your code, moreover, for some reason, it is rather difficult to test its call, because it is an extension method, not an IQueryable interface IQueryable . But you can check that the dbContextFactory method will call Create and that the context created has a call to the Users property. Here’s how you can do this simply with the Moq library.

 public void ReadById_WhenCalled_CallsDbContextFactoryCreate() { var dbContextFactoryMock = new Mock<IDbContextFactory>(); var dbContextFactory = dbContextFactoryMock.Object; var userRepository = new EfUserRepository(dbContextFactory); var user = userRepository.ReadById(1); dbContextFactoryMock.Verify(x => x.Create()); }

Integrating testing

Here you need a base and real requests. It is in integration testing that you check the code as a whole - create a record in the table, then read it and make sure that it is there.

Your question is how to fill this database, and there are two main ones here: you can either separately fill the database with test data and write tests separately, and you can create your own test data set in each test (create the necessary records in the tables). The second option seems bad - there will be a lot of unnecessary records, tests will work for a long time. But, if your project is large, this is the only solution, because you will get confused pretty quickly, because the base will be filled in one place, and use in several other places.

But now, as I understand it, you are working on a project alone and there are tables within ten to fifteen? The first option will do.

Do I need to clean the base after the tests? It is possible, but in most running projects, deleting data leads to problems. The usual solution is to run the tests on a separate database, maybe even every time a new one.

It is best to hang integration testing on macros that are similar to DEBUG , for example, DB_INTEGRATION . In the Entity Framework migrations (in the Migrations folder) lies the Configuration class, which has a Seed method. This method is called to populate the data.

 internal sealed class Configuration : DbMigrationsConfiguration<DispatcherDbContext> { public Configuration() { AutomaticMigrationsEnabled = false; } protected override void Seed(DbContext context) { FillTestData(context); } [Conditional("DB_INTEGRATION")] private void FillTestData(DbContext context) { // с помощью context.AddOrUpdate добавляем тестовые записи // во все таблицы context.SaveChanges(); } }

Insert the FillTestData method and attach the Conditional attribute to it, due to which the method will be called and inserted into the assembly only if the macro definition DB_INTEGRATION .

Exactly the same attribute must be added to all integration test methods that will access this data.

 [TestMethod] [TestCategory("Integration")] [Conditional("DB_INTEGRATION")] public void UserCreation() { var dbContextFactory = new DbContextFactory(); var userRepository = new EfUserRepository(dbContextFactory); var username = Utils.GenerateRandomString(200); var user1 = userRepository.Create(username); var user2 = userRepository.ReadById(user1.Id); Assert.AreEqual(user1.Name, user2.Name); }

Now go to the properties page of the test project, and on the Build tab, type DB_INTEGRATION in the list of Conditional compilation symbols . After recompiling the project, test data will appear in the database, and tests will appear in the test panel. In the Test Explorer, the left upper Group By button, select Group By Traits and the Integration category tests will be shown in a separate group that you can run. Regular unit tests will be in the No Traits group, so it will be convenient to run them separately from integration tests and often.

Can integration testing be done fast?

Now, if you really want to make a real test of calls to the database, but that worked quickly? Yes, it is possible.

The second question: unit tests should work even if you do not have any external services and databases, is it possible to conduct testing without an external database server? Also yes.

Many modern DBMSs allow storing databases in memory. Some DBMSs are specifically designed to emulate the operation of an external server, although they are built into your application, see for example SQLite and SQL Server Compact Edition.

These tests will still not be modular in essence, but in terms of speed they will be at the modular level, and they can be run frequently.

Start exploring the issue with http://weblogs.asp.net/scottgu/vs-2010-sp1-and-sql-ce https://www.nuget.org/packages/EntityFramework.SqlServerCompact/

to be honest I didn’t understand the example with unit testing at all
The word nonsense is not well suited in the technical discussion.
In this case, we are talking about testing not a specification but a specific implementation through EntityFramework.
You can test the specification only in the integration test.
When possible, tests should be based on the specification, but perhaps not always.
@PavelMayorov, I spent a lot of time writing a post, and I don’t want to spend it on bickering anymore.
I do not think that you are right, because in this case there would be no talk of any mocks and stubs, they are used precisely for testing the implementation.
If this argument does not seem right to you, I cannot suggest another.
If the repository encapsulates work with the database, then the code using the repository does not know anything about the database.
But then the specification for the repository should not contain a database, from the point of view of the other modules the repository to store all the data in itself, that there is some other database there is just a implementation detail.

Pavel Mayorov Pavel Mayorov 48.9k five 50 110 · Answer 3 · 2016-08-26T08:11:59

To invent a wrapper over EF only in order not to depend on the database during testing is not a good idea. Because someone should test this wrapper too :)

Therefore, if you do not have a separate DAL, you need to test it on a separate test database. Keeping it up to date is not a problem; you can create a database and roll back migrations right in the tests. You can do this in some SetUpFixture

If you have a separate DAL, then it should be tested in the same way, on a test base. But everything above DAL can already be tested by replacing DAL.

PS If the class under test does not manage connections and transactions inside - you can run a test inside a transaction, and then roll it back - this will solve the problem of accumulating errors in the test database.

To invent a wrapper over EF only in order not to depend on the database during testing is not a good idea.
- No need to invent anything, you can use ready-made solutions.
Keeping it up to date is not a problem - if there is one programmer in the team who remembers everything and he has nothing but one test - then yes, otherwise it isn’t.
to conduct a test inside a transaction, and then roll it back - the tests should be simple and clear, the use of transactions there is also more like an extra code.
@DenisBubnov "if there is one programmer in the team who remembers everything and he has nothing but one test - then yes, otherwise it is not that" - and what problems may arise?
@DenisBubnov "The use of transactions there is also more like an extra code" in the test - not only it seems, but it is.
In the base class for tests, this is not a superfluous code, but an element of the testing infrastructure.
and what problems may arise - when a project is large with several branches (dev, test, preproduction and production) - then the maintenance is also problematic.
If the programmer accompanying this database is sick or quit, what then?
In the base class for tests, this is not a superfluous code, but an element of the testing infrastructure , depending on what will be tested.

Answer 4 · 2016-08-26T06:07:26

If possible, full-fledged tests should be similar to the work of a real application.

Those. a test is created for running tests, as it is created for real work, pumped up with data necessary for work (and test ones if load testing is needed). Further, respectively, there is an imitation of the client's work - connecting, performing operations, getting results and checking them for validity.

It should be borne in mind that some tests begin to overlap in the data, therefore, if possible, it is worth deleting the data for the test. Those. The test itself creates test data for itself, processes and checks it itself, and then also deletes it itself, to minimize the impact of its data on other tests.

How about this test looks like:

 [TestMethod] public void RegisterLetter() { this.Given(_ => this.GivenNewlyLetter()) .And(_ => this.GivenNewlyDocumentRegister(DocumentFlow.Incoming)) .When(_ => this.WhenRegisterDocument(this.letter)) .Then(_ => this.LetterShouldBeRegistred()) .TearDownWith(_ => this.DeleteLetter()) .TearDownWith(_ => this.DeleteDocumentRegister()) .BDDfy(); }

Create a letter, create a journal for it. We register in the journal a letter, check that the registration is successful. Delete the letter, delete the journal. The deletion of entities occurs in any case, although the test is successful, although not.

Another serious advantage of this approach is that if you do not deploy a new base, you will learn about it quickly.

There is also a serious minus, for which I have not yet found a convenient solution. If you do not have the opportunity to clean the database after the test (a business restriction that cannot be bypassed legally and simply), then it becomes more difficult to do related tests for the same data.

Testing in the second way, through Moki \ Fakey will be faster, because you will not have to knock on the base, but it will also have less benefit, because the connection with the base and some specific features of the EF can be lost. On the other hand, if you want to test the business logic of the application and on the storage features in the database for you without a difference - this is a great option, which is much easier to deploy, run and get results.

Стоит иметь в виду, что так нельзя тестировать например нагрузку, потому как вы выкинули целый слой приложения. Так нельзя тестировать сложные запросы, потому как EF их в SQL завернёт, где то упадёт при материализации и прочее, а тут у вас фейк, который работает в памяти и врядли будет имитировать любую ORM с достаточной достоверностью.

Собственно, плюсы этого тестирования, на мой взгляд, сильно проседают под реальностью.

Как вывод - на мой взгляд, тесты должны быть клиентскими в первую очередь. Those. работать так, как будет работать клиент - авторизация, операция, результат, проверка, выход. Пока не меняется клиентский интерфейс, ему одновременно неважно, что спрятано в базе данных, и одновременно он проверяет сразу все слои приложения. Естественно, тут возникает проблема, что развернуть такого клиента гораздо сложнее, чем просто написать тест на функцию, которой скормили параметр и получили результат. С другой стороны, разворачивание такого клиента обычно почти равнозначно разворачиванию реального клиента, унифицировать не так уж и сложно.

И да, не стоит сильно задумываться над тем, как за собой почистить базу. В большинстве случаев, достаточно развернуть чистую базу и дропнуть её за собой. А когда данные от других тестов начинают влиять друг на друга - у вас они либо и реальному клиенту мешают, либо тест недостаточно корректно изображает из себя клиента.