DbContext查询返回空数据排查：数据库有记录但allRecords计数为0

问题场景 #

数据库中存在clinic_id匹配的Visit记录，直接执行SQL语句SELECT * FROM visit WHERE clinic_id = clinicId可返回1条记录，但调用以下代码查询时，allRecords.Count始终为0：

public record struct ImposterDbContextAuthorization(int AccountUserId, bool HasPermissionCanAccessAnyAccount, bool HasPermissionSuperUser = false) : IDbContextAuthorization;

public async Task<string> Send(int clinicId)
{
    // added await here
    Task.Run(async () =>
    {
        await SendIt(clinicId, DbContext.GetAccountId());
    });
    return "";
}
public async Task<string> SendIt(int clinicId, int accountId)
{
  var scope = _serviceScopeFactory.CreateScope();
  var dbOptions = scope.ServiceProvider.GetRequiredService<DbContextOptions<MyDbContext>>();
  var imposter = new ImposterDbContextAuthorization(accountId, true);
  var dbContext = new MyDbContext(dbOptions, imposter);

  var allRecords = await dbContext.Set<Visit>()
            .AsNoTracking()
            .Where(visit => visit.ClinicId == clinicId)
            .GroupBy(visit => visit.PatientId)
            .Select(group => group.Key).ToListAsync();
  // !!! allRecords.Count is 0, even though "SELECT * FROM visit WHERE clinic_id = clinicId" returns 1 record in database
  return "";
}

排查发现 #

1. 为Task.Run添加await后，查询可正常返回数据；
2. EF Core生成的实际SQL包含额外过滤条件（权限验证、软删除、鉴别器），具体SQL如下：

SQL: -- @__ef_filter__IncludeDeleted_0='False'
12:10:30:610    -- @__ef_filter___hasPermissionCanAccessAnyAccount_4='True'
12:10:30:610    -- @__ef_filter__p_3='True'
12:10:30:610    -- @__ef_filter___accountId_1='759' (Nullable = true)
12:10:30:610    -- @__ef_filter___accountUserId_2='860'
12:10:30:610    -- @__clinicId_0='1696'
12:10:30:610    SELECT v.patient_id
12:10:30:610    FROM visit AS v
12:10:30:610    WHERE v.discriminator = 'Visit' AND (@__ef_filter__IncludeDeleted_0 OR NOT (v.is_deleted)) AND (@__ef_filter___hasPermissionCanAccessAnyAccount_4 OR CASE
12:10:30:610        WHEN @__ef_filter__p_3 THEN v.account_id = @__ef_filter___accountId_1
12:10:30:610        ELSE EXISTS (
12:10:30:610            SELECT 1
12:10:30:610            FROM account_user AS a
12:10:30:610            WHERE a.id = @__ef_filter___accountUserId_2 AND a.account_id = v.account_id)
12:10:30:610    END) AND v.clinic_id = @__clinicId_0
12:10:30:610    GROUP BY v.patient_id

3. 怀疑问题由account_id不匹配导致。

原因分析 #

1. 异步操作未等待导致上下文丢失：Send方法中调用Task.Run但未添加await，方法会立即返回并结束当前请求，此时请求上下文被释放。Task.Run的异步委托后续执行时，调用DbContext.GetAccountId()会获取到错误的accountId（或默认值），传入SendIt后导致EF Core的权限过滤条件不匹配，最终返回空结果。添加await后，请求上下文会保留到异步操作完成，能获取正确的accountId，过滤条件匹配后返回预期数据。
2. EF Core全局过滤的影响：生成的SQL显示EF Core自动注入了权限验证、软删除等全局过滤逻辑。即使HasPermissionCanAccessAnyAccount设为true，若accountId错误，全局过滤中依赖accountId的分支（如超级用户判断）仍可能导致过滤条件不满足，返回空结果。

解决方案 #

1. 正确等待异步操作 #

在Send方法中添加await，确保异步委托执行时请求上下文有效：

public async Task<string> Send(int clinicId)
{
    await Task.Run(async () =>
    {
        await SendIt(clinicId, DbContext.GetAccountId());
    });
    return "";
}

更优方案：Web请求中避免不必要的Task.Run，直接调用异步方法：

public async Task<string> Send(int clinicId)
{
    await SendIt(clinicId, DbContext.GetAccountId());
    return "";
}

2. 提前获取上下文数据 #

如果必须使用Task.Run，提前在请求上下文有效时获取accountId，避免在异步委托中依赖已释放的上下文：

public async Task<string> Send(int clinicId)
{
    var accountId = DbContext.GetAccountId();
    await Task.Run(async () =>
    {
        await SendIt(clinicId, accountId);
    });
    return "";
}

3. 验证全局过滤逻辑 #

检查MyDbContext中基于ImposterDbContextAuthorization的全局过滤实现，确保HasPermissionCanAccessAnyAccount=true时，正确跳过account_id相关验证，避免参数映射或逻辑判断错误导致过滤异常。

内容的提问来源于stack exchange，提问作者Jason