EF Core批量导入数据：外键验证查询优化与批量提交问题

AI 小北

2026-7-29

Blazor+EF Core批量导入Bid数据的优化方案

我们基于Blazor开发应用，通过API结合Entity Framework Core访问数据库，实现从电子表格导入数据插入到Bid表的功能。Bid表包含7个外键字段，若外键值在关联表中不存在，插入时会触发外键约束违反错误。

当前项目运行正常，现需优化效率并学习相关技术，面临两个核心问题：

验证阶段：当前ValidateImportedBids API循环每个待导入Bid，针对7个外键ID分别发起数据库查询验证存在性，每个Bid产生7次数据库请求。能否合并为一次数据库请求？同事提到用Inner Joins实现，但主表尚未插入新行，如何关联查询？
插入阶段：当前PostImportedBids API若插入时出现外键约束错误，整个批量操作会回滚，无数据提交。能否跳过前置验证，直接执行提交，仅回滚错误行、提交成功行并返回错误行列表？

现有代码片段

Blazor调用API代码

_gridData = await processor.ProcessObject<List<Data.ViewModels.BidImport>>(await apiClient.ValidateImportedBids(_bidItemSetImported));
_gridDataValid = _gridData.Where(g => g.Valid).ToList();
_gridDataImported = await processor.ProcessObject<List<Data.Models.Bid>>(await apiClient.ImportBids(_gridDataValid));
_gridData = _gridData.Where(g => !g.Valid).ToList();

API客户端代码

private readonly string apiBidImport = "/bidimport";
public async Task<HttpResponseMessage> ValidateImportedBids(List<Data.ViewModels.BidImport> items)
{
    _logger.LogInfo($"Validating Bid Item Set Bids{items.Count()}");

    var sentContent = new HttpRequestMessage(HttpMethod.Get,
          new Uri(_httpClient.BaseAddress + apiBidImport + "/validateimportedbids"))
    {
        Content = new StringContent(JsonConvert.SerializeObject(items), Encoding.UTF8, "application/json")
    };

    return await SendAsync<List<Data.ViewModels.BidImport>>(sentContent);
}

public async Task<HttpResponseMessage> ImportBids(List<Data.ViewModels.BidImport> items)
{
    _logger.LogInfo($"Updating Bid Item Set Bids{items.Count()}");
    var model = new List<Bid>();
    var MappedItems = BidExtensions.Map(model, items);

    var sentContent = new HttpRequestMessage(HttpMethod.Post,
          new Uri(_httpClient.BaseAddress + apiBidImport + "/postimportedbids"))
    {
        Content = new StringContent(JsonConvert.SerializeObject(items), Encoding.UTF8, "application/json")
    };

    return await SendAsync<List<Bid>>(sentContent);
}

验证API代码

