.NET 6升级后WriteToServerAsync无法正常工作求助

将原本运行正常的.NET 5项目升级至.NET 6后，批量插入数据的代码出现异常。核心问题出在await sqlBulkCopy.WriteToServerAsync(dataTable);：

• 调用同步方法sqlBulkCopy.WriteToServer(dataTable);时，报错：发生一个或多个错误。(用户‘Devadmin’登录失败。)
• 使用Task.Run(async () => await sqlBulkCopy.WriteToServerAsync(dataTable));执行无报错，但数据并未插入数据库。

public async Task AddBulkAsync(List<T> entityList)
{
    try
    {
        if (entityList.Count > 0)
        {
            using (var sqlBulkCopy = GetSqlBulkCopy(this._dbContext, this._dbContext.Database.CurrentTransaction))
            {
                sqlBulkCopy.BatchSize = 10000;
                sqlBulkCopy.BulkCopyTimeout = 1800;
                var dataTable = GetDataTable(entityList, sqlBulkCopy);
                await sqlBulkCopy.WriteToServerAsync(dataTable);
            }
        }
    }
    catch (Exception)
    {
        throw;
    }
}

internal DataTable GetDataTable<T>(IList<T> entities, SqlBulkCopy sqlBulkCopy)
{
    var dataTable = new DataTable();
    var columnsDict = new Dictionary<string, object>();
    var ownedEntitiesMappedProperties = new HashSet<string>();

    var type = entities[0].GetType();
    var entityType = this._dbContext.Model.FindEntityType(type);
    var entityPropertiesDict = entityType.GetProperties().ToDictionary(a => a.Name, a => a);
    var entityNavigationOwnedDict = entityType.GetNavigations().Where(a => a.GetTargetType().IsOwned()).ToDictionary(a => a.Name, a => a);
    var properties = type.GetProperties();

    foreach (var property in properties)
    {
        if (entityPropertiesDict.ContainsKey(property.Name))
        {
            var propertyEntityType = entityPropertiesDict[property.Name];
            string columnName = propertyEntityType.GetColumnName();

            var propertyType = property.PropertyType;
            var underlyingType = Nullable.GetUnderlyingType(propertyType);
            if (underlyingType != null)
            {
                propertyType = underlyingType;
            }

            dataTable.Columns.Add(columnName, propertyType);
            columnsDict.Add(property.Name, null);
        }
    }

    foreach (var entity in entities)
    {
        foreach (var property in properties)
        {
            if (entityPropertiesDict.ContainsKey(property.Name))
            {
                var propertyValue = property.GetValue(entity, null);
                if (property.PropertyType == typeof(Guid) && (Guid)propertyValue == default(Guid))
                {
                    propertyValue = Guid.NewGuid();
                }

                columnsDict[property.Name] = propertyValue;
            }
        }
        var record = columnsDict.Values.ToArray();
        dataTable.Rows.Add(record);
    }

    foreach (DataColumn item in dataTable.Columns)  //Add mapping
    {
        sqlBulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
    }
    string schema = entityType.GetSchema() != null ? entityType.GetSchema() : "dbo";
    dataTable.TableName = schema + "." + entityType.GetTableName();
    sqlBulkCopy.DestinationTableName = dataTable.TableName;
    return dataTable;
}

private SqlBulkCopy GetSqlBulkCopy(DbContext dbContext, IDbContextTransaction transaction)
{
    var sqlConnection = dbContext.Database.GetDbConnection().ConnectionString;
    if (transaction == null)
    {
        return new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints);
    }
    else
    {
        var sqlTransaction = (SqlTransaction)transaction.GetDbTransaction();
        return new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints);
    }
}

核心问题 #

1. 连接复用错误：原GetSqlBulkCopy方法通过连接字符串新建SqlBulkCopy，而非复用DbContext已有的数据库连接。这会创建全新的数据库连接，其身份验证上下文可能与DbContext的连接不一致，在.NET 6的连接管理逻辑下触发登录失败。
2. 事务未关联：即使存在DbContext事务，代码也未将事务传入SqlBulkCopy，导致批量操作不在事务上下文内，同时进一步加剧连接冲突。
3. Task.Run的无效性：使用Task.Run时，using块的SqlBulkCopy会在主线程结束后被立即释放，后台线程的写入操作还未完成就失去依赖对象，因此数据无法插入且无异常抛出。

修复步骤 #

1. 修复GetSqlBulkCopy方法，复用DbContext连接与事务 #

修改方法，直接使用DbContext的已存在连接，并正确关联事务：

private SqlBulkCopy GetSqlBulkCopy(DbContext dbContext, IDbContextTransaction transaction)
{
    var dbConnection = dbContext.Database.GetDbConnection();
    // 确保连接处于打开状态
    if (dbConnection.State != System.Data.ConnectionState.Open)
    {
        dbConnection.Open();
    }

    var sqlConnection = (SqlConnection)dbConnection;
    if (transaction == null)
    {
        return new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints, null);
    }
    else
    {
        var sqlTransaction = (SqlTransaction)transaction.GetDbTransaction();
        return new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints, sqlTransaction);
    }
}

2. 移除冗余的try-catch块 #

原代码中的try-catch仅直接抛出异常，无任何额外处理，反而会丢失异常的原始调用栈信息，建议直接删除：

public async Task AddBulkAsync(List<T> entityList)
{
    if (entityList.Count > 0)
    {
        using (var sqlBulkCopy = GetSqlBulkCopy(this._dbContext, this._dbContext.Database.CurrentTransaction))
        {
            sqlBulkCopy.BatchSize = 10000;
            sqlBulkCopy.BulkCopyTimeout = 1800;
            var dataTable = GetDataTable(entityList, sqlBulkCopy);
            await sqlBulkCopy.WriteToServerAsync(dataTable);
        }
    }
}

3. 避免使用Task.Run包装异步操作 #

直接调用await sqlBulkCopy.WriteToServerAsync(dataTable)即可，无需用Task.Run包装，确保异步操作在正确的上下文内完成。

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