You need to configure the appropriate query mode. Which query mode should you select?

In-Memory with DirectQuery sets in-memory cache as the primary query resolution path, delivering the highest query performance through cached data. When a query cannot be resolved from cache or DirectQuery is explicitly required, the engine falls back to the relational source - matching the stated requirement that queries use cache unless otherwise necessary.

DirectQuery with In-Memory reverses the priority, making the relational database the primary query target and using in-memory cache only as a secondary option, which does not prioritize cache-based performance.

DirectQuery mode routes all queries directly to the relational data source and never uses the in-memory cache, completely failing the requirement to resolve queries from cache.

In-Memory only mode uses exclusively the in-memory cache and provides no DirectQuery fallback capability, so it cannot serve queries that require direct relational source access.