Entity Framework Core + Npgsql是否支持字符串聚合？Linq转换咨询

我正尝试将以下PostgreSQL查询通过Npgsql转换为Linq：

var result = _dbContext.Substrates.FromSql(@$"SELECT 
                                                vs.*,
                                                STRING_AGG(DISTINCT p.product_ref, ', ') AS product_ref,
                                                STRING_AGG(DISTINCT cu.acr, ', ') AS acr
                                            FROM 
                                                portal_data.v_substrates vs
                                            LEFT JOIN portal_data.custart c ON c.article_id = vs.id_substrate
                                            LEFT JOIN portal_data.product_components pc ON pc.id_component = vs.id_substrate
                                            LEFT JOIN portal_data.products p ON p.id_product = pc.id_product
                                            LEFT JOIN portal_data.customers cu ON cu.id_customer = c.id_customer
                                            WHERE
                                                c.id_customer = {customerId}
                                            GROUP BY
                                                vs.id_substrate,
                                                vs.substrate_type,
                                                vs.substrate_creation_date,
                                                vs.substrate_status,
                                                vs.substrate_invent_qty,
                                                vs.record_copy_date,
                                                vs.mapped_status
                                            ");

我误以为Npgsql的EF.Functions.StringToArray()会对应SQL里的STRING_AGG，于是尝试编写如下Linq代码：

var substrates = from substrate in _dbContext.Substrates
                 join custart in _dbContext.CustomerArticles on substrate.IdSubstrate equals custart.ArticleId
                 join component in _dbContext.ProductComponents on substrate.IdSubstrate equals component.IdComponent
                 join product in _dbContext.Products on component.IdProduct equals product.IdProduct
                 join customer in _dbContext.Customers on custart.IdCustomer equals customer.Id                              
                 where custart.IdCustomer == customerId
                 select new SubstrateExtended
                 {
                     IdSubstrate = substrate.IdSubstrate,
                     SubstrateType = substrate.SubstrateType,
                     SubstrateCreationDate = substrate.SubstrateCreationDate,
                     SubstrateStatus = substrate.SubstrateStatus,
                     SubstrateInvetoryQuantity = substrate.SubstrateInvetoryQuantity,
                     RecordCopyDate = substrate.RecordCopyDate,
                     MappedStatus = substrate.MappedStatus,
                     ProductRef = EF.Functions.StringToArray(product.ProductRef, ", "),
                     Acr = EF.Functions.StringToArray(customer.Accronym, ", ")
                 };

但此方法无法正常工作，因为EF.Functions.StringToArray()返回的是string[]类型，和原SQL中STRING_AGG返回的拼接字符串不符。请问是否存在无需获取全部聚合项即可将该SQL查询转换为Linq的方法？

你混淆了StringToArray和STRING_AGG的作用：StringToArray是把单个字符串按分隔符拆成数组，而你需要的是聚合多个字符串值为单个拼接字符串，对应PostgreSQL的STRING_AGG，在EF Core中应该用EF.Functions.StringAgg（注意是StringAgg，不是StringToArray）。

同时要对应原SQL的LEFT JOIN和DISTINCT去重逻辑，Linq实现如下：

var substrates = from substrate in _dbContext.Substrates
                 // 左连接CustomerArticles，对应原SQL的LEFT JOIN custart
                 join custart in _dbContext.CustomerArticles 
                     on substrate.IdSubstrate equals custart.ArticleId into custartGroup
                 from custart in custartGroup.DefaultIfEmpty()
                 where custart != null && custart.IdCustomer == customerId
                 // 左连接ProductComponents
                 join component in _dbContext.ProductComponents 
                     on substrate.IdSubstrate equals component.IdComponent into componentGroup
                 from component in componentGroup.DefaultIfEmpty()
                 // 左连接Products
                 join product in _dbContext.Products 
                     on component?.IdProduct equals product.IdProduct into productGroup
                 from product in productGroup.DefaultIfEmpty()
                 // 左连接Customers
                 join customer in _dbContext.Customers 
                     on custart?.IdCustomer equals customer.Id into customerGroup
                 from customer in customerGroup.DefaultIfEmpty()
                 // 按substrate分组，对应原SQL的GROUP BY vs.*
                 group new { product, customer } by substrate into grouped
                 select new SubstrateExtended
                 {
                     IdSubstrate = grouped.Key.IdSubstrate,
                     SubstrateType = grouped.Key.SubstrateType,
                     SubstrateCreationDate = grouped.Key.SubstrateCreationDate,
                     SubstrateStatus = grouped.Key.SubstrateStatus,
                     SubstrateInvetoryQuantity = grouped.Key.SubstrateInvetoryQuantity,
                     RecordCopyDate = grouped.Key.RecordCopyDate,
                     MappedStatus = grouped.Key.MappedStatus,
                     // 用StringAgg配合Distinct实现原SQL的STRING_AGG(DISTINCT ...)
                     ProductRef = EF.Functions.StringAgg(grouped.Select(x => x.product?.ProductRef).Distinct(), ", "),
                     Acr = EF.Functions.StringAgg(grouped.Select(x => x.customer?.Accronym).Distinct(), ", ")
                 };

关键点说明： #

• 用into ... DefaultIfEmpty()实现SQL的LEFT JOIN，避免内连接过滤掉无匹配的记录
• 按substrate分组，对应原SQL中对vs表所有字段的分组逻辑
• 使用EF.Functions.StringAgg配合Distinct()，完全对应原SQL的STRING_AGG(DISTINCT ...)语义
• 处理左连接后可能出现的null值（比如无匹配的product或customer）

确保你安装了最新版本的Npgsql.EntityFrameworkCore.PostgreSQL包，StringAgg函数需要该包提供支持。

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