Raw sql with EF Core and in-memory db provider
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.
Instead InMemory DB you can use SQLite which works good with sql and it's pretty simple to setup as in memory database.