This is something that I've heard a number of opinions and theories about, but I'm still torn on how to go forward.

For context, this particular issue deals with the following technologies, in case that matters much:

  • xUnit
  • Entity Framework 8.0
  • SQL-Server 2016

I'm wanting to set up testing within our new software development, since the previous development teams had next to nothing in terms of testing. I thought a good place to start would be to create some basic unit tests utilizing the CRUD operations our applications will be using. And for this, I'm mainly targeting Entity Framework in terms of database connections and operations. However, this is a database-first methodology. The database, schema, and data are all already set up, primarily because this is a proprietary third-party database we're connecting to. It's a SQL database that's installed by the vendor we have and we're able to manage and add schema tables and views, but the basic structure of the database shouldn't change, as that would break the application it's tied to.

So, given these constraints, my EF software solution is automating CRUD operations into the table from other sources (mainly data warehousing sources). And for this focused project, it's only managing data in a handful (about 20) tables, rather than the whole database (about 200+ tables). This means my EF is very focused on just those tables and just the fields we're interested in.

All this to say, the solution is sound and a demo of it seems to run nicely. However, it's the unit tests that I don't really know what to do with. With other xUnit Entity Framework projects, I've seen a ton of answers on how to go about unit testing data, and it always starts and ends with tearing down the current database and creating a new database from the EF context. But I can't do that, because again, I don't control this database on those terms. So...I'm wondering if there's better methods to unit test pre-existing EF schema, other than delete/create.

3 Answers 3


You need a test database under your own control, regardless whether you create automated tests or going to test manually. So focus on creating one, (where the schema is a copy of the production database's schema), containing only a fraction of the number of records you expect in the production database.

Any real DBMS I know has command line tools for "dumping" a whole database to a file and recreating the DB from a dump. For Ms-SQL, according to this source, this can be accomplished using sqlcmd and the TSQL commands BACKUP and RESTORE. Use these tools for automating the steps you mentioned inside your automated tests ("tearing down the current database and creating a new database"), but not "from EF context", but from a prior test database dump. This can be quite efficient if you can downsize the amount of data to a minimum beforehand.

If the process is still too slow for your purpose, consider optimizations. For example:

  • keep the test database with the required schema online, only clean-up and reinstall the data as part of of the tear down/setup process. That may work well when the schema stays unchanged over a longer period

  • downsize the schema from the 200 tables you mentioned to the 20 tables your program requires

  • you could also try to write your CRUD code in a way to make it work not just on SQL Server, but also on SQlite, which would allow you easily to keep a whole test database in a single SQlite file, which is much better to handle for automated tests than a heavyweight SQL server. But beware, the behaviour with SQlite may be different than with SQL server, you have to evaluate by yourself if that's an option.

  • I will say, this is my preferred method, but I have a DBA that is very, very controlling and refuses to work with our dev team. He's incredibly protective of all databases and refuses to even give us a dev environment that is backed up (since he's in charge of any server that has SQL on it). I know this is more of an internal issue for us, but that's one of the reasons why I'm trying to do DB verification without doing a full restore or teardown right now. Commented May 24 at 15:35
  • 1
    @CrystalBlue: well, ignore that guy, download an MS SQL server developer license (its free) and install it yourself on a development machine. For setting up a small-scale SQL server for testing purposes, you don't need a DBA.
    – Doc Brown
    Commented May 24 at 16:04
  • @DocBrown,"But beware, the behaviour with SQlite may be different than with SQL server, you have to evaluate by yourself if that's an option." - it's quite unlikely that a significant existing application written against a client-server SQL engine, would work seamlessly against SQLite. And SQLite has no concurrency, so a vast scope of errors that will occur on a client-server engine under load, cannot occur in SQLite.
    – Steve
    Commented May 26 at 4:48
  • @Steve: well, we can speculate a lot about the OPs situation, but in the end, it is just wild guessing. Note the OP wrote they want to test just "some basic CRUD operations". Note the OP wrote they are using entity framework (which can help to abstract from the specific DBMS). Note there are other lightweight DBMS options than SQLite the OP could try. In the end, the OP has to try out what works for them and what not.
    – Doc Brown
    Commented May 26 at 5:27
  • 1
    That complexity and subtlety also means one person cannot supervise a menagerie of different configurations and must have a consistent approach with which they are able to become familiar over many years of experience. Because a development machine has no critical data to preserve (though it may still require security) and is expected to be occasionally broken, your existing DBA is probably the wrong person to ask to furnish that facility, and you might implicitly be asking for something which under your corporate circumstances would require an additional DBA. (2/2)
    – Steve
    Commented May 26 at 5:42

Dropping and recreating the schema is used for two reasons:

  • A clean DB schema makes for a mostly deterministic test.
  • It simplifies test cleanup.

