EF Core migration's SQL script for computed column expression differs depending on the way the column is being handled
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
SQL expression for computed columns varies between table creation and individual column creation.
Migration code:
```
migrationBuilder.AddColumn(
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(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
Contributor guide
Assessment
This issue has not been assessed yet.