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?
A bit of background: If the aggregating input to
STRING_AGG()
is aVARCHAR(N)
orNVARCHAR(N)
type, the result type is limited toVARCHAR(8000)
orNVARCHAR(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 toCONVERT(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: