WinUI/C#应用Task.Run中使用DbContext重复查询崩溃问题求助

开发一款基于WinUI/C#、EF Core和SQLite的应用，数据库内有多张表，每张表包含900万条记录。为处理这类重型查询，采用Task.Run结合async/await实现异步操作，首次查询可正常返回结果，但再次查询时抛出异常。将所有代码放入Dispatcher.TryEnqueue后异常消失，但Task.Run失去作用，UI出现阻塞，怀疑DbContext无法在Task.Run中正常工作。

• Exception thrown: 'System.InvalidOperationException' in WinRT.Runtime.dll
• Exception thrown: 'System.ArgumentOutOfRangeException' in WinRT.Runtime.dll

await Task.Run(async () =>
{
    await PerformSearchAsync(query, progress, cancellationTokenSource.Token);
});

private Dictionary<TableType, Func<DbContext, IQueryable<object>>> tableMappings = new Dictionary<TableType, Func<DbContext, IQueryable<object>>>
{
    { TableType.T935, db => db.Set<Person935>() },
    { TableType.T936, db => db.Set<Person936>() },
    { TableType.T937, db => db.Set<Person937>() },
    { TableType.T938, db => db.Set<Person938>() },
    { TableType.T939, db => db.Set<Person939>() },
    { TableType.T93033, db => db.Set<Person93033>() }
};

public async Task PerformSearchAsync(string query, IProgress<int> progress, CancellationToken cancellationToken)
{
    var db = new IFDBDbContext();

    var tableQueries = new List<IAsyncEnumerable<object>>();
    
    var tableType = GetTableType(SearchQuery);

    if (tableMappings.TryGetValue(tableType, out var dbQuery))
    {
        var dbSet = dbQuery(db);
        tableQueries.Add(GetSearchResultsAsync(dbSet, query, cancellationToken));
    }

    // Track progress
    int totalTables = tableQueries.Count;
    int completedTables = 0;
    int completedItems = 0;

    foreach (var tableQuery in tableQueries)
    {
        await foreach (var item in tableQuery.WithCancellation(cancellationToken))
        {
            completedItems++;

            dispatcherQueue.TryEnqueue(() =>
            {
                DataList.Add(item);
                _tmpCompletedItems = completedItems;
            });
        }

        completedTables++;
        progress.Report((completedTables * 100) / totalTables);
    }

    dispatcherQueue.TryEnqueue(() =>
    {
        ShowStatus(DataList.Count);
    });
}

private async IAsyncEnumerable<object> GetSearchResultsAsync(IQueryable<object> dbSet, string query, [EnumeratorCancellation] CancellationToken cancellationToken)
{
    await foreach (var item in dbSet
        .Where(x => EF.Property<string>(x, nameof(BasePerson.Mobile)) != null && EF.Property<string>(x, nameof(BasePerson.Mobile)).Contains(query))
        .AsAsyncEnumerable()
        .WithCancellation(cancellationToken))
    {
        yield return item;
    }
}

1. 移除不必要的Task.Run包裹 #

EF Core的异步查询本身属于IO密集型操作，自带非阻塞特性，无需用Task.Run包裹。直接await PerformSearchAsync(...)即可，既保留异步非阻塞特性，又避免线程切换带来的上下文冲突。

2. 正确管理DbContext生命周期 #

DbContext是线程不安全的，且需及时释放资源。使用using语句包裹DbContext，确保每次查询结束后自动释放，避免资源泄漏导致的重复查询异常。

3. 优化查询避免客户端评估 #

将tableMappings的返回类型改为IQueryable<BasePerson>（假设所有PersonXXX类继承自BasePerson），这样可以直接访问Mobile属性，让EF Core将查询逻辑翻译成SQL在数据库端执行，大幅提升性能，同时避免客户端评估引发的潜在问题。

修改后的代码示例 #

// 直接调用，无需Task.Run
await PerformSearchAsync(query, progress, cancellationTokenSource.Token);

// 修改tableMappings的泛型类型
private Dictionary<TableType, Func<IFDBDbContext, IQueryable<BasePerson>>> tableMappings = new Dictionary<TableType, Func<IFDBDbContext, IQueryable<BasePerson>>>
{
    { TableType.T935, db => db.Set<Person935>().AsQueryable<BasePerson>() },
    { TableType.T936, db => db.Set<Person936>().AsQueryable<BasePerson>() },
    { TableType.T937, db => db.Set<Person937>().AsQueryable<BasePerson>() },
    { TableType.T938, db => db.Set<Person938>().AsQueryable<BasePerson>() },
    { TableType.T939, db => db.Set<Person939>().AsQueryable<BasePerson>() },
    { TableType.T93033, db => db.Set<Person93033>().AsQueryable<BasePerson>() }
};

public async Task PerformSearchAsync(string query, IProgress<int> progress, CancellationToken cancellationToken)
{
    // 使用using确保DbContext及时释放
    using var db = new IFDBDbContext();

    var tableQueries = new List<IAsyncEnumerable<BasePerson>>();
    
    var tableType = GetTableType(SearchQuery);

    if (tableMappings.TryGetValue(tableType, out var dbQuery))
    {
        var dbSet = dbQuery(db);
        tableQueries.Add(GetSearchResultsAsync(dbSet, query, cancellationToken));
    }

    // Track progress
    int totalTables = tableQueries.Count;
    int completedTables = 0;
    int completedItems = 0;

    foreach (var tableQuery in tableQueries)
    {
        await foreach (var item in tableQuery.WithCancellation(cancellationToken))
        {
            completedItems++;

            dispatcherQueue.TryEnqueue(() =>
            {
                DataList.Add(item);
                _tmpCompletedItems = completedItems;
            });
        }

        completedTables++;
        progress.Report((completedTables * 100) / totalTables);
    }

    dispatcherQueue.TryEnqueue(() =>
    {
        ShowStatus(DataList.Count);
    });
}

private async IAsyncEnumerable<BasePerson> GetSearchResultsAsync(IQueryable<BasePerson> dbSet, string query, [EnumeratorCancellation] CancellationToken cancellationToken)
{
    // 直接访问BasePerson的Mobile属性，避免EF.Property的客户端评估
    await foreach (var item in dbSet
        .Where(x => x.Mobile != null && x.Mobile.Contains(query))
        .AsAsyncEnumerable()
        .WithCancellation(cancellationToken))
    {
        yield return item;
    }
}

额外优化建议 #

• 为BasePerson.Mobile字段创建SQLite索引，900万条数据的模糊查询会非常慢，索引能大幅提升查询速度。
• 考虑分页返回结果，避免一次性加载大量数据到内存，同时减少UI更新的压力。

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