Raw sql with EF Core and in-memory db provider

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.