string_agg use in MSSQL
According to this link, starting in EF Core 7.0, both string.Concat and string.Join should translate to using the string_agg function on the database. But when I use that in LINQPad 8 with .NET 8, the generated SQL reported in LINQPad is not showing use of the string_agg function. Is this a LINQPad driver issue that can be fixed?
Example LINQ:
Products
.Select(x => new
{
ProductName = x.Name,
Markets = string.Concat(x.ProductMarkets.Select(y => y.Market.Code))
})
https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions
Comments
Interesting I had the opposite problem where EF core using string_agg when it shouldn't.
I'm not sure whether your query is supposed to use string_agg, but the following Testwind query does
Customers.GroupBy(a=>a.Country).Select(a=>new { a.Key, Cites= String.Concat(a.Select(b=>b.City + ","))})Unfortunately running this gives me the error
because my version of sql server does not support it.
As far as I can see this was reported at https://github.com/dotnet/efcore/issues/30161 and hopefully fixed in https://github.com/dotnet/efcore/commit/309d8f0077d8d583c3fdfe5d449206515096418e which presumably will be in EF Core 9.
Thankfully this only happened on a LinqPad generated EF context and so I just needed to select an older version of EF Core and rerun the query to avoid using string_agg .