Update pattern for scaffolding column default constraints #13613
Description
When using database first, creating a table (MSSQL) with a a bit field that has a default value produces strange results.
MSSQL Script:
CREATE TABLE [dbo].[Account] (
[Id] INT NOT NULL IDENTITY(1,1),
[EmailAddress] VARCHAR(256) NOT NULL,
[PasswordSalt] BINARY(512) NOT NULL,
[PasswordHash] BINARY(512) NOT NULL,
[Verified] BIT NOT NULL CONSTRAINT DF_Account_Verified DEFAULT (0),
[UtcDateCreated] DATETIME NOT NULL CONSTRAINT DF_Account_UtcDateCreated DEFAULT (GETUTCDATE()),
[Active] BIT NOT NULL CONSTRAINT DF_Account_Active DEFAULT (1),
CONSTRAINT PK_Account_Id PRIMARY KEY ([Id]),
CONSTRAINT UQ_Account_EmailAddress UNIQUE ([EmailAddress])
);
Scaffolding command:
Scaffold-DbContext "Server=172.16.1.140;Database=DocStudio;User Id=<uid>;Password=<pass>;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -f
Generated model :
using System;
using System.Collections.Generic;
namespace Namespace.Models
{
public partial class Account
{
public int Id { get; set; }
public string EmailAddress { get; set; }
public byte[] PasswordSalt { get; set; }
public byte[] PasswordHash { get; set; }
public bool Verified { get; set; }
public DateTime UtcDateCreated { get; set; }
public bool? Active { get; set; }
}
}
and finally, the fluent OnModelCreating method:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Account>(entity =>
{
entity.HasIndex(e => e.EmailAddress)
.HasName("UQ_Account_EmailAddress")
.IsUnique();
entity.Property(e => e.Active)
.IsRequired()
.HasDefaultValueSql("((1))");
entity.Property(e => e.EmailAddress)
.IsRequired()
.HasMaxLength(256)
.IsUnicode(false);
entity.Property(e => e.PasswordHash)
.IsRequired()
.HasMaxLength(512);
entity.Property(e => e.PasswordSalt)
.IsRequired()
.HasMaxLength(512);
entity.Property(e => e.UtcDateCreated)
.HasColumnType("datetime")
.HasDefaultValueSql("(getutcdate())");
});
}
Several issues here. Note the difference between Verified and Active. Despite being identical in their definition they produce different property types in the model.
The lack of a default value for Verified. There should be a definition like so:
entity.Property(e => e.Verified)
.IsRequired()
.HasDefaultValueSql("((0))");
Missing a primary key definition.
Finally a warning for Active, but not for Verified:
The column 'dbo.Account.Active' would normally be mapped to a non-nullable bool property, but it has a default constraint. Such a column is mapped to a nullable bool property to allow a difference between setting the property to false and invoking the default constraint.