dotnet / dotnet/efcore

Computed column incorrectly created as nullable

Open
#32,451 3 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

## File a bug

Hello,

I'm using SQL Server 2019 with EF Core 7.0 migrations,

I have a table with 2 columns:

- One column is a `date, null`,
- the other is a `datetimeoffset(7), not null`

I want to add a computed `SortDate` column which coalesces these two into a `not null` date for sorting the rows.

My approach might not be the best, but I found that my code incorrectly creates the computed column as a nullable column.

### Include your code

My fluent API config:

``` csharp
modelBuilder.Property(document => document.SortDate)
.HasColumnName("SortDate")
.HasColumnType("date")
.HasComputedColumnSql("coalesce(DocumentDate, cast(CreatedAt as date))", stored: true)
.IsRequired();
```

The corresponding Migration:

(Note it contains `nullable: false`)

``` csharp
migrationBuilder.AddColumn(
name: "SortDate",
schema: "dbo",
table: "Documents",
type: "date",
nullable: false,
computedColumnSql: "coalesce(DocumentDate, cast(CreatedAt as date))",
stored: true)
```

The generated SQL using `dotnet ef migrations script`:

``` sql
EXEC(N'ALTER TABLE [dbo].[Documents] ADD [SortDate] AS coalesce(DocumentDate, cast(CreatedAt as date)) PERSISTED');
```

And I believe this is wrong, it should contain `NOT NULL`:

Expected:

``` sql
EXEC(N'ALTER TABLE [dbo].[Documents] ADD [SortDate] AS coalesce(DocumentDate, cast(CreatedAt as date)) PERSISTED NOT NULL');
```

Without `NOT NULL`, I believe SQL Server will use some heuristic to determine whether the computation can return NULL. I think SQL Server believes my computation can return a NULL because I use a cast.

I think it's a good idea to always specify `NULL` or `NOT NULL` in the generated SQL for computed columns.

### Include provider and version information

EF Core version: 7.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 6.0
Operating system: Debian 11
IDE: VS Code

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.