如何将两个基于相同claimId查询的SQL结果合并到同一个数组中

1 调整模型定义 #

首先需要在Tires类中新增属性来承载对应轮胎的服务信息，修改后的代码如下：

public class Tires
{
    public int claimId { get; set; } = 0;
    public int tireId { get; set; } = 0;
    public string serviceReason { get; set; } = "";
    public string TireService { get; set; } = "";
    public int quantity { get; set; } = 0;
    public string brand { get; set; } = "";
    public string model { get; set; } = "";
    public int pricePerUnit { get; set; } = 0;
    public int price { get; set; } = 0;
    public string tireTypeId { get; set; } = "";
    public string widthID { get; set; } = "";
    public string heightID { get; set; } = "";
    public string diameterID { get; set; } = "";
    // 新增对应轮胎服务属性
    public IEnumerable<TireServiceInfo> tireservice { get; set; } = Enumerable.Empty<TireServiceInfo>();
}

如果不需要保留顶层的tireservice返回字段，也可以同步修改TireList类删除对应属性，不删除也可以，返回的时候不赋值即可。

2 修改查询方法逻辑 #

使用C#内置的Zip方法按顺序配对两个查询结果，将服务数据嵌入对应轮胎对象，修改后的方法代码如下：

public async Task<TireList> getAllTireClaims(Tires model)
{
    var parameters = new DynamicParameters();
    parameters.Add("@claimId", model.claimId);
    var getAllTires = await _sqlconnection.QueryAsync<Tires>($@"            
    SELECT
    Tire.ID as tireId, Claim.ID as claimId,
    RequestType.Name as serviceReason,
    Quantity, PricePerUnit, Tire.Price as price,
    RequestTireTypes.Name as tireTypeId, 
    Brand, Model, RequestTireWidth.Name as widthID,
    RequestTireHeight.Name as heightID, RequestTireDiameter.Name as diameterID
    FROM Claim
    INNER JOIN RequestType ON Claim.RequestTypeID = RequestType.ID
    INNER JOIN ClaimCrossTire ON Claim.ID = ClaimCrossTire.ClaimID
    INNER JOIN Tire ON ClaimCrossTire.TireID = Tire.ID
    INNER JOIN RequestTireTypes ON Tire.TireTypeID = RequestTireTypes.ID
    INNER JOIN RequestTireWidth ON Tire.WidthID = RequestTireWidth.ID
    INNER JOIN RequestTireHeight ON Tire.HeightID = RequestTireHeight.ID
    INNER JOIN RequestTireDiameter ON Tire.DiameterID = RequestTireDiameter.ID
    WHERE ClaimCrossTire.ClaimID = @claimId AND RequestTypeID = 3 ", parameters);

    var getTireService = await _sqlconnection.QueryAsync<TireServiceInfo>($@"SELECT TireServiceID
    FROM CostItem cI
    JOIN ClaimCrossCostItem ccrossI ON cI.ID = ccrossI.CostItemID
    JOIN Claim c  ON ccrossI.ClaimID = c.ID
    WHERE (TireServiceID = 3 OR TireServiceID = 5)
    AND c.ID = @claimId", parameters);

    // 合并两个集合：按索引顺序配对，将对应服务信息赋值给轮胎对象
    var mergedTires = getAllTires.Zip(getTireService, (tire, service) => 
    {
        tire.tireservice = new List<TireServiceInfo> { service };
        return tire;
    });

    return new TireList()
    {
        tires = mergedTires
        // 不需要顶层tireservice可以注释掉该行
        // tireservice = getTireService
    };
}

注意说明 #

• Zip方法会严格按照两个集合的顺序一一配对，完全适配你提到的两个查询行数一致、顺序对应的场景，不会出现匹配错误
• 如果后续需要支持单条轮胎对应多条服务记录的场景，只需调整配对逻辑即可，当前方案完全适配现有需求

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