Using Concat for model/seed data in migrations may truncate long strings

[ajcvickers]

Originally reported as #23634 (comment) by @saliksaly.

Using CONCAT for strings is not so good due to string truncation to 4000 chars in SQL server :-(
Now the migrations run without error (Microsoft.Data.SqlClient.SqlException (0x80131904): The concat function requires 2 to 254 arguments.) but my seeded strings are now truncated in the database. Silently!

Here are some resources for this SQL server behavior:
https://stackoverflow.com/questions/4547821/using-varcharmax-with-string-concatenation-in-sql-server-2005
https://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated
https://sqlundercover.com/2017/09/27/concatenation-truncation-are-your-strings-being-truncated/

Is there any workaround for this? We have had CSHTML templates in seed data and now it would not be posible in new version of .net?

[ajcvickers]

/cc @bricelam

[smitpatel]

My preliminary experimentation says things are not as straight-forward as in posts.
Results of CONCAT is the highest sized nvarchar from arguments.
Results of + is highest sized nvarchar from the 2 arguments according to type-precedence rules due to implicit casting.

[saliksaly]

My preliminary experimentation says things are not as straight-forward as in posts.
Results of CONCAT is the highest sized nvarchar from arguments.
Results of + is highest sized nvarchar from the 2 arguments according to type-precedence rules due to implicit casting.

Here is a nice explanation of the Data type precedence used:
https://stackoverflow.com/questions/1371383/for-nvarcharmax-i-am-only-getting-4000-characters-in-tsql/1371584#1371584

There is a workaround:
concat with an additional nvarchar(max) member (can be empty)

Let's try:

This returns only 4000 chars:
SELECT CONCAT(REPLICATE(N'A',4000), REPLICATE(N'B',4000), REPLICATE(N'C',4000))

This returns everything:
SELECT CONCAT(CAST('' AS NVARCHAR(MAX)), REPLICATE(N'A',4000), REPLICATE(N'B',4000), REPLICATE(N'C',4000))

(inspired here: https://sqlundercover.com/2017/09/27/concatenation-truncation-are-your-strings-being-truncated/)

[smitpatel]

@saliksaly - I have gone through the official T-Sql documentation. My point is that regardless of issue happening due to data type precedence, same data type is applied for Concat function and + operator. So using of Concat is not in itself an issue.

[smitpatel]

efcore/src/EFCore.SqlServer/Migrations/SqlServerMigrationsSqlGenerator.cs

Lines 1911 to 1919 in 843e35b

	if (!omitVariableDeclarations)
	{
	builder.Append("DECLARE @description AS sql_variant")
	.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
	}
	builder.Append("SET @description = ")
	.Append(Literal(description))
	.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);

Comments declare variable of type sql_variant but still uses string type mapping to print out. sql_variant is incompatible with (n)varchar(max) so such casting generate invalid SQL. Further the limitation on sp_addextendedproperty value (which is sql_variant type) is 7500 bytes at max. So comments which crosses 4000 characters and contains line breaks will be truncated (probably at 3750 character mark) and it cannot be fixed at all.

In order to fix this issue, we need to change the migration code to stop using string type mapping to print sql_variant literal else fix for this issue is generating invalid SQL for a working case of comments.

cc: @bricelam