How to remove default value from non nullable column in code first entity framework

Question

I have created a table with column like this

builder.Property(b => b.IndicatorPriority)
    .HasColumnName("IndicatorPriority") //Optional, Priority
    .HasColumnType("INT")
    .IsRequired();

I did not specify any default value for this column but the migration generated using add-migration this, has default value

migrationBuilder.AddColumn<int>(
    name: "IndicatorPriority",
    schema: "Internal",
    table: "IndicatorStrategyMapping",
    type: "INT",
    nullable: false,
    defaultValue: 0);

How can I remove this default value binding in code first?

I tried adding these lines in my column definition

.HasDefaultValue(null)

and

.HasDefaultValueSql(null)

but no migration change is detected by these lines.

How can I delete the default value link for non nullable columns?

Update

As asked in one comment, so to clarify, I do want a non nullable column with no default value to force user supply value for this column while inserting records

I can't understand the problem, you using 'IsRequired' property and also want remove default value? What is the sense? try to remove 'IsRequired' property. — Serhii Matvienko
– Serhii Matvienko, Commented Jul 7, 2021 at 7:57
@SergiyMatvienko - And I am not getting why it is so hard to get the question, is it some unexpected requirement to have a required column but no default value to force user provide the value while inserting record? — Pawan Nogariya
– Pawan Nogariya, Commented Jul 7, 2021 at 8:19
I believe if your user will try insert model without required field he will get a validation error message. But in your case you running migration script, and it's automatically inserting value to the required field. That's it why I can't unterstand what is the problem everything works as expected. — Serhii Matvienko
– Serhii Matvienko, Commented Jul 7, 2021 at 9:02
@SergiyMatvienko - Which scripts are inserting values to the required field? The script I posted is the schema creation script and not the insertion script. And when I insert the object without this value using Entity framework, it does not give me any error but takes the default value. — Pawan Nogariya
– Pawan Nogariya, Commented Jul 7, 2021 at 10:03
Okay, so what is the goal? If you set 'not null' and 'no default value', so what value should be there?) — Serhii Matvienko
– Serhii Matvienko, Commented Jul 7, 2021 at 10:57

asinino · Accepted Answer · 2023-05-02 10:55:35Z

So as answered by @serhii-matvienko here link to comment we can conclude:

This happens when the developer adds a column ta an existing table, and the new column is NOT NULL. If it does not specify a default value the DB will remove all rows from that table because the new column will invalidate each row (can't insert a row where that new column is null).

To make the job easier for the developer (specially for new commers) they make the assumption the the column will be filled-up with the CLR Type default value, for ex:

Int will have default value 0
Date will have default value '0001-01-01 00:00:00'
...

So I suggest that the developer take a manual migration edit on this change because EF Core always add a default value. It is the responsibility of the programmer to decide what to do with this new column. He must define the proper way to do it for each use case, one of the following actions:

A) let that snippet has it is, at the end of the migration Up method redefine the column to have the default value has not null so new rows will require the user to insert a proper value but the existing ones will be pre-filled with 0 (this will not work if the column is a foreign key);

B) change the current snippet to let NULLs in the new column, make a SQL script that will fill-up this column for all rows of current table (like getting the foreign key value), after having the values change the type of the column to NOT NULL.

Hoàng Trần · Accepted Answer · 2021-07-07 08:58:48Z

1

Just remove the part ~~defaultValue: 0~~ before executing the command database update

answered Jul 7, 2021 at 8:58

Hoàng Trần

6109 silver badges15 bronze badges

1 Comment

Pawan Nogariya Over a year ago

That file is generated automatically. So I do not want to change it manually. I want to do it in the right way so that the EF will generate the change properly

Collectives™ on Stack Overflow

How to remove default value from non nullable column in code first entity framework

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related