I'm trying to use STRING_AGG on the SQL Server side. According to the documentation, from EF Core 7.0 onwards,
string.Join(separator, group.Select(x => x.Property))
should be translated to STRING_AGG(Property, @separator), but this translation isn't happening.
What am I missing here?
Here is my code:
var names = _context.Account
.Select(l => new
{
Names = string.Join(" | ", l.Customers.Select(x=>x.FirstName))
}).ToListAsync();
But that, is translated to this query
SELECT
[t].[AccountId], [c].[FirstName], [c].[CustomerID]
FROM
( FROM [Accounts] AS [l]
) AS [t]
LEFT JOIN
[Customer] AS [c] ON [t].[AccountId]= [c].[AccountId]
While I expect to see
SELECT
STRING_AGG([c].[FirstName], N' | ')
FROM
[Customer] AS [c]
WHERE
[l].[AccountId] = [c].[AccountId]) AS [Names]
FROM [Accounts] AS [l]
I'm trying to use STRING_AGG on the SQL Server side. According to the documentation, from EF Core 7.0 onwards,
string.Join(separator, group.Select(x => x.Property))
should be translated to STRING_AGG(Property, @separator), but this translation isn't happening.
What am I missing here?
Here is my code:
var names = _context.Account
.Select(l => new
{
Names = string.Join(" | ", l.Customers.Select(x=>x.FirstName))
}).ToListAsync();
But that, is translated to this query
SELECT
[t].[AccountId], [c].[FirstName], [c].[CustomerID]
FROM
( FROM [Accounts] AS [l]
) AS [t]
LEFT JOIN
[Customer] AS [c] ON [t].[AccountId]= [c].[AccountId]
While I expect to see
SELECT
STRING_AGG([c].[FirstName], N' | ')
FROM
[Customer] AS [c]
WHERE
[l].[AccountId] = [c].[AccountId]) AS [Names]
FROM [Accounts] AS [l]
- The word "group" in the documentation is telling. – Gert Arnold Commented Nov 17, 2024 at 21:57
1 Answer
Reset to default 1It seems that STRING_AGG is designed to be used as part of a grouping query. This is how it is implemented in EF Core. However, we can mimic this functionality by grouping with a constant key. For example:
The following query in EF Core produces a STRING_AGG SQL, but the generated SQL might look questionable:
var query = _context.Accounts
.SelectMany(a => a.Customers)
.GroupBy(c => 1) // Grouping by a constant
.Select(g => new
{
Names = string.Join(", ", g.Select(c => c.FirstName))
});
Generated SQL:
SELECT COALESCE(STRING_AGG([t].[FirstName], N', '), N'') AS [Names]
FROM (
SELECT [c].[FirstName], 1 AS [Key]
FROM [Accounts] AS [a]
INNER JOIN [Customers] AS [c] ON [a].[Id] = [c].[AccountId]
) AS [t]
GROUP BY [t].[Key]
While this approach works, a better result can be achieved by grouping with a meaningful, non-constant key. For example:
var query = _context.Accounts
.SelectMany(a => a.Customers)
.GroupBy(c => c.AccountId) // Grouping by AccountId
.Select(g => new
{
Names = string.Join(", ", g.Select(c => c.FirstName))
});
Generated SQL:
SELECT COALESCE(STRING_AGG([c].[FirstName], N', '), N'') AS [Names]
FROM [Accounts] AS [a]
INNER JOIN [Customers] AS [c] ON [a].[Id] = [c].[AccountId]
GROUP BY [c].[AccountId]