Database column: 'Allow Nulls' is not being changed during a migration

[InteXX]

I am unsure as to whether this problem is within the scope of this library.

I generated a migration, not providing any configurations:

Protected Overrides Sub Up(Builder As MigrationBuilder)
  Builder.CreateTable(
    name:="Videos",
    columns:=Function(Table) New With {
      .Id = Table.Column(Of Integer)(type:="int", nullable:=False).Annotation("SqlServer:Identity", "1, 1"),
      .ViewKey = Table.Column(Of String)(type:="nvarchar(max)", nullable:=True), ' <-- Column of interest
      .IsProcessed = Table.Column(Of Boolean)(type:="bit", nullable:=False),
      .Name = Table.Column(Of String)(type:="nvarchar(max)", nullable:=True),
      .KeyAdded = Table.Column(Of Date)(type:="datetime2", nullable:=False)
    },
    constraints:=Sub(Table) Table.PrimaryKey("PK_Videos", Function(x) x.Id)
  )
End Sub

I ran Update-Database and ended up with a schema that looked like this:

I then added a few configurations:

Public Class VideoConfiguration
  Implements IEntityTypeConfiguration(Of Video)

  Public Sub Configure(Builder As EntityTypeBuilder(Of Video)) Implements IEntityTypeConfiguration(Of Video).Configure
    Builder.Property(Function(Video) Video.IsProcessed).IsRequired.HasDefaultValue(False)
    Builder.Property(Function(Video) Video.KeyAdded).IsRequired.HasDefaultValue(#1/1/1753#)
    Builder.Property(Function(Video) Video.ViewKey).IsRequired.HasDefaultValue(String.Empty).HasMaxLength(15) ' <-- Column of interest
    Builder.Property(Function(Video) Video.Name).IsRequired.HasDefaultValue(String.Empty).HasMaxLength(15)
  End Sub
End Class

The new migration looks like this:

Builder.AlterColumn(Of Date)(
  name:="KeyAdded",
  table:="Videos",
  type:="datetime2",
  nullable:=False, ' <-- Disallow nulls
  defaultValue:=New Date(1753, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified),
  oldClrType:=GetType(Date),
  oldType:="datetime2"
)

After running the migration, however, certain elements of the schema remained unchanged:

The default constraints were added as expected:

...but the 'Allow Nulls' and column length properties were not altered.

Here's the SQL code that ran during the second migration:

Opening connection to database 'Db' on server 'DEV'.
Opened connection to database 'Db' on server 'DEV'.

Beginning transaction with isolation level 'Unspecified'.
Began transaction with isolation level 'ReadCommitted'.

Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Videos]') AND [c].[name] = N'ViewKey');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Videos] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Videos] ADD DEFAULT N'' FOR [ViewKey];
Executed DbCommand (20ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Videos]') AND [c].[name] = N'ViewKey');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Videos] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Videos] ADD DEFAULT N'' FOR [ViewKey];

Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var1 sysname;
SELECT @var1 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Videos]') AND [c].[name] = N'Name');
IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Videos] DROP CONSTRAINT [' + @var1 + '];');
ALTER TABLE [Videos] ADD DEFAULT N'' FOR [Name];
Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var1 sysname;
SELECT @var1 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Videos]') AND [c].[name] = N'Name');
IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Videos] DROP CONSTRAINT [' + @var1 + '];');
ALTER TABLE [Videos] ADD DEFAULT N'' FOR [Name];

Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var2 sysname;
SELECT @var2 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Videos]') AND [c].[name] = N'KeyAdded');
IF @var2 IS NOT NULL EXEC(N'ALTER TABLE [Videos] DROP CONSTRAINT [' + @var2 + '];');
ALTER TABLE [Videos] ADD DEFAULT '1753-01-01T00:00:00.0000000' FOR [KeyAdded];
Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var2 sysname;
SELECT @var2 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Videos]') AND [c].[name] = N'KeyAdded');
IF @var2 IS NOT NULL EXEC(N'ALTER TABLE [Videos] DROP CONSTRAINT [' + @var2 + '];');
ALTER TABLE [Videos] ADD DEFAULT '1753-01-01T00:00:00.0000000' FOR [KeyAdded];

Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var3 sysname;
SELECT @var3 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Videos]') AND [c].[name] = N'IsProcessed');
IF @var3 IS NOT NULL EXEC(N'ALTER TABLE [Videos] DROP CONSTRAINT [' + @var3 + '];');
ALTER TABLE [Videos] ADD DEFAULT CAST(0 AS bit) FOR [IsProcessed];
Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var3 sysname;
SELECT @var3 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Videos]') AND [c].[name] = N'IsProcessed');
IF @var3 IS NOT NULL EXEC(N'ALTER TABLE [Videos] DROP CONSTRAINT [' + @var3 + '];');
ALTER TABLE [Videos] ADD DEFAULT CAST(0 AS bit) FOR [IsProcessed];

Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20220804063021_002', N'6.0.7');
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20220804063021_002', N'6.0.7');

Committing transaction.
Committed transaction.
Disposing transaction.

Closing connection to database 'Db' on server 'DEV'.
Closed connection to database 'Db' on server 'DEV'.

As we can see, the migration instructions for altering the column length and 'Allow Nulls' setting were not sent to the database.

I next undertook to separate the default constraint configurations into their own migration, and then the 'Allow Nulls' and column length configurations into a third migration. This time all schema updates were processed as expected:

Again, I am unsure as to whether this behavior is expected or whether this issue is within the scope of this library.

[lorcQc]

Hi @InteXX,
I tried to reproduce this issue without success.

Here is the project I used to do the tests. It uses version 6.0.7 of the official SqlServer provider.
Issue77.zip

You can use the command Script-Migration -From Initial -To AddVideoConfig to inspect the generated SQL for a particular migration.

The SQL script is generated by the database provider from the migration file. If the generated migration file looks correct, the problem may be with the provider.

[InteXX]

I tried to reproduce this issue without success

I'll give it another go.

[ErikEJ]

@InteXX Did you ever investigate this further?

[InteXX]

@ErikEJ

Due to time constraints, I did not.

But I will try again to get to it soon.