How to use cast to varchar(max) with STRING_AGG using Entity Framework Core

Question

I am trying to get the following part of the query to work using Entity Framework Core and Linq in C#

STRING_AGG(cast(EntityName as varchar(max)), ' , ') AS AllEntityNames

I need to concat all the EntityName. However, I get an error:

STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

when I am using

.Select(g => new
             {
                 AllEntityNames = string.Join(",", g.Select(p => p.EntityName )) 
             })

How can we do the cast to varchar part of the SQL query in my C# code?

Answer 1

A bit of background: If the aggregating input to STRING_AGG() is a VARCHAR(N) or NVARCHAR(N) type, the result type is limited to VARCHAR(8000) or NVARCHAR(4000), respectively. If the result would be longer, the posted error occurs.

To allow longer results, the input string must be converted to a xVARCHAR(MAX) type (referred to as "LOB types" in the error message). The question is: How do we do that from LINQ to SQL and EF Core.

According to the Function Mappings of the Microsoft SQL Server Provider article, the function call Convert.ToString(value) will map to CONVERT(nvarchar(max), @value).

This is likely intended for conversions from types other-than-string, but will also work with a string type.

Your code would then be something like:

.Select(g => new {
    // The Convert.ToString() below will cast EntityName to NVARCHAR(MAX).
    // This will avoid the 8000 char limit for STRING_AGG() on VARCHAR(N).
    // This comment is here for a reason. Leave it and the ToString() call.
    AllEntityNames = string.Join(
        ",",
        g.Select(p => Convert.ToString(p.EntityName))
    ) 
})