后台服务中调用DbContext执行LINQ查询出现超时问题的排查与解决步骤咨询

我遇到了一个无法解决的查询超时问题，涉及DocumentMoveData.TemplateFiles方法。这个方法被三个继承自同一基类的BackgroundService调用，服务之间唯一的区别是查询参数。这些服务通过IServiceScopeFactory创建作用域，从作用域中获取IDocumentMoveData实例，然后调用TemplateFiles方法。该方法使用DBContext执行针对SQL Server的LINQ查询，但同样的查询在SQL管理控制台中执行只需要不到1秒。

服务类结构 #

public class DocumentMoveService : BackgroundService {
    private IServiceScopeFactory _services;

    public DocumentMoveService(IServiceScopeFactory services) {
        _services = services;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken) {
        while (!stoppingToken.IsCancellationRequested) {
            try {
                using (var scope = _services.CreateScope()) {
                    var scopedServices = scope.ServiceProvider;
                    var docV1Factory = scopedServices.GetService<IDocumentMoveData>();
                    // 以下调用会使用DBContext，详见DocumentMoveData构造函数
                    var filesToProcess = await docV1Factory.TemplateFiles(10, _storageConfig.FileType);
                    foreach (DocumentInfo f in filesToProcess) {
                        // ... 处理逻辑
                        await docV1Factory.Save(f, destfile, (int)_storageConfig.FileType);
                    }
                }
            } catch (Exception ex) {
                // 异常处理（省略）
            }
            // 服务轮询间隔（省略）
        }
    }
}

public class DocumentMoveServiceOne : DocumentMoveService{}
public class DocumentMoveServiceTwo : DocumentMoveService{}

数据访问接口与实现 #

public interface IDocumentMoveData {
    Task Save(DocumentInfo docinfo, IStorageFile savedfile, int ftype);
    Task<List<DocumentInfo>> TemplateFiles(int count, eFormsModel.DataObjects.DocumentFile.TypeEnum ftype);
}

public class DocumentMoveData : IDocumentMoveData {
    private readonly DBContext _context;

    public DocumentMoveData(DBContext context) {
        _context = context;
    }

    public async Task Save(DocumentInfo docinfo, IStorageFile savedfile, int ftypeid) {
        int storageid = await GetStorageId(savedfile.Folder);
        DocTemplateFile newret = new DocTemplateFile() { /* 初始化代码省略 */ };
        _context.DocumentTemplateFiles.Add(newret);
        await _context.SaveChangesAsync();
    }

    public async Task<List<DocumentInfo>> TemplateFiles(int count, eFormsModel.DataObjects.DocumentFile.TypeEnum ftype) {
        try {
            var query =  _context.DocumentFiles
                .Include(o=> o.Document)
                .Where(df => df.FileTypesId == Convert.ToInt32(ftype))
                .Where(c => !_context.DocumentTemplateFiles.Any(cc => cc.OriginalDocId == c.DocumentsId))
                .Select(df => new DocumentInfo {
                    DocumentFileId = df.Id,
                    FileName = df.FileName,
                    DocumentId = df.DocumentsId,
                    UserId = _context.Documents
                        .Where(d => d.Id == df.DocumentsId)
                        .Select(d => d.UploadedByUserId)
                        .FirstOrDefault(),
                    CustomerId = _context.Users
                        .Where(u => u.LoginId == _context.Documents
                            .Where(d => d.Id == df.DocumentsId)
                            .Select(d => d.UploadedByUserId)
                            .FirstOrDefault())
                        .Select(u => u.CustomersId)
                        .FirstOrDefault(),
                    WhenCreated = df.Document.UploadedDate
                })
                .OrderByDescending(df => df.DocumentId)
                .Take(count);

            List<DocumentInfo> documentFileInfos = query.ToList();
            return documentFileInfos;
        } catch (Exception ex) {
            // 异常处理（省略）
            throw;
        }
    }
}

Program.cs中的服务配置 #

hostBuilder.ConfigureServices(services => {
    // 注意：两种DbContext注册方式会导致不同的超时表现
    // 方式1：约100次迭代后出现查询性能下降
    // services.AddDbContext<DBContext>(options => {
    //     var t = config.GetConnectionString("connstr");
    //     options.UseSqlServer(t);
    // });

    // 方式2：立即出现性能问题
    services.AddDbContext<DBContext>(options => {
        var t = config.GetConnectionString("connstr");
        options.UseSqlServer(t);
    }, ServiceLifetime.Scoped);

    services.AddScoped<DBContext>();
    services.AddScoped<IDocumentMoveData, DocumentMoveData>();

    services.AddHostedService<DocumentMoveService>(serviceProvider => {
        var logger = serviceProvider.GetRequiredService<ILogger<DocumentMoveService>>();
        var scopeFactory = serviceProvider.GetService<IServiceScopeFactory>();
        StorageTypeByTaskType[configFType.FileType] = typeof(DocumentMoveService);
        return new DocumentMoveService(logger, scopeFactory, configFType);
    });

    // 另外两个后台服务的注册（类似）
    services.AddHostedService<DocumentMoveServiceOne>(serviceProvider => {
        // 初始化逻辑省略
    });
    services.AddHostedService<DocumentMoveServiceTwo>(serviceProvider => {
        // 初始化逻辑省略
    });
});

