CODEWITHSUNDEEP
sqlentity-framework-corein-memory-database
gzak
gzak

Reputation: 4110

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?

Upvotes: 20

Views: 10609

Answers (3)

Anderson Paiva
Anderson Paiva

Reputation: 978

Alternatively in this scenario, you can use SQLite as an in-memory database when running the test suite to avoid network usage, it supports many SQL features but has some limitations.

services.AddDbContext<YourContext>(options =>
                options.UseSqlite(connection)
            );

Upvotes: 0

rgvlee
rgvlee

Reputation: 3193

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.

Upvotes: 5

ErikEJ
ErikEJ

Reputation: 41759

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

Upvotes: 21

Related Questions