C#调用多表行数统计存储过程报错，单变量调用正常求助

问题根源 #

你的存储过程当前返回的是9行1列的结果集，列名统一为第一个查询的BurnOut，每行对应一个表的计数。但你的DTO模型类是包含9个属性（如BurnOut、CIP等）的单对象，ORM无法将多行结果映射到单个DTO实例，因此调用报错；而仅传递单个变量时，存储过程返回单行单列，能匹配单个属性，所以可以正常运行。

修正后的存储过程（匹配DTO结构） #

将所有表的计数作为单行多列返回，这样就能和DTO的属性一一对应：

ALTER PROCEDURE [dbo].[GetTotalNumberOfFormsSubmitted]
AS
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM Client_BurnOuts) AS BurnOut,
        (SELECT COUNT(*) FROM Client_CIPEnergyCrisises) AS CIP,
        (SELECT COUNT(*) FROM Client_HomeRepairs) AS HomeRepair,
        (SELECT COUNT(*) FROM Client_MedicalTravelAssistances) AS MadicalTravelAssistan,
        (SELECT COUNT(*) FROM Client_EmergencyAssistances) AS EmergencyAssistance,
        (SELECT COUNT(*) FROM Client_Heatings) AS Heating,
        (SELECT COUNT(*) FROM Client_DukeEnergyFoundations) AS DuckFoundation,
        (SELECT COUNT(*) FROM Client_EmergencyFoodPantries) AS FoodPantry,
        (SELECT COUNT(*) FROM Client_MedicalEquipments) AS MedicalEquipment
END

扩展：直接返回百分比（适配图表需求） #

如果需要直接获取百分比用于图表展示，可以先计算总计数，再生成各表的计数和百分比（处理总计数为0的情况避免除零错误）：

ALTER PROCEDURE [dbo].[GetTotalNumberOfFormsSubmittedWithPercentage]
AS
BEGIN
    -- 计算所有表的总行数
    DECLARE @Total INT = (
        SELECT COUNT(*) FROM Client_BurnOuts
        + COUNT(*) FROM Client_CIPEnergyCrisises
        + COUNT(*) FROM Client_HomeRepairs
        + COUNT(*) FROM Client_MedicalTravelAssistances
        + COUNT(*) FROM Client_EmergencyAssistances
        + COUNT(*) FROM Client_Heatings
        + COUNT(*) FROM Client_DukeEnergyFoundations
        + COUNT(*) FROM Client_EmergencyFoodPantries
        + COUNT(*) FROM Client_MedicalEquipments
    )

    SELECT 
        BurnOutCount = (SELECT COUNT(*) FROM Client_BurnOuts),
        BurnOutPercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_BurnOuts) * 100.0 / @Total END,
        CIPCount = (SELECT COUNT(*) FROM Client_CIPEnergyCrisises),
        CIPPercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_CIPEnergyCrisises) * 100.0 / @Total END,
        HomeRepairCount = (SELECT COUNT(*) FROM Client_HomeRepairs),
        HomeRepairPercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_HomeRepairs) * 100.0 / @Total END,
        MadicalTravelAssistanCount = (SELECT COUNT(*) FROM Client_MedicalTravelAssistances),
        MadicalTravelAssistanPercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_MedicalTravelAssistances) * 100.0 / @Total END,
        EmergencyAssistanceCount = (SELECT COUNT(*) FROM Client_EmergencyAssistances),
        EmergencyAssistancePercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_EmergencyAssistances) * 100.0 / @Total END,
        HeatingCount = (SELECT COUNT(*) FROM Client_Heatings),
        HeatingPercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_Heatings) * 100.0 / @Total END,
        DuckFoundationCount = (SELECT COUNT(*) FROM Client_DukeEnergyFoundations),
        DuckFoundationPercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_DukeEnergyFoundations) * 100.0 / @Total END,
        FoodPantryCount = (SELECT COUNT(*) FROM Client_EmergencyFoodPantries),
        FoodPantryPercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_EmergencyFoodPantries) * 100.0 / @Total END,
        MedicalEquipmentCount = (SELECT COUNT(*) FROM Client_MedicalEquipments),
        MedicalEquipmentPercentage = CASE WHEN @Total = 0 THEN 0 ELSE (SELECT COUNT(*) FROM Client_MedicalEquipments) * 100.0 / @Total END
END

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