Using an existing schema prevents you from having a perfectly deterministic test, but this can be forgivable if the data is segregated good enough. I've done this before and it was plenty good for development. Each test can insert the data it needs, and then each test is responsible for cleaning this data up at the end. The challenge is cleaning up data from a failed test, because it leaves the database in an uncertain and possibly a partially corrupted state (at least from the perspective of your business logic).

The downside to reusing an existing schema is that two people running the same test can stomp on each other's data, or cause unintended failures. You will need to take care to ensure queries in your test data don't accidentally capture information that another test is using or creating. This is a lot more difficult than you think, because the System Under Test isn't written this way.

If the vendor cannot provide you a dedicated database just for automated testing, then consider using a local installation of the same kind of database. Entity Framework can create the schema before each test, and blow it away at the end. The challenge here is seeding the database with common lookup data, but this can be handled in SQL scripts or Entity Framework migrations. This gives you an isolated database with the same table structure, but only that small sample of tables. This isn't a perfect solution, because it isn't using a schema with all 200+ tables, so you won't eliminate integration testing in the real environment, but this should suffice for local development.

If none of these options are viable, then it's time to consider if the database is absolutely necessary. Split tests into how frequently you can run them, and how isolated you can make them.

  • Fast and isolated tests.
    • Tests are not dependent on one another.
    • Tests can be run concurrently.
    • Tests do not require outside resources like databases, file system, etc.
    • Mocks and stubs are used in place of outside resources like databases and file systems.
    • Each test should run in one millisecond; ideally less.
  • Slow and integrated tests.
    • Connect to a real database.
    • Use a real file system.
    • Call a real web service.
    • Tests do not use the real user interface, but might directly initialize components used in a user interface.
    • Each test can take 100s of milliseconds to several seconds each.
  • End-to-end tests
    • Use the real, fully integrated software system (e.g. web server + database + web browser)
    • Typically need some sort of automation framework like Selenium, Appium, etc.
    • Most of the test is executed against the user interface simulating a real use case.
    • Each test can take multiple seconds to 10s of seconds to execute.

If the tests you are talking about are really verifying C# logic, then try to utilize fast and isolated tests. Mock and stub the database portion of the application. This might require refactoring code to sprinkle in some interfaces instead of using the Entity Framework data context directly, but then you can take the database out of the picture all together, which is the most ideal situation.

  • Thanks a lot of this insight! Helps me to think about my current testing and what we should be doing going forward. Commented May 24 at 15:29

What's not clear from the question is, what things are you actually going to test?

You're obviously hooking into the backend of an existing application made by a third party, to supplement its functionality or automate certain changes.

The main risk under such circumstances will be disruption to the workings of the existing application, but obviously you aren't going to have the test suite available for the third-party application, nor will you have access to the design assumptions and expertise that have gone into the existing application so as to test for whether you are violating the design. You most likely won't know what you've broken, until the fallout has already been produced.

Given that the unit tests will impose a conceptual overhead for you as the developer, and an overhead in terms of devising automated testing infrastructure (for example, additional databases and setup scripts and so on), you might find it's more effective just to accept that there are no automated tests, rather than do a lot of work implementing spurious automated tests which do not go anywhere near the things which could benefit from tests, whilst drawing your attention away from the things that do matter.

Also, in this kind of situation, it's highly likely that you'd benefit from writing raw SQL rather than using EF, because you will need to be very careful, and might need to be very specific, about how certain procedures execute and under what settings (assuming you're treading over some of the same data as the existing application).

Many database-related bugs occur under concurrent load when the existing application is being operated by one or more users at the same time as your procedure is running in the background, or otherwise when two or more things are happening at once under very specific coincidences of timing or sequencing.

In other words, in order to automatically test for the errors that developers make in this area, some necessary (but not wholly sufficient) conditions are that you must run two or more procedures concurrently, and you must run them at very specific controlled timings.

Practically speaking, you cannot automatically test for these kinds of cases, and it's not professionally normal for database application developers to try to implement automated tests for these particular kinds of defects. Instead, only manual analysis occurs.

SQL itself does not allow a so-called "physical" algorithm to be dictated to the database engine, nor to control the sequencing or to single-step the algorithms at a necessary level of detail. Special instrumentation and techniques, specific to each engine technology, are required for this. The best you could do with pure SQL is to blast the engine repeatedly with the same queries at once, and hope that the chosen query plans don't change between the test environment and the production environment.

But I don't want to make it sound like the tooling is the only obstacle.

  • This is some great insight. And something me and the rest of the team need to discuss as well. One of our pain points is that a lot of our databases are not ours. They're DBs from other applications that we're trying to ETL between. So some of the testing we want to do has been about verifying data between the databases, since they don't do that on their own. Commented May 24 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.