解决C#中SQL多列结果转换为值元组的类型转换错误

问题描述 #

编写了如下C#异步方法，通过执行带OUTPUT子句的SQL插入语句获取PurchaseOrderId和PONum，期望以值元组(int purchaseOrderId, string PONum)形式返回结果：

public async Task<(int purchaseOrderId, string PONum)> AddPurchaseOrder(PurchaseOrderMaster purchaseOrderMaster)
{
    try
    {
        var sqlQuery = new StringBuilder();
        var paramSearch = new DynamicParameters();

        _ = sqlQuery.Append($@"INSERT INTO PurchaseOrder (PONum, VendorID, WHId, PODate, ReferenceNum, EstArrivalDate, POType, SubTotal, TAX, ShippingHandling, Total, EnteredBy, EnteredOn, IsDelete)
                OUTPUT inserted.PurchaseOrderId, inserted.PONum
                VALUES (FLOOR(RAND() * 900000) + 100000, @VendorID, @WHId, @PODate, @ReferenceNum, @EstArrivalDate, @POType, @SubTotal, @TAX, @ShippingHandling, @Total, @EnteredBy, GETUTCDATE(), 'N');
        ");

        paramSearch.Add("@VendorID", purchaseOrderMaster.VendorID);
        paramSearch.Add("@WHId", purchaseOrderMaster.WHId);
        paramSearch.Add("@PODate", purchaseOrderMaster.PODate);
        paramSearch.Add("@ReferenceNum", purchaseOrderMaster.ReferenceNum);
        paramSearch.Add("@EstArrivalDate", purchaseOrderMaster.EstArrivalDate);
        paramSearch.Add("@POType", purchaseOrderMaster.POType);
        paramSearch.Add("@SubTotal", purchaseOrderMaster.SubTotal);
        paramSearch.Add("@TAX", purchaseOrderMaster.TAX);
        paramSearch.Add("@ShippingHandling", purchaseOrderMaster.ShippingHandling);
        paramSearch.Add("@Total", purchaseOrderMaster.Total);
        paramSearch.Add("@EnteredBy", purchaseOrderMaster.EnteredBy);

        return await dbHelper.ExecuteScalarAsync<(int, string)>(sqlQuery.ToString(), paramSearch).ConfigureAwait(false);
    }
    catch (Exception ex)
    {
        throw new BadRequestException(ex.Message);
    }
}

执行时出现类型转换错误：

{"Invalid cast from 'System.Int32' to 'System.ValueTuple`2[[System.Int32, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.String, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]'."}

问题原因 #

错误根源在于ExecuteScalarAsync方法的特性：它仅返回查询结果的第一列第一行的值，也就是这里的PurchaseOrderId（int类型），无法直接将单个int值转换为包含int和string的复合值元组，因此抛出类型转换异常。

解决方案 #

使用Dapper的QueryFirstAsync方法替代ExecuteScalarAsync，该方法支持将多列查询结果自动映射到值元组。修改后的代码如下：

public async Task<(int purchaseOrderId, string PONum)> AddPurchaseOrder(PurchaseOrderMaster purchaseOrderMaster)
{
    try
    {
        var sqlQuery = new StringBuilder();
        var paramSearch = new DynamicParameters();

        _ = sqlQuery.Append($@"INSERT INTO PurchaseOrder (PONum, VendorID, WHId, PODate, ReferenceNum, EstArrivalDate, POType, SubTotal, TAX, ShippingHandling, Total, EnteredBy, EnteredOn, IsDelete)
                OUTPUT inserted.PurchaseOrderId, inserted.PONum
                VALUES (FLOOR(RAND() * 900000) + 100000, @VendorID, @WHId, @PODate, @ReferenceNum, @EstArrivalDate, @POType, @SubTotal, @TAX, @ShippingHandling, @Total, @EnteredBy, GETUTCDATE(), 'N');
        ");

        paramSearch.Add("@VendorID", purchaseOrderMaster.VendorID);
        paramSearch.Add("@WHId", purchaseOrderMaster.WHId);
        paramSearch.Add("@PODate", purchaseOrderMaster.PODate);
        paramSearch.Add("@ReferenceNum", purchaseOrderMaster.ReferenceNum);
        paramSearch.Add("@EstArrivalDate", purchaseOrderMaster.EstArrivalDate);
        paramSearch.Add("@POType", purchaseOrderMaster.POType);
        paramSearch.Add("@SubTotal", purchaseOrderMaster.SubTotal);
        paramSearch.Add("@TAX", purchaseOrderMaster.TAX);
        paramSearch.Add("@ShippingHandling", purchaseOrderMaster.ShippingHandling);
        paramSearch.Add("@Total", purchaseOrderMaster.Total);
        paramSearch.Add("@EnteredBy", purchaseOrderMaster.EnteredBy);

        // 替换为QueryFirstAsync，直接映射到具名值元组
        return await dbHelper.QueryFirstAsync<(int purchaseOrderId, string PONum)>(sqlQuery.ToString(), paramSearch).ConfigureAwait(false);
    }
    catch (Exception ex)
    {
        throw new BadRequestException(ex.Message);
    }
}

补充说明 #

• QueryFirstAsync<T>会自动匹配SQL返回列名和值元组的成员名，只要两者名称一致即可完成映射（这里inserted.PurchaseOrderId对应元组的purchaseOrderId，inserted.PONum对应PONum）。
• 如果SQL列名和元组成员名不匹配，可以在SQL中给列添加别名，例如：OUTPUT inserted.PurchaseOrderId AS purchaseOrderId, inserted.PONum AS PONum，确保映射准确。

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