/// <summary>
/// Validation of Bids ready for Importing into Bids
/// </summary>
/// <param name="bids">Proc execution : List of bids to be validated</param>
/// <returns>Success and return the List of Validated bids and errors if found</returns>
[HttpGet("validateimportedbids")]
[ProducesResponseType(200)]
[ProducesResponseType(400)]
public async Task<ActionResult<List<BidImport>>> ValidateBidItemSetImportBids([FromBody] List<BidImport> bids)
{
    _logger.LogDebug($"Validating Imported Bids");

    var returnBids = new List<BidImport>();
    foreach (var bid in bids)
    {
         
        bid.Valid = true;
        bid.Exclude = false;
        bid.ErrorMessage = null;

        var error = false;
        var ErrorMessageString = new StringBuilder();
        ErrorMessageString.Append("Error: ");

        // Bid Type Id is Required
        if (string.IsNullOrEmpty(bid.Bid_TypeId) || string.IsNullOrWhiteSpace(bid.Bid_TypeId))
        {
            error = true;
            ErrorMessageString.Append("Bid Type Id is Required");
        }
        else
        {
            // Retrieve the given Bid_Type Id
            var bid_TypeId = await _unitOfWork.BidTypes.Get(bid.Bid_TypeId).ConfigureAwait(false);
            if (bid_TypeId == null)
            {
                error = true;
                ErrorMessageString.Append("Bid Type Id is Invalid");
                 
            }
        }

        // Bid Class Id is Required
        if (string.IsNullOrEmpty(bid.Bid_ClassId) || string.IsNullOrWhiteSpace(bid.Bid_ClassId))
        {
            error = true;
            ErrorMessageString.Append("(Bid Class Id is Required)");
        }
        else
        {
            // Retrieve the given Bid_Class Id
            var bid_ClassId = await _unitOfWork.BidClasses.Get(bid.Bid_ClassId).ConfigureAwait(false);
            if (bid_ClassId == null)
            {
                error = true;
                ErrorMessageString.Append("(Bid Class Id is Invalid)");
            }
        }

        // Product Line Id is Required
        if (string.IsNullOrEmpty(bid.ProductLineId) || string.IsNullOrWhiteSpace(bid.ProductLineId))
        {
            error = true;
            ErrorMessageString.Append("(Product Line Id is Required)");
        }
        else
        {
            // Retrieve the given Product Line Id
            var bid_ProductLineId = await _unitOfWork.ProductLines.Get(bid.ProductLineId).ConfigureAwait(false);
            if (bid_ProductLineId == null)
            {
                error = true;
                ErrorMessageString.Append("(Product Line Id is Invalid)");
            }
        }

        // Distributor Group Id is Required
        if (string.IsNullOrEmpty(bid.Distributor_GroupId) || string.IsNullOrWhiteSpace(bid.Distributor_GroupId))
        {
            error = true;
            ErrorMessageString.Append("(Distributor Group Id is Required)");
        }
        else
        {
            // Retrieve the given Distributor Groupd Id
            var bid_DistributorGroupId = await _unitOfWork.DistributorGroups.Get(bid.Distributor_GroupId).ConfigureAwait(false);
            if (bid_DistributorGroupId == null)
            {
                error = true;
                ErrorMessageString.Append("(Distributor Group Id is Invalid)");
            }
        }

        // Distributor Id is Required
        if (string.IsNullOrEmpty(bid.DistributorId) || string.IsNullOrWhiteSpace(bid.DistributorId))
        {
            error = true;
            ErrorMessageString.Append("(Distributor Id is Required)");
        }
        else
        {
            // Retrieve the given Distributor Id
            var bid_DistributorId = await _unitOfWork.Distributors.Get(bid.DistributorId).ConfigureAwait(false);
            if (bid_DistributorId == null)
            {
                error = true;
                ErrorMessageString.Append("(Distributor Id is Invalid)");
            }
        }

        // SalesPerson Default Id is Required
        if (string.IsNullOrEmpty(bid.SalesPersonDefaultId) || string.IsNullOrWhiteSpace(bid.SalesPersonDefaultId))
        {
            error = true;
            ErrorMessageString.Append("(Sales Person Default Id is Required)");
        }
        else
        {
            // Retrieve the given Sales Person Default Id
            var bid_SalesPersonDefaultId = await _unitOfWork.SalesPeople.Get(bid.SalesPersonDefaultId).ConfigureAwait(false);
            if (bid_SalesPersonDefaultId == null)
            {
                error = true;
                ErrorMessageString.Append("(Sales Person Default Id is Invalid)");
            }
        }

        // Bid Status Id is Required
        if (string.IsNullOrEmpty(bid.Bid_StatusId) || string.IsNullOrWhiteSpace(bid.Bid_StatusId))
        {
            error = true;
            ErrorMessageString.Append("(Bid Status Id is Required)");
        }
        else
        {
            // Retrieve the given Bid Status Id
            var bid_StatusId = await _unitOfWork.BidStatuses.Get(bid.Bid_StatusId).ConfigureAwait(false);
            if (bid_StatusId == null)
            {
                error = true;
                ErrorMessageString.Append("(Bid Status Id is Invalid)");
            }
        }

        if (error)
        {
            bid.Valid = false;
            bid.Exclude = true;
            bid.ErrorMessage = ErrorMessageString.ToString();
        }

        returnBids.Add(bid);

    }
    return returnBids;
}

提交API代码

