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?
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?
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
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.