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.
Instead InMemory DB you can use SQLite which works good with sql and it's pretty simple to setup as in memory database.
https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/sqlite