如何让SQL存储过程返回多值列以映射C#的ICollection/HashSet？

一、SQL端：把多值合并成单列返回 #

你不需要用用户定义表，直接用SQL的聚合函数就能把一条主记录对应的多个关联值合并成单列，两种常用方式：

1. 字符串聚合（适合简单值） #

用SQL Server的STRING_AGG函数，把关联表的字段用分隔符拼起来：

SELECT 
    mp.Id,
    mp.Name,
    -- 把ForeignProperty的Value字段用逗号分隔合并，空值时返回NULL
    STRING_AGG(fp.Value, ',') WITHIN GROUP (ORDER BY fp.Id) AS ForeignPropertyValues
FROM MainProperty mp
LEFT JOIN ForeignProperty fp ON mp.Id = fp.MainPropertyId
GROUP BY mp.Id, mp.Name

如果字段值可能包含分隔符，换个特殊符号比如|||就行。

2. JSON聚合（适合复杂对象） #

如果要保留ForeignProperty的多个字段（比如Id、Value），用JSON_ARRAYAGG（SQL Server 2022+或Azure SQL支持）：

SELECT 
    mp.Id,
    mp.Name,
    -- 把每个ForeignProperty转成JSON对象，再聚合成数组
    JSON_ARRAYAGG(JSON_OBJECT('Id', fp.Id, 'Value', fp.Value)) AS ForeignPropertiesJson
FROM MainProperty mp
LEFT JOIN ForeignProperty fp ON mp.Id = fp.MainPropertyId
GROUP BY mp.Id, mp.Name

老版本SQL Server可以用FOR JSON PATH拼接替代：

SELECT 
    mp.Id,
    mp.Name,
    (
        SELECT fp.Id, fp.Value 
        FROM ForeignProperty fp 
        WHERE fp.MainPropertyId = mp.Id 
        FOR JSON PATH
    ) AS ForeignPropertiesJson
FROM MainProperty mp

二、C#端：把单列转成ICollection/HashSet #

1. 处理字符串聚合结果 #

先定义一个和存储过程返回字段匹配的临时DTO，再转成你的MainProperty模型：

// 临时DTO，对应存储过程返回的列
public class MainPropertyTemp
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ForeignPropertyValues { get; set; }
}

// 转换逻辑
var tempList = // 调用存储过程获取的List<MainPropertyTemp>
var mainList = tempList.Select(temp => new MainProperty
{
    Id = temp.Id,
    Name = temp.Name,
    ForeignProperty = string.IsNullOrWhiteSpace(temp.ForeignPropertyValues)
        ? new HashSet<ForeignProperty>()
        : temp.ForeignPropertyValues.Split(',')
            .Select(val => new ForeignProperty { Value = val })
            .ToHashSet()
}).ToList();

2. 处理JSON聚合结果 #

用System.Text.Json反序列化，直接转成HashSet：

public class MainPropertyTemp
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ForeignPropertiesJson { get; set; }
}

// 转换
var mainList = tempList.Select(temp => new MainProperty
{
    Id = temp.Id,
    Name = temp.Name,
    ForeignProperty = string.IsNullOrWhiteSpace(temp.ForeignPropertiesJson)
        ? new HashSet<ForeignProperty>()
        : JsonSerializer.Deserialize<HashSet<ForeignProperty>>(temp.ForeignPropertiesJson)
}).ToList();

三、关键注意点 #

• SQL里必须按MainProperty的主键分组，不然会返回重复的主记录，导致C#转换后出现错误。
• 如果用EF Core，也可以考虑在调用存储过程后手动用Include关联，但这种方式不如直接在SQL返回聚合值高效。

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