4

Use case: We have a quite large database (about 200 tables) that is used in a large (legacy) system. It's implemented as a database-first approach, with one edmx file defining the entire database. We are using XUnit and Effort for automatic testing. The problem is that these tests are very slow. It takes something like 7-8 minutes to run our current test suite, even though test coverage isn't anywhere near what we want it to be.

I've noticed that if I create a smaller subset of the edmx file, by removing some tables that aren't needed, tests run faster.

I'm looking for a solution where for a particular test, or suite of tests, we can somehow make Effort only create the subset of tables that are needed (I think in many cases, we'll only need one table).

Currently we're setting up our connection like this:

connection = EntityConnectionFactory.CreateTransient("metadata=res://entities.csdl|res://entities.ssdl|res://entities.msl");

Is there some way we can (for instance, by running an XML transformation in runtime), make Effort only create the data structures it need for a subset of tables that we define?

Ropez
  • 3,485
  • 3
  • 28
  • 30
  • Do you really need the database for unit testing? What about mocking? Check the "test pyramid" and use the database for integration tests and not for all unit tests. – Julian Nov 07 '19 at 22:13
  • Yes, we are refactoring, and using unit tests where we can, but there are two cases where it still would be very helpful to get effort to run faster. One is, as you say, for so-called integration tests. We have a large legacy system, and in many cases we can't easily refactor to support unit tests, but we still want to implement some test coverage. The other case is for refactored code, where we have unit tests for all the business logic, which is separate from the DB layer, we still need fast tests for the lowest layer, and it's not straight forward to mock out entity framework. – Ropez Nov 09 '19 at 08:09
  • Is the time consumed by creating 200 empty tables or is there a lot of data inserted in the tables as well? – gogognome Nov 14 '19 at 09:08
  • Getting rid of edmx file may be the better goal for refactoring. Then build a hierarchy among dbcontextes. Then you refactor your tests to use the smaller portions of db context and leave the business layer with the top level dbcontext. Having mixins in c# would be perfect for this situation. – Eldar Nov 14 '19 at 19:16
  • There are many workarounds on this topic, some people split database into separate contexts, depending on a business logic, for example [this](https://stackoverflow.com/questions/6185918/entity-framework-4-1-for-large-number-of-tables-715) topic is discussion around that approach, but I am interested, if you have considered different approach for Unit testing, for instance, creating an API, with a reference to the same context, which may initialize test db context only once (the most time consuming part I assume), when you publish and load it first time. – Irakli Nov 28 '19 at 12:54

2 Answers2

4

Disclaimer: I'm the owner of the project Entity Framework Effort

Our library has a feature that allows creating a restore point and rollbacking to it.

So by using this trick, you could use the CreateRestorePoint() only once when all tables are created and then for every test, start them with RollbackToRestorePoint. (There is several other ways to make it works but I guess you get the point)

It will without a doubt make your test run A LOT faster since the table will not have to be created every time.

Here is an example: 

var conn = Effort.DbConnectionFactory.CreateTransient();

using (var context = new EntityContext(conn))
{
    context.EntitySimples.Add(new EntitySimple { ColumnInt = 1 });
    context.EntitySimples.Add(new EntitySimple { ColumnInt = 2 });
    context.EntitySimples.Add(new EntitySimple { ColumnInt = 3 });
    context.SaveChanges();
}

// Create a RestorePoint that will save all current entities in the "Database"
conn.CreateRestorePoint();


// Make any change
using (var context = new EntityContext(conn))
{
    context.EntitySimples.RemoveRange(context.EntitySimples);
    context.SaveChanges();
}

// Rollback to the restore point to make more tests
conn.RollbackToRestorePoint();
Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
  • I've been struggling trying to make your suggestion work in our project. If I create a restore point immediately after I've created the connection, it runs, but it has zero improvement on performance. However, I've tried to "warm up" adding some fixtures to the database _before_ creating the restore point. Whenever I try to create something non-trivial, the rollback method throws this error, "Oops! There is an error when trying to generate the insert order." – Ropez Nov 23 '19 at 10:33
  • 1
    @Ropez, if you could provide a small unit test project with this issue either on our Issue Tracker: https://github.com/zzzprojects/EntityFramework-Effort or directly by mail: info@zzzprojects.com, we would be happy to look at it. – Jonathan Magnan Nov 23 '19 at 21:56
0

Separate out Unit test and Integration test. For Integration test you can use Database and run on higher environments (to save time) but on local environments you can make use of Faker\Bogus and NBuilder to generate massive data for unit test.

https://dzone.com/articles/using-faker-and-nbuilder-to-generate-massive-data

Other option is you can create resource file corresponding to your unit test cases https://www.danylkoweb.com/Blog/the-fastest-way-to-mock-a-database-for-unit-testing-B6

I would also like to take you look at InMemoryDB vs SqlLite performance, http://www.mukeshkumar.net/articles/efcore/unit-testing-with-inmemory-provider-and-sqlite-in-memory-database-in-ef-core

Although above example is for EFCore, in EF6 also we can use SqlLite https://www.codeproject.com/Tips/1056400/Setting-up-SQLite-and-Entity-Framework-Code-First

So my recommendation for you is to go with sqllite for Integration testing scenarios. For Unit test you can go either with sqllite or with Faker\Bogus and NBuilder.

Hope it helps!

PavanT
  • 81
  • 8