[SOLVED] Raw sql with EF Core and in-memory db provider

Raw sql with EF Core and in-memory db provider

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?

Solution

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