Efcore: In-memory DB testing issues

[rp0m]

Hi all,

I've been experimenting with xUnit and ef core in-memory db for testing my mock dotnet core api. It started going well, however the most tests I write against the in-memory DB, the more issues I am starting to see. I am trying to understand if this is an EF Core issue, an in-memory DB provider issue or my own expectations/understanding issue. To better or worse, my current understanding of the in-memory DB provider is that it is supposed to closely mimic the real DB behavior, to allow writing meaningful unit and or integration tests.

TL;DR of issues:

1. In-memory DB provider not enforcing FKs (in certain cases?)
2. In-memory DB provider not refreshing context on-demand, failing DB transactions (which seems to contradict issue 1 in places)
3. In-memory DB provider does not support transactions. How transaction-contaning methods be tested?
4. In-memory DB provider not resetting auto-incremented keys between DBs. Resolved using: https://github.com/aspnet/EntityFrameworkCore/issues/6872#issuecomment-258025241

I've put together a demo app to demonstrate the issues: https://github.com/rpasechnikov/xUnitMockApi

Steps to reproduce:

1. dotnet test

Further technical details

EF Core version: 2.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 (1803)
IDE: Visual Studio 2017 15.9.4

Does anyone else have such issues? How do you work around them? Is it just me or does in-memory DB provider seem very flaky and missing some core features? Is it worth/possible going away from in-memory DB provider and spinning up a real DB for each test? (limitations when run in a linux environment)

Is the problem my lack of understanding on the purpose of in-memory DB and how it is supposed to be used in the context of unit/integration tests?

Thanks!!

[ajcvickers]

@rp0m When you say, "my current understanding of the in-memory DB provider is that it is supposed to closely mimic the real DB behavior, to allow writing meaningful unit and or integration tests," can I ask where you got this understanding from?

[rp0m]

@ajcvickers - reading through the top few lines of https://docs.microsoft.com/en-us/ef/core/providers/in-memory/ got that idea into my mind, however I am not sure if I am mistaken. It sounds like I am...

Unit/integration testing is somewhat new ground for me when it comes to DB, so I am still trying to wrap my mind around how to do this correctly. Any recommendations at all are very welcome :)

[dsaf]

Does anyone else have such issues?

Yes.

How do you work around them?

Have to live with this as it's to late to remove ORM.

Is it just me or does in-memory DB provider seem very flaky and missing some core features?

No, it's not just you.

Is it worth/possible going away from in-memory DB provider and spinning up a real DB for each test?

Yes, I have seen this with LocalDB (lacks a lot of features though).

(limitations when run in a linux environment)

Sorry, whoever uses Linux brings their own suffering on them.

Is the problem my lack of understanding on the purpose of in-memory DB and how it is supposed to be used in the context of unit/integration tests?

No, the problem is that in-memory DB should not exist. It leads developers on a wrong path. Also ORMs should not exist, they make developers believe abstracting DB is possible.

[roji]

@rp0m for what it's worth, most of the limitations of the InMemory provider are documented in this doc page. If you need your tests to work with relational database features such as foreign key constraints, consider using Sqlite (which has an in-memory mode) or SQL Server, possibly in LocalDB which does provide most of what you'll need.

As a general rule, testing against the same database you'll be using in production reduces the surprises you risk.

[ajcvickers]

@rp0m Thanks for the additional info--my motivation for asking was to make sure we can update the docs to provide a more complete picture. This is a somewhat controversial area for which there are some different opinions even on the EF team. Here are my thoughts.

Testing that actual database queries work as expected (regardless of whether EF generates the queries or not) requires that they are run against the actual database system used in production. Anything else can result in differences in the database system resulting in different behavior.

That being said, consider that:

• Not all test code is specifically about testing the database access
• Sometimes the potential for some difference is worth accepting for efficient maintainable tests

For example, if the production database is SQL Azure, then tests could be run against:

• SQL Azure
• SQL Server on-site
• SQLite
• SQLite in-memory mode
• The non-relational in-memory provider

Each step down the list introduces more behavioral differences. But that doesn't mean you should always run everything against SQL Azure even when developing, since that would not be easy, cheap, or practical. So it would be common to use on-site SQL Server for this (e.g. LocalDb or SQL Express), even though some things may behave differently. Likewise, it might be more pragmatic to use SQLite for this, since it retains relational semantics, even though again it may behave differently in some ways.

Finally, to my first bullet point above, if you're writing tests for business logic, and they just need some fake data to work with, then using the in-memory provider to supply that data can be easy and efficient. It's just important to recognize that these tests are not really testing the database at all.

[rp0m]

@roji @ajcvickers thanks so much for such a detailed responses! This makes much more sense now! We are somewhat new to testing, progressively trying to add more and more front and back-end tests to our developing application, so this is mostly new ground for us.

It sounds like we'll stick to InMemoryDB for basic tests and use SQL Server on-site DB for more comprehensive tests, that involve more data movement/validation/etc.

Thanks again! 😺

[tomRedox]

@rp0m, you can stop methods with transactions throwing errors as follows:

public MyDbContext GetContextWithData()
{
    var options = new DbContextOptionsBuilder<MyDbContext>()
        .UseInMemoryDatabase(Guid.NewGuid().ToString())
        // don't raise the error warning us that the in memory db doesn't support transactions
        .ConfigureWarnings(x => x.Ignore(InMemoryEventId.TransactionIgnoredWarning))
        .Options;

    return new MyDbContext(options); 
}

There's a bit more on that on this question and answer I wrote on Stack Overflow.

I know that doesn't really help with actually checking if the transactions are doing what's intended, but it does at least make it possible to run those bits.

[tomRedox]

@rp0m I was also wondering how to test whether transactions had occurred. Imagine my surprise when I
was searching on stack overflow and found that I'd actually previously worked out how to test for transactions with both In Memory and SQL Lite! There's lots of info on that here