EF Core Complex query gets cached for each variation of parameters

Question

I have a complex query that seems to cause a memory leak in my applications memory.

From my understanding the query result is cached so that this processing does not need to be done every time the query is executed. https://learn.microsoft.com/en-us/ef/core/querying/how-query-works

But what it looks like is that the query is getting cached for each and every user that logs into the system. From the memory dump it looks like we have thousands of compiled query cache objects for the same query.

The query looks as follows.

public async Task<IList<EmployeeInboxMessage>> GetEmployeeMessagesAsync(long employeeId)
        {
            return await (from message in this.Repository.Set
                          join userStep in this.Repository.Context.Set<UserWorkflowHeaderStep>() on message.UserWorkflowStepId equals userStep.UserWorkflowStepId into userSteps
                          from userStep in userSteps.DefaultIfEmpty()
                          join acceptedStep in this.Repository.Context.Set<CompanyWorkflowStep>() on userStep.AcceptedStepId equals acceptedStep.WorkflowStepId into acceptedSteps
                          from acceptedStep in acceptedSteps.DefaultIfEmpty()
                          join rejectedStep in this.Repository.Context.Set<CompanyWorkflowStep>() on userStep.RejectedStepId equals rejectedStep.WorkflowStepId into rejectedSteps
                          from rejectedStep in rejectedSteps.DefaultIfEmpty()

                          let step =
                              message.InboxEntryType == InboxEntryType.Claims ||
                              message.InboxEntryType == InboxEntryType.AdvancedLeave ||
                              message.InboxEntryType == InboxEntryType.ChangeRequest
                                  ? new WorkflowHeaderStep
                                  {
                                      WorkflowItem = userStep.WorkflowItem,
                                      AcceptedStepId = userStep.AcceptedStepId,
                                      AcceptedStep = acceptedStep == null ? null : new WorkflowStep
                                      {
                                          OnApprovalActionId = acceptedStep.OnApprovalActionId,
                                          OnRejectionAction = acceptedStep.OnRejectionAction,
                                          OrderNumber = acceptedStep.OrderNumber
                                      },
                                      RejectedStepId = userStep.RejectedStepId,
                                      RejectedStep = rejectedStep == null ? null : new WorkflowStep
                                      {
                                          OnApprovalActionId = rejectedStep.OnApprovalActionId,
                                          OnRejectionAction = rejectedStep.OnRejectionAction,
                                          OrderNumber = rejectedStep.OrderNumber
                                      }
                                  }
                                  : null

                          let employeeName = string.IsNullOrWhiteSpace(message.OBOEmployee.PreferredName)
                              ? message.OBOEmployee.FullName
                              : message.OBOEmployee.PreferredName + " " + message.OBOEmployee.LastName

                          where message.EmployeeId == employeeId
                          orderby message.EffectiveDate
                          select new EmployeeInboxMessage
                          {
                              Message = message,
                              UserStep = step,
                              UserId = message.UserId,
                              UserName = message.User.FullName,
                              EmployeeName = message.OBOEmployeeId.HasValue
                                  ? employeeName
                                  : message.User.FullName,
                              RelatedPrimaryKey =
                                  message.InboxEntryType == InboxEntryType.Claims ||
                                  message.InboxEntryType == InboxEntryType.AdvancedLeave ||
                                  message.InboxEntryType == InboxEntryType.ChangeRequest
                                      ? userStep.RelatedPrimaryKey
                                      : message.UserWorkflowStepId!.Value,
                              StartUserCompanyId = message.StartUser.CompanyId
                          }).ToListAsync();
        }

Answer 1

It looks like your query is to complex for EF to create a single CompiledQueryCache object and it is adding a cached version for each "employeeId" that you are passing in. By the looks of it there is already 27804 versions.

If you restructure your query then it might solve your problem. Try removing the "let step = ..." and "let employeeName = ...". Worst case you would need to create raw SQL or maybe a view.

There is not a lot of documentation out there explaining how EF creates the query cache(I can't find any). You might never now what exactly your problem is except if some of the EF creators give some input or you could always go through their code.

If you still have a problem after changing your query it might be best to log a issue with efcore https://github.com/dotnet/efcore