dotnet / dotnet/efcore

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

Open
#27,323 2 comments 0 reactions 0 assignees View on GitHub
area-migrations customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.