EF Core生成的SQL查询 #

declare  @__p_1 int, @__ToInt32_0 int
select @__p_1 = 10, @__ToInt32_0 = 2

SELECT [t].[ID] AS [DocumentFileId], [t].[FileName], [t].[Documents_ID] AS [DocumentId], COALESCE((
    SELECT TOP(1) [d2].[UploadedBy_User_ID]
    FROM [Documents] AS [d2]
    WHERE [d2].[ID] = [t].[Documents_ID]), 0) AS [UserId], COALESCE((
    SELECT TOP(1) [u].[Customers_ID]
    FROM [Users] AS [u]
    WHERE [u].[login_id] = COALESCE((
        SELECT TOP(1) [d3].[UploadedBy_User_ID]
        FROM [Documents] AS [d3]
        WHERE [d3].[ID] = [t].[Documents_ID]), 0)), 0) AS [CustomerId], [d1].[UploadedDate] AS [WhenCreated]
FROM (
    SELECT TOP(@__p_1) [d].[ID], [d].[Documents_ID], [d].[FileName]
    FROM [DocumentFiles] AS [d]
    WHERE [d].[FileTypes_ID] = @__ToInt32_0 AND NOT (EXISTS (
        SELECT 1
        FROM [DocumentTemplateFiles] AS [d0]
        WHERE [d0].[OriginalDocId] = [d].[Documents_ID]))
    ORDER BY [d].[Documents_ID] DESC
) AS [t]
INNER JOIN [Documents] AS [d1] ON [t].[Documents_ID] = [d1].[ID]
ORDER BY [t].[Documents_ID] DESC

数据库表结构 #

CREATE TABLE [dbo].[Documents](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    -- 其他字段省略
    CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[DocumentFiles](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Documents_ID] [int] NOT NULL,
    [FileTypes_ID] [int] NOT NULL,
    -- 其他字段省略
    CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

排查与解决步骤建议 #

我来给你梳理几个关键的排查方向，一步步定位问题：

• 规范DbContext的生命周期注册：你现在同时用AddDbContext（指定Scoped）和AddScoped<DBContext>()，这会导致DbContext实例重复注册，可能引发作用域内的实例混乱。建议只保留AddDbContext的注册（默认就是Scoped生命周期），去掉单独的AddScoped<DBContext>()，避免实例冲突。另外要确认using (var scope = ...)块是否正确释放作用域，确保DbContext被及时回收。

• 重构LINQ查询优化性能：当前查询在Select里嵌套了多次子查询，虽然EF Core能转成SQL，但这种嵌套结构可能让SQL Server生成低效的执行计划。尝试用Join代替嵌套子查询，重构后的查询会更简洁，比如：

  var query = from df in _context.DocumentFiles
              join doc in _context.Documents on df.DocumentsId equals doc.Id
              join user in _context.Users on doc.UploadedByUserId equals user.LoginId into userJoin
              from u in userJoin.DefaultIfEmpty()
              where df.FileTypesId == Convert.ToInt32(ftype)
              && !_context.DocumentTemplateFiles.Any(cc => cc.OriginalDocId == df.DocumentsId)
              orderby df.DocumentsId descending
              take count
              select new DocumentInfo {
                  DocumentFileId = df.Id,
                  FileName = df.FileName,
                  DocumentId = df.DocumentsId,
                  UserId = doc.UploadedByUserId ?? 0,
                  CustomerId = u?.CustomersId ?? 0,
                  WhenCreated = doc.UploadedDate
              };
  

• 检查SQL Server的执行计划与索引：把生成的SQL拿到SSMS中查看实际执行计划，看看是否存在表扫描、缺少索引的情况。建议给这些字段创建非聚集索引：

  • DocumentFiles的FileTypes_ID和Documents_ID联合索引
  • DocumentTemplateFiles的OriginalDocId字段索引
  • Users的LoginId字段索引
  • Documents的UploadedByUserId字段索引
    同时可以执行UPDATE STATISTICS [TableName]更新表统计信息，确保查询优化器能生成正确的执行计划。

• 排查连接池与资源泄漏：长时间运行服务后，用SSMS活动监视器查看SQL Server的连接数，确认是否存在连接泄漏。检查连接字符串的Max Pool Size参数（默认100），如果后台服务并发较高，可以适当调整，但不要超过SQL Server的承载能力。

• 添加日志监控：在TemplateFiles方法中添加日志，记录每次查询的执行时间和DbContext的实例ID（通过_context.GetHashCode()获取），确认是否存在查询时间逐渐递增或者实例复用异常的情况，帮助定位是单查询变慢还是累积的资源问题。

备注：内容来源于stack exchange，提问作者Michael EstrinOne