/// <summary>
/// Create the bid in the repository matching the submitted bid
/// </summary>
/// <param name="passedObject">The item you want to create</param>
/// <response code="200">Returns newly created bids</response>
/// <response code="400">Error adding the bid</response>
[HttpPost("postimportedbids")]
[ProducesResponseType(200)]
[ProducesResponseType(400)]
[ProducesResponseType(401)]
[ProducesResponseType(404)]
public async Task<ActionResult <List<Data.Models.Bid>>> Post([FromBody] List<BidImport> passedObject)
{

    var items = passedObject;
    var model = new List<Data.Models.Bid>();
    var newBids = new List<Data.Models.Bid>();
    var MappedBids = BidExtensions.Map(model, items);
    _logger.LogDebug($"Importing Bid Set Import Bids");
    try
    {
        // Loop through the model and generate a new BidId
        foreach(var bid in MappedBids)
        {
            bid.Id = await _unitOfWork.Bids.GetNextBidNumber().ConfigureAwait(false);
            await _unitOfWork.Bids.Add(bid);
            var result = await _unitOfWork.Complete().ConfigureAwait(false);
            newBids.Add(bid);
        }

        return newBids;
         
    }
    catch (Exception ex)
    {
        return BadRequest("Error Inserting Imported Bids");
    }

}

问题解决方案

问题1：合并外键验证的数据库请求

不需要关联未插入的Bid主表，核心思路是批量提取待验证外键ID，一次性查询关联表中存在的ID，再在内存中校验每个Bid的外键有效性，将数据库请求从N*7次减少到7次（N为待导入Bid数量）。

优化后的验证API代码

[HttpPost("validateimportedbids")] // 修正为Post，符合HTTP规范
[ProducesResponseType(200)]
[ProducesResponseType(400)]
public async Task<ActionResult<List<BidImport>>> ValidateBidItemSetImportBids([FromBody] List<BidImport> bids)
{
    _logger.LogDebug($"Validating Imported Bids");

    // 1. 提取所有待验证的外键ID（去重减少查询量）
    var bidTypeIds = bids.Where(b => !string.IsNullOrWhiteSpace(b.Bid_TypeId)).Select(b => b.Bid_TypeId).Distinct().ToList();
    var bidClassIds = bids.Where(b => !string.IsNullOrWhiteSpace(b.Bid_ClassId)).Select(b => b.Bid_ClassId).Distinct().ToList();
    var productLineIds = bids.Where(b => !string.IsNullOrWhiteSpace(b.ProductLineId)).Select(b => b.ProductLineId).Distinct().ToList();
    var distributorGroupIds = bids.Where(b => !string.IsNullOrWhiteSpace(b.Distributor_GroupId)).Select(b => b.Distributor_GroupId).Distinct().ToList();
    var distributorIds = bids.Where(b => !string.IsNullOrWhiteSpace(b.DistributorId)).Select(b => b.DistributorId).Distinct().ToList();
    var salesPersonIds = bids.Where(b => !string.IsNullOrWhiteSpace(b.SalesPersonDefaultId)).Select(b => b.SalesPersonDefaultId).Distinct().ToList();
    var bidStatusIds = bids.Where(b => !string.IsNullOrWhiteSpace(b.Bid_StatusId)).Select(b => b.Bid_StatusId).Distinct().ToList();

    // 2. 批量查询每个关联表中存在的ID（每个表仅1次请求）
    var existingBidTypeIds = await _unitOfWork.BidTypes.Query().Where(t => bidTypeIds.Contains(t.Id)).Select(t => t.Id).ToListAsync();
    var existingBidClassIds = await _unitOfWork.BidClasses.Query().Where(c => bidClassIds.Contains(c.Id)).Select(c => c.Id).ToListAsync();
    var existingProductLineIds = await _unitOfWork.ProductLines.Query().Where(p => productLineIds.Contains(p.Id)).Select(p => p.Id).ToListAsync();
    var existingDistributorGroupIds = await _unitOfWork.DistributorGroups.Query().Where(g => distributorGroupIds.Contains(g.Id)).Select(g => g.Id).ToListAsync();
    var existingDistributorIds = await _unitOfWork.Distributors.Query().Where(d => distributorIds.Contains(d.Id)).Select(d => d.Id).ToListAsync();
    var existingSalesPersonIds = await _unitOfWork.SalesPeople.Query().Where(s => salesPersonIds.Contains(s.Id)).Select(s => s.Id).ToListAsync();
    var existingBidStatusIds = await _unitOfWork.BidStatuses.Query().Where(s => bidStatusIds.Contains(s.Id)).Select(s => s.Id).ToListAsync();

    // 3. 内存中校验每个Bid的外键
    foreach (var bid in bids)
    {
        bid.Valid = true;
        bid.Exclude = false;
        bid.ErrorMessage = null;
        var errorMessage = new StringBuilder("Error: ");
        var hasError = false;

        // 校验Bid_TypeId
        if (string.IsNullOrWhiteSpace(bid.Bid_TypeId))
        {
            hasError = true;
            errorMessage.Append("Bid Type Id is Required; ");
        }
        else if (!existingBidTypeIds.Contains(bid.Bid_TypeId))
        {
            hasError = true;
            errorMessage.Append("Bid Type Id is Invalid; ");
        }

        // 校验Bid_ClassId
        if (string.IsNullOrWhiteSpace(bid.Bid_ClassId))
        {
            hasError = true;
            errorMessage.Append("Bid Class Id is Required; ");
        }
        else if (!existingBidClassIds.Contains(bid.Bid_ClassId))
        {
            hasError = true;
            errorMessage.Append("Bid Class Id is Invalid; ");
        }

        // 重复上述逻辑处理剩余外键：ProductLineId、Distributor_GroupId、DistributorId、SalesPersonDefaultId、Bid_StatusId

        if (hasError)
        {
            bid.Valid = false;
            bid.Exclude = true;
            bid.ErrorMessage = errorMessage.ToString().TrimEnd(' ', ';');
        }
    }

    return bids;
}

