Testing Entity Framework Core Correctly in .NET

Hello, everybody. I'm Nick, and in this video, I will show you the biggest mistake .NET developers make when it comes to testing their database, especially when they are using Entity Framework Core, and that's replacing their actual data provider with the in-memory one. That approach leads to massive problems, and in this video, I will show you how you can deal with it.

Don't forget to comment, like and subscribe :)

Social Media:

#csharp #dotnet

[brianm]

We used to use the SQLite in-memory DB for our tests. Once we started switching to using TestContainers (since our actual DB was Postgres), we learned that a lot of our tests were actually invalid because SQLiite (and the EF in-memory DB) don't enforce a lot of things, like string length limits and foreign keys.

[timmoth]

I always use a real database for my integration tests, but often I'll write higher-level behavioral unit tests for which I will use an in-memory database (being mindful of the limitations) to keep them quick and minimizing the need to mock.

[lost-prototype]

Been doing this for the last few years! Did a lot of discussion on it with EF maintainers and some other thinkers in the space. It works very well.

[liquidpebbles]

That password joke was so smooth. I can't stop laughing

[dinov]

How do you handle large systems with setup and inserting setup data with 100s of tests? Do you use a transactional context that rolls back after a test or regular transactions? How do you do parallel testing if you are sharing the same instance?

[cdnkarasu]

Our production is Azure Sql, devs and tests all use localdb. Just a difference in connection string and all works great.

[Thorarin]

I usually use Sqlite provider with the "memory" connection string. One advantage of the in memory provider though: the error messages are actually better than the TRASH Sqlite provider's error messages. One that comes up most often for me is foreign key violations.
Testing with the actual provider you use in production has obvious advantages, but it's still relatively slow.

[Crozz]

I also suggest specifying to test container builder a concrete image tag of the db that corresponds to your prod db version.

[LaRevelacion]

Ok, i have a question, if I set up containers for my tests but let's say that my integration tests require something to be configured in database like a stored procedure or something like that, how should I do it? EF core uses migrations to keep up the database state, is it necessary to run all migrations before the test in that start method or what would be the best solution here?

[SinaSoltani-tfzo]

TestContainers can be used only when Docker is installed on the Build Server. Otherwise you have to ignore these tests on the Build Server and run them locally/manually which is not useful.
Also, if your Build Server is a Windows Server, then you can forget the whole thing, because there is no Docker for Windows Server and if you manage to install it with tricks on a Windows Server you can then only have Windows Containers and not Linux Containers.

[yuGesreveR]

It would be great if there was any option to do this without docker at all. Although, most of ci's supports it, the key word is 'most'. Moreover, I don't see a reason to use docker for testing purposes only if your app is not contenerized itself and you haven't considered it as a needed feature of the pipeline initially

[FernandoMedeiros]

I understand the premise and agree that it's not testing the actual conversion of SQL, but you know what? I don't think it's that bad.
Surely it has a few drawbacks, but so does every approach. Some of the benefits I like are:
- It's portable, and runs straight from the IDE or pipeline without needing to set up a container runtime.
- The tests are usually quicker, as they can be easily parallelizable (Especially with SQLite provider), while actual DBs need to run mostly sequentially to avoid having the data of one test impacting the other.
- Easy to run + Quicker feedback loop = Better developer experience.
The TestContainer approach also has some drawbacks:
- Some teams don't have EF migrations, but the DB is versioned in SQL Scripts that run a separate pipeline, sometimes in a different repo. Creating a TestContainer with the correct schema may be more difficult.
- Now that Docker Desktop require licenses, some companies are using containerd under WSL2, which (AFAIK) would not run natively unless it's configured to have the socket exposed in a local port.
- Some CI/CD setups don't support containers.
You also mention that unless it's using the actual database, we don't have good integration tests. I'm not too sure I agree with this point, for a couple of reasons.
One of them is the difference between Unit and Integration tests. I'm aware of some heat in the community over the past years regarding the Chicago vs London school of thought (a.k.a., "Sociable Unit Tests") when it comes to the definition of unit test, but it's arguably that if Unit tests are supposed to test just one unit, then if you have more than one concrete (not mocked) service class instance being test you have an integration test.
The other reason is... does it really matter? Saying that you don't have good integration tests unless you use the same DB is like saying you don't have good integration tests if you don't use WebApplicationFactory and call your API in your tests because that's what your clients use. I'm not too keen on this mindset, as the core of our solution should be the domain, the business logic. That's the critical part, that's what needs a comprehensive test. Entrypoint layer (being it HTTP, gRPC, Service Bus events) and persistency (being it SQL, NoSQL or simple text files) are implementation details, coupling most of the test with those seems a bit odd. And making a test that is supposed to verify some rules under a chain of responsibility go all the way from API to the Persistency layer when just the raw classes would suffice seems like unnecessary overhead and hurts developer experience.
At the end of the day, there's no right or wrong. Each team uses what suits them best. I just don't think actual DBs on TestContainers are better than an in-memory provider, it just has different pros and cons.

[alonmore]

That's great. In memory db doesn't really convert the linq to real sql and therefore may hide potential issues when writing complex linq which may call on other methods in dotnet (which may or may not be valid for linq conversions). I will definitely start adopting this in my workplace.

[llindeberg]

IMO: In-memory database is for unit tests! Testcontainers are for integration tests. Question - are you able to run these tests in parallel? Is it one database per test, if so what is the overhead of doing that for 100-500 tests?

[MrFreddao]

I write my Integration tests from scratch, without using any extra library. Excellent results so far.

[BesarKutleshi-zw]

Hey Nick, I have used this TestContainers but when having many tests more than 100 lets say, its creating a lot of containers in docker, its taking 100% of cpu and memory running all of them and some of them will fail too. Do you know how can we optimize?

[tridy]

I do not think there is any place for any kind of replacement, mocking or anything for the integrations test. If one is testing the interaction between 2 components, how replacing one of them would make any sense? You are running MS SQL in prod, but using MySQL/SQLite in integration tests? How is that an integration test?

Quote from the in-memory db package page:
"This database provider allows Entity Framework Core to be used with an in-memory database. While some users use the in-memory database for testing, this is discouraged"

[zethoun]

Nice video but I really feel like it's missing the WHY in memory db tests are a bad idea (like I saw in some comment about constraints and other)

[alexbarac]

TestContainers = mindblown, thank you for this!

[ferquo]

Feels like Deja Vu... Is this a re-upload?