STRING_AGG in EF Core 7+ - Stack Overflow

admin管理员组

文章数量:1435859

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]

• entity-framework-core

Share Improve this question Follow edited Nov 15, 2024 at 21:44 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Nov 15, 2024 at 19:13 KamranKamran 1,38011 gold badge1919 silver badges3131 bronze badges 1

• The word "group" in the documentation is telling. – Gert Arnold Commented Nov 17, 2024 at 21:57

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

It 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]

本文标签： STRINGAGG in EF Core 7Stack Overflow