EF Core migration's SQL script for computed column expression differs depending on the way the column is being handled

[CesarD]

SQL expression for computed columns varies between table creation and individual column creation.

Migration code:

migrationBuilder.AddColumn<string>(
				name: "Field",
				table: "Table",
				type: "nvarchar(25)",
				maxLength: 25,
				nullable: true,
				computedColumnSql: "CASE WHEN MarketTypeId = '0a5360e6-54cb-405a-adc7-5354e384532d' THEN LEFT(Code, 10)\r\n                   WHEN MarketTypeId = '4c663578-c608-4166-b8ed-8d6e14760d56' THEN LEFT(Code, 9)\r\n                   WHEN MarketTypeId = '9c5f3970-659c-47fd-b143-1003e1df9b93' AND LEN(Code) < 20 THEN SUBSTRING(Code, 0, LEN(Code))\r\n    			   WHEN MarketTypeId = '9c5f3970-659c-47fd-b143-1003e1df9b93' AND LEN(Code) >= 20 THEN LEFT(Code, 20) ELSE LEFT(Code, 10)\r\n                   END"
			);

Generated SQL script code:

EXEC(CONCAT(CAST(N'ALTER TABLE [Table] ADD [Field] AS CASE WHEN MarketTypeId = ''0a5360e6-54cb-405a-adc7-5354e384532d'' THEN LEFT(Code, 10)' AS nvarchar(max)), nchar(13), nchar(10), N'                   WHEN MarketTypeId = ''4c663578-c608-4166-b8ed-8d6e14760d56'' THEN LEFT(Code, 9)', nchar(13), nchar(10), N'                   WHEN MarketTypeId = ''9c5f3970-659c-47fd-b143-1003e1df9b93'' AND LEN(Code) < 20 THEN SUBSTRING(Code, 0, LEN(Code))', nchar(13), nchar(10), N'    			   WHEN MarketTypeId = ''9c5f3970-659c-47fd-b143-1003e1df9b93'' AND LEN(Code) >= 20 THEN LEFT(Code, 20) ELSE LEFT(Code, 10)', nchar(13), nchar(10), N'                   END'));

This script portion generated by EF Core CLI is invalid for the SQL Server most recent versions.
Basically, a verbatim string that could contain \r\n characters in its content (used to make the original code more readable by developers), is parsed into a script that is not correctly parsed by SQL Server (neither SQL Server Development edition nor Azure DevOps Release tasks).

Now, on the other side, for when the table is being created from scratch, this is what is generated on both sides:

In the CreateTable() method from the migration, the specific field is generated this way:

Field = table.Column<string>(type: "nvarchar(20)", maxLength: 20, nullable: true, computedColumnSql: "CASE WHEN MarketTypeId = '0a5360e6-54cb-405a-adc7-5354e384532d' THEN LEFT(Code, 10)\r\n               WHEN MarketTypeId = '4c663578-c608-4166-b8ed-8d6e14760d56' THEN LEFT(Code, 9)\r\n               WHEN MarketTypeId = '9c5f3970-659c-47fd-b143-1003e1df9b93' THEN LEFT(Code, 20) ELSE LEFT(Code, 10)\r\n               END"),

and the SQL script code that this generates is the following:

CREATE TABLE [Table] (
        [Field] AS CASE WHEN MarketTypeId = '0a5360e6-54cb-405a-adc7-5354e384532d' THEN LEFT(Code, 10)
                   WHEN MarketTypeId = '4c663578-c608-4166-b8ed-8d6e14760d56' THEN LEFT(Code, 9)
                   WHEN MarketTypeId = '9c5f3970-659c-47fd-b143-1003e1df9b93' THEN LEFT(Code, 20) ELSE LEFT(Code, 10)
                   END,

As you can see, the computed column expression there is not parsed as a dynamic query executed with EXEC() SQL function... And there's no problem with having the same verbatim string with carrier returns and new line characters in it.

I would assume the mechanism for generating both code snippets is different for each case, thus the difference that I'm noticing...
Shouldn't this be unified in order to be able to generate a more consistent code?

Include provider and version information

EF Core version: 6.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer)
Target framework: .NET 6.0
Operating system: Any
IDE: N/A

[jums]

Just ran into the same problem during deployment today, using v6.0.1. I had added a new property + HasComputedColumnSql definition with a verbatim string containing line breaks for nicer formatting. The workaround was of course to remove the line breaks and format it in a less readable way.

[bricelam]

Related to #20723