问题2：跳过前置验证，提交成功行并返回错误列表

通过逐个处理Bid插入，独立提交事务，捕获外键约束异常，实现错误行回滚、成功行保留，并返回错误明细。

优化后的提交API代码

// 自定义返回模型，明确区分成功和失败数据
public class BidImportResult
{
    public List<Data.Models.Bid> SuccessBids { get; set; } = new();
    public List<BidImportError> ErrorBids { get; set; } = new();
}

public class BidImportError
{
    public BidImport Bid { get; set; }
    public string ErrorMessage { get; set; }
}

[HttpPost("postimportedbids")]
[ProducesResponseType(200)]
[ProducesResponseType(400)]
[ProducesResponseType(401)]
[ProducesResponseType(404)]
public async Task<ActionResult<BidImportResult>> Post([FromBody] List<BidImport> passedObject)
{
    var items = passedObject;
    var mappedBids = BidExtensions.Map(new List<Data.Models.Bid>(), items);
    _logger.LogDebug($"Importing Bid Set Import Bids");

    var result = new BidImportResult();

    foreach (var (bid, originalImport) in mappedBids.Zip(items, (b, o) => (b, o)))
    {
        try
        {
            bid.Id = await _unitOfWork.Bids.GetNextBidNumber().ConfigureAwait(false);
            _unitOfWork.Bids.Add(bid);
            await _unitOfWork.Complete().ConfigureAwait(false);
            result.SuccessBids.Add(bid);
            // 重置工作单元，清除已跟踪实体，避免错误影响后续操作
            _unitOfWork.Reset();
        }
        catch (DbUpdateException ex)
        {
            // 捕获SQL Server外键约束错误（错误码547，其他数据库需调整）
            if (ex.InnerException is SqlException sqlEx && sqlEx.Number == 547)
            {
                result.ErrorBids.Add(new BidImportError
                {
                    Bid = originalImport,
                    ErrorMessage = "外键约束违反：关联表中不存在对应的ID"
                });
            }
            else
            {
                result.ErrorBids.Add(new BidImportError
                {
                    Bid = originalImport,
                    ErrorMessage = $"插入失败：{ex.Message}"
                });
            }
            _unitOfWork.Reset();
        }
    }

    return result;
}

关键补充：UnitOfWork的Reset方法

需在你的UnitOfWork实现中

EF Core批量导入数据：外键验证查询优化与批量提交问题

现有代码片段 #

Blazor调用API代码 #

API客户端代码 #

验证API代码 #

提交API代码 #

问题解决方案 #

问题1：合并外键验证的数据库请求 #