How to correctly cache query results when using EF Core with a lot of Include()?

Viewed 408

I am using https://github.com/VahidN/EFCoreSecondLevelCacheInterceptor package to cache EF Core query results in my ASP.NET Core app. According to creator, it works like that:

The results of EF commands will be stored in the cache, so that the same EF commands will retrieve their data from the cache rather than executing them against the database again.

So this library returns cached results if the generated SQL is the same.

The problem is that I am using a lot of Include() methods while querying database. This is needed due to some pages showing a lot of information that is stored in different related database tables. Also, the query contains current-user-specific info, e.g. if the current user liked the Post or not. Caching user-specific info is not right as I heard and results in redundant cache entries since the only data that is changing is the current user ID.

Example of a query:

var post = dbContext.Posts
   .Where(p => p.Id == 1)
   .Include(p => p.Comments)
   .Include(p => p.VoteTracker
               .Where(t => t.UserId == "{current user ID}"))
   // ... other Include(...) calls
   .ToList();

The SQL generated can be quite huge with a lot of JOINs. So the problem with caching here are:

  1. if Comments get changed, then the whole query above will be invalidated aswell since this library

watches for all of the CRUD operations using its interceptor and then invalidates the related cache entries automatically

  1. Having current-user-specific info in the query creates a lot of identical cache entries, which are only varying by that current-user-specific info

What is the better approach for caching here?

The first thing that comes to mind is to have separate queries that can be cached. For example:

// Post and Comments query will be cached
var post = dbContext.Posts.SingleOrDefault(p => p.Id == 1);

var comments = dbContext.Comments
    .Where(c => c.PostId == 1)
    .ToList();

// This one will be excluded from caching (because of user-specific info)
var voteTracker = dbContext.VoteTrackers
    .SingleOrDefault(t => t.PostId == 1 && t.UserId == "{current user ID}");

Is it a good approach? And if it's not which one is better?

I am struggling with this a lot, but having a hard time finding the right solution. Thank you very much in advance! :)

0 Answers
Related