Default value for Required fields in Entity Framework migrations?
I've added the [Required] data annotation to one of my models in an ASP.NET MVC application. After creating a migration, running the Update-Database command results in the following error:
Cannot insert the value NULL into column 'Director', table 'MOVIES_cf7bad808fa94f89afa2e5dae1161e78.dbo.Movies'; column does not allow nulls. UPDATE fails. The statement has been terminated.
This is due to some records having NULL in their Director columns. How can I automatically change those values to some default (say "John Doe") director?
Here is my model:
public class Movie
{
public int ID { get; set; }
[Required]
public string Title { get; set; }
[DataType(DataType.Date)]
public DateTime ReleaseDate { get; set; }
[Required]
public string Genre { get; set; }
[Range(1,100)]
[DataType(DataType.Currency)]
public decimal Price { get; set; }
[StringLength(5)]
public string Rating { get; set; }
[Required] /// <--- NEW
public string Director { get; set; }
}
and here is my latest migration:
public partial class AddDataAnnotationsMig : DbMigration
{
public override void Up()
{
AlterColumn("dbo.Movies", "Title", c => c.String(nullable: false));
AlterColumn("dbo.Movies", "Genre", c => c.String(nullable: false));
AlterColumn("dbo.Movies", "Rating", c => c.String(maxLength: 5));
AlterColumn("dbo.Movies", "Director", c => c.String(nullable: false));
}
public override void Down()
{
AlterColumn("dbo.Movies", "Director", c => c.String());
AlterColumn("dbo.Movies", "Rating", c => c.String());
AlterColumn("dbo.Movies", "Genre", c => c.String());
AlterColumn("dbo.Movies", "Title", c => c.String());
}
}
In addition to the answer from @webdeveloper and @Pushpendra, you need to manually add updates to your migration to update existing rows. For example:
public override void Up()
{
Sql("UPDATE [dbo].[Movies] SET Title = 'No Title' WHERE Title IS NULL");
AlterColumn("dbo.Movies", "Title", c => c.String(nullable: false,defaultValue:"MyTitle"));
}
This is because AlterColumn produces DDL to set the default of the column to some specific value in the table specification. The DDL does not affect existing rows in the database.
You're actually making two changes at the same time (setting the default and making the column NOT NULL) and each of them is valid individually, but since you're making the two at the same time, you can expect the system to 'intelligently' realize your intent and set all NULL values to the default value, but this is not what's expected all the time.
Suppose you're only setting the default value for the column, and not making it NOT NULL. You obviously don't expect all the NULL records to be updated with the default you provide.
So, in my opinion, this is not a bug, and I don't want EF to update my data in the ways that I don't explicitly tell it to do. The developer is responsible to instruct the system about what to do with the data.
If I remember correctly, something like this should work:
AlterColumn("dbo.Movies", "Director", c => c.String(nullable: false, defaultValueSql: "'John Doe'"));
Note: The defaultValueSql parameter value is treated as a verbatim SQL statement, so if the required value is effectively a string, like the John Doe example, then single quotes are required around the value.
IS NULL check by your query.
"'John Doe'" - you need to use SQL quotes.
AlterColumn update current values? It is a DDL (not DML) command.
public partial class AddDataAnnotationsMig : DbMigration
{
public override void Up()
{
AlterColumn("dbo.Movies", "Title", c => c.String(nullable: false,defaultValue:"MyTitle"));
AlterColumn("dbo.Movies", "Genre", c => c.String(nullable: false,defaultValue:"Genre"));
AlterColumn("dbo.Movies", "Rating", c => c.String(maxLength: 5));
AlterColumn("dbo.Movies", "Director", c => c.String(nullable: false,defaultValue:"Director"));
}
public override void Down()
{
AlterColumn("dbo.Movies", "Director", c => c.String());
AlterColumn("dbo.Movies", "Rating", c => c.String());
AlterColumn("dbo.Movies", "Genre", c => c.String());
AlterColumn("dbo.Movies", "Title", c => c.String());
}
}
not sure if this option was always around but just ran into a similar issue, found that i was able to set the default value without running any manual updates using the following
defaultValueSql: "'NY'"
I got an error when the value provided was "NY" then i realized that they are expecting a SQL value like "GETDATE()" so i tried "'NY'" and that did the trick
the entire line looks like this
AddColumn("TABLE_NAME", "State", c => c.String(maxLength: 2, nullable: false, defaultValueSql: "'NY'"));
Thanks to this answer, got me on the right track
Since EF Core 2.1, you can use MigrationBuilder.UpdateData to change values before altering the column (cleaner than using raw SQL):
protected override void Up(MigrationBuilder migrationBuilder)
{
// Change existing NULL values to NOT NULL values
migrationBuilder.UpdateData(
table: tableName,
column: columnName,
value: valueInsteadOfNull,
keyColumn: columnName,
keyValue: null);
// Change column type to NOT NULL
migrationBuilder.AlterColumn<ColumnType>(
table: tableName,
name: columnName,
nullable: false,
oldClrType: typeof(ColumnType),
oldNullable: true);
}
Many of the other responses focus on how to manually intervene when these issues occur.
After generating the Migration perform either of the following changes to the migration: Modify the Column definition to include a defaultValue or defaultSql statement: AlterColumn("dbo.Movies", "Director", c => c.String(nullable: false, default: "")); Inject a SQL statement to pre-fill the existing columns, before the AlterColumn: Sql("UPDATE dbo.Movies SET Director = '' WHERE Director IS NULL");
Keep in mind that manual changes applied to a migration script will be overwritten if you re-scaffold the migration. For the first solution, it is pretty easy to extend EF to define a default value on a field automatically as part of the migration generation.
NOTE: EF doesn't automatically do this for you because the default value implementation would be different for each RDBMS provider, but also because default values have less meaning in a pure EF runtime because each row insert will provide the current value for each property, even if it is null, so the default value constraint never gets evaluated. This AlterColumn statement is the only time that the default constraint comes into play, I guess this become a lower priority for the team that designed the SQL Server Migration Implementation.
The following solution combines attribute notation, model configuration conventions, and column annotations to pass through metadata to a custom Migration code generator. Steps 1 and 2 can be replaced with fluent notation for each affected field if you are not using attribute notation. There are a lot of techniques in play here, feel free to use some or all, I hope that there is value for everyone here
Declare the Default Value Create or re-purpose an existing attribute to define the default value to use, for this example we will create a new attribute called DefaultValue that inherits from ComponentModel.DefaultValueAttribute, as the usage is intuitive and there is a chance that existing code bases already implement this attribute. With this implementation you only need to use this specific attribute to access DefaultValueSql which is useful for dates and other custom scenarios. Implementation [DefaultValue("Insert DefaultValue Here")]
[Required] /// <--- NEW
public string Director { get; set; }
// Example of default value sql
[DefaultValue(DefaultValueSql: "GetDate()")]
[Required]
public string LastModified { get; set; }
Attrribute Definition namespace EFExtensions
{
///
I found that just using Auto-Property Initializer on entity property is enough to get the job done.
For example:
public class Thing {
public bool IsBigThing { get; set; } = false;
}
For some reason, that I was unable to explain myself the approved answer does no longer works for me.
It worked on another app, on the one that I am working it doesn't.
So, an alternative, but quite inefficient, solution would be to override the SaveChanges() Method as shown bellow. This method should be on the Context class.
public override int SaveChanges()
{
foreach (var entry in ChangeTracker.Entries().Where(entry => entry.Entity.GetType().GetProperty("ColumnName") != null))
{
if (entry.State == EntityState.Added)
{
entry.Property("ColumnName").CurrentValue = "DefaultValue";
}
}
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- Entity Framework vs LINQ to SQL
- How do I view the SQL generated by the Entity Framework?
- Fastest Way of Inserting in Entity Framework
- Entity Framework - Include Multiple Levels of Properties
- Reset Entity-Framework Migrations
- Entity Framework 5 Updating a Record
- No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient'
- Entity Framework 6 Code first Default value
