Computed column incorrectly created as nullable
- 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
Assessment
This issue has not been assessed yet.