C#使用原生SQL查询返回带List属性的实体 替换LINQ提升性能

你当前的写法属于典型的N+1查询问题：查询主列表1次，遍历每个条目再查1次材料，数据量越大性能损耗越严重。以下两种更优的原生SQL实现方案：

方案1：单次查询+字符串聚合（性能最优） #

利用数据库自带的字符串聚合函数，一次查询直接拿到所有Quote及对应拼接后的材料列表，仅需1次数据库交互。

适配不同数据库的聚合函数： #

• SQL Server 2017+/PostgreSQL：使用STRING_AGG
• MySQL：使用GROUP_CONCAT
• 低版本SQL Server（2016及之前）：使用FOR XML PATH实现拼接

实现步骤： #

1. 先定义临时映射DTO接收聚合结果：

public class QuoteTemp
{
    public int Id { get; set; }
    public int Type { get; set; }
    // 存储拼接后的材料字符串
    public string MaterialsStr { get; set; }
}

2. 执行单次查询后转换为目标模型：

// 以SQL Server为例的SQL语句，可根据自己的表结构调整关联条件
string sql = @"
SELECT q.Id, q.Type, STRING_AGG(m.MaterialName, ',') AS MaterialsStr
FROM Quote q
LEFT JOIN Materials m ON q.Id = m.QuoteId
GROUP BY q.Id, q.Type
";

List<QuoteTemp> tempList = db.Database.SqlQuery<QuoteTemp>(sql).ToList();
List<Quote> result = tempList.Select(temp => new Quote
{
    Id = temp.Id,
    Type = temp.Type,
    Materials = string.IsNullOrEmpty(temp.MaterialsStr) 
        ? new List<string>() 
        : temp.MaterialsStr.Split(',').ToList()
}).ToList();

方案2：两次查询+内存关联（无分隔符冲突风险） #

如果材料名称可能包含你用来拼接的分隔符（比如逗号），可以选择两次查询再在内存中关联赋值，比N+1查询性能高很多。

实现步骤： #

// 1. 查询所有Quote主数据
List<Quote> quotes = db.Database.SqlQuery<Quote>("SELECT Id, Type FROM Quote").ToList();
// 2. 批量查询所有关联的材料，定义临时DTO接收
public class MaterialTemp
{
    public int QuoteId { get; set; }
    public string MaterialName { get; set; }
}
var quoteIds = quotes.Select(q => q.Id.ToString()).ToList();
string materialSql = $@"
SELECT QuoteId, MaterialName 
FROM Materials 
WHERE QuoteId IN ({string.Join(",", quoteIds)})
";
List<MaterialTemp> allMaterials = db.Database.SqlQuery<MaterialTemp>(materialSql).ToList();
// 3. 内存分组后赋值
var materialDict = allMaterials.GroupBy(m => m.QuoteId)
    .ToDictionary(g => g.Key, g => g.Select(m => m.MaterialName).ToList());
foreach(var quote in quotes)
{
    materialDict.TryGetValue(quote.Id, out var materials);
    quote.Materials = materials ?? new List<string>();
}

注意：如果Quote数据量特别大，超过了数据库IN查询的参数限制，可以将Id分批查询，比如每1000个Id为一批查询材料。

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