Entity Framework 6 Refresher: Changing a nullable field to non-nullable
You may not know this, but on rare occasions business rules and requirements change. Let’s consider a case where our Person
class has a Weight
and a Modified
field.
public class Person
{
public int PersonId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int? Weight { get; set; }
public DateTimeOffset Modified { get; set; }
}
//migration
public override void Up()
{
AddColumn("dbo.People", "Weight", c => c.Int());
AddColumn("dbo.People", "Modified", c => c.DateTimeOffset(precision: 7));
}
After applying this migration, data in our database looks like this.
Let’s imagine that everything runs smoothly for several days and then all of a sudden a project manager or a process manager or a product manager calls and makes a much bigger deal than he needs to about Weight
and Modified
being critical. “No big deal” you think. And off to work you go to make these fields non-nullable.
public class PersonMap : EntityTypeConfiguration<Person>
{
public PersonMap()
{
Property(x => x.Modified).IsRequired();
Property(x => x.Weight).IsRequired();
}
}
public override void Up()
{
AlterColumn("dbo.People", "Weight", c => c.Int(nullable: false));
AlterColumn("dbo.People", "Modified", c => c.DateTimeOffset(nullable: false, precision: 7));
}
In them PMC, you enter update-database
and get a nice long error starting with System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'Weight', table 'RefresherContext.dbo.People'; column does not allow nulls. UPDATE fails.
. Oh. If we run update-database -script
we see the following. The existing records all have null for Weight
and Modified
and this migration is explicitly saying that is not allowed.
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.People')
AND col_name(parent_object_id, parent_column_id) = 'Weight';
IF @var0 IS NOT NULL
EXECUTE('ALTER TABLE [dbo].[People] DROP CONSTRAINT [' + @var0 + ']')
ALTER TABLE [dbo].[People] ALTER COLUMN [Weight] [int] NOT NULL
DECLARE @var1 nvarchar(128)
SELECT @var1 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.People')
AND col_name(parent_object_id, parent_column_id) = 'Modified';
No problem. We can manually update the migration to update the existing values.
public override void Up()
{
Sql("UPDATE dbo.People SET Weight=0 WHERE Weight IS NULL");
Sql("UPDATE dbo.People SET Modified=GetDate() WHERE Modified IS NULL");
AlterColumn("dbo.People", "Weight", c => c.Int(nullable: false));
AlterColumn("dbo.People", "Modified", c => c.DateTimeOffset(nullable: false, precision: 7));
}
The Sql
method here gets us a script that looks something like this.
UPDATE dbo.People SET Weight=0 WHERE Weight IS NULL
UPDATE dbo.People SET Modified=GetDate() WHERE Modified IS NULL
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.People')
AND col_name(parent_object_id, parent_column_id) = 'Weight';
IF @var0 IS NOT NULL
EXECUTE('ALTER TABLE [dbo].[People] DROP CONSTRAINT [' + @var0 + ']')
ALTER TABLE [dbo].[People] ALTER COLUMN [Weight] [int] NOT NULL
DECLARE @var1 nvarchar(128)
SELECT @var1 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.People')
AND col_name(parent_object_id, parent_column_id) = 'Modified';
Leave a Comment