Raw sql with EF Core and in-memory db provider

Question

One of my API routes uses a raw sql merge into command in order to do an atomic upsert operation, and in my automated tests I've got a TestServer instance that uses the in-memory db provider. It gives me an error, probably because the in-memory provider doesn't support running raw sql commands - is that true? If not, how do I get it to work?

Here's the Startup class for the tests:

// In memory DB for testing
services.AddDbContext<MyContext>(optionsBuilder => optionsBuilder.UseInMemoryDatabase("stuff"));
services.AddDbContext<MyStatusContext>(optionsBuilder => optionsBuilder.UseInMemoryDatabase("status"));
services.AddDbContext<MyUserRolesContext>(optionsBuilder => optionsBuilder.UseInMemoryDatabase("userroles"));

And the API code is as you'd expect:

var count = await context.Database.ExecuteSqlCommandAsync(@"merge into ...", default(CancellationToken), ...);
return count;

This code works fine in production against a real database, I just can't get it working with the in-memory provider in my tests. Is there any hope for me? What's the usual test strategy for custom sql scripts?

Answer 1

There is no hope for you, as the InMemory provider is a NoSQL non-relational provider. You should use SQL Server (for example localdb) for integration testing

Answer 2

As you've discovered the in-memory provider can't do relational operations (a reasonable limitation).

I had similar problems and ended up putting together a library to extend the in-memory provider to support relational operations - EntityFrameworkCore.Testing. It'll do the ExecuteSqlCommand/ExecuteSqlCommandAsync mocking.