dotnet
Allow to specify constraint name for default values
Hello,
Is there any way to specify constraint name for default values, for example:
builder.Property(x => x.CreatedDate)
.IsRequired()
.HasColumnType("DATETIME")
.HasDefaultValueSql("GETDATE()")
.HasConstraintName("DF_MyConstraint");
Thanks
@dehghani-mehdi There isn't currently a way to specify a default constraint name.
From the perspective of EF Core, this is not necessary because SQL Sever will generate one for you and we can always address the constraint (e.g. to drop it) without the name through the column (because there can only be one default constraint per column).
However we understand there may be other reasons this can be useful (in fact, you are the second person asking for this). It would be very helpful if you could explain why you need it.
One reason I have experienced is major issues using sqlpackage / SSDT with unnamed constraints
I always using T-SQL instead of wizard for creating tables (or altering them) and I always specify constraint names, I like to have clean names for all things (we have naming convention in our team) Also I have auto-update system (replacing files/ running updated SQL on the fly) for my projects and in some versions I have to drop some constraint and I need to know the name of them. finding the constraint is possible for specific column but not clean as
ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint]
Maybe the reason that I have is not big enough, but adding this feature will be great.
Two click-stops:
- Generate a deterministic name and specify it in the DDL so that SSDT diffs are stable
- Allow customizing the name
@dehghani-mehdi However we understand there may be other reasons this can be useful (in fact, you are the second person asking for this).
And I am a 3rd person
My team and I would also like this feature for the reasons mentioned. It provides flexibility for working with a database from different sources (besides through EF Core). Convention in constraint names makes that much easier.
Count me on too. We too give constraints meaningful names, it gives easy recognition and upgrades via dacpac to multiple databases seamless
Another reason is the ability to compare automatically two database schemas. With names of default constraints generated automatically there is a lot of noise reported.
Glad I found this post, I was about to open my own for the same topic. I think as a general rule of thumb "If I can do it in tSQL I should be able to do it in EF" is what should be observed. I don't like not knowing what my constraints are called, I like to specify everything in order to stay within explicit over implicit. Implicit makes me nervous.
I feel like SQL Server itself has already dropped the ball by giving constraint names the most inconsistent names possible - so I am just reacting to previous bad experiences of having many client databases with different constraint naming resembling a quarter GUID.
Add me to the list requesting this feature. We too have a coding standard with naming conventions for constraint for not only the reason mentioned by others in this thread, but also for portability/decreased ramp up time of resources from one product to another. It is quite handy when the design document is not detailed enough to call out every single data model rule.
Also requesting this feature. This has been bothersome to me for many many years.
I have always thought that default constraints created by EF should be explicitly named in the format "DF_TableName_ColumnName" instead of defaulting to SQL Server generated names (and that behavior should be configurable so the preferred method will be used). Additionally, it should be possible to user-define default constraint names with fluent api to override the context default.
My reasoning being that I should be able to use code first to model an existing schema that was hand designed with traditional tools (SSMS) and get a structurally identical schema when allowing EF to create a new instance in a different database. As someone else mentioned above, if I diff the source schema against a newly generated schema there is a lot of noise because the names don't match.
Creating or renaming the default constraints with migrations feels kludgy.
Very much in favor for this feature, comparing sachems generate too much nose because of auto generated constraint names
we can always address the constraint (e.g. to drop it) without the name through the column
@divega We have default constraints generated by migrations which now have an auto generated name. How do we drop these constraints through the column name only in a migration?
I would like it even better if there were also an e.g.
[Default(Value = {myDefaultValue}, Constraint="DF_MyconstraintName")]
attribute that could be specified on the property in the entity class.
Or perhaps some additional options could meet that need using the [Column(...)] attribute.
Another +1. Having different pseudo-randomly named constraints on every different developer's machine, and in each deployment environment is horrific.
+1
The need for this? Well when I create table I all ways give name to the constraints, I know that SQL can generate them, but I prefer to give them the name,
For example we can give a name to a constraint using efcore, don't know why on the default value constraint (never the less, it is a constraint) we can't
Guys, messages like +1 make github notifications pretty useless.
Any one has found a workarround for this?! I know it is possible to Edit the generated file, and add a query in there to give a proper name to the default constraint, but that would defeat the purpose of the auto generated file
Been trying to find a way to fix but but with no luck so far
After spending some time on this, there is a work arround for this.
We just need to define the default value with the method the EF provide us, replace the IMigrationsSqlGenerator on the DbContextOptionsBuilder then we need to override the:
protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder, bool terminate = true)
Before he create the table, just go through the columns, get the default and save it, then pass the default value to null After just add to the builder the "sql script"
ALTER TABLE TABLE ADD CONSTRAINT DEFAULT_VALUE_OF_TABLE_OF_COLUMN DEFAULT VALUE FOR COLUMN
This way it is possible to give a constraint name to the default value that we want, giving us total control over this
If needed I can provide the code for this
Hi thamathar. Yes can you please provide the code for this. It will be very useful to have deterministic names for default value constraints.
This is the logic behind of what I did explain, just created this now from home to help out.
public class MSqlDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer("your connection string")
.ReplaceService<IMigrationsSqlGenerator, MyMSqlMigrationSqlGenerator>();
}
private class MyMSqlMigrationSqlGenerator : SqlServerMigrationsSqlGenerator
{
public MyMSqlMigrationSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider migrationsAnnotations) : base(dependencies, migrationsAnnotations)
{
}
protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder, bool terminate = true)
{
Dictionary<string, string> defaultValues = new Dictionary<string, string>();
foreach (var column in operation.Columns)
{
// If you use the DefaultValueSql
if (!string.IsNullOrWhiteSpace(column.DefaultValueSql))
{
// We pass now the information that we need
defaultValues.Add(column.Name, column.DefaultValueSql);
// Now we pass it to NULL to discard the normal behavior
column.DefaultValueSql = null;
}
}
base.Generate(operation, model, builder, terminate);
// Now we go through all values inside the defaultValues so we can do what we need
foreach (var defaultValue in defaultValues)
{
builder
.AppendLine($"ALTER TABLE {operation.Name} ")
.AppendLine($"ADD CONSTRAINT NAME_FOR_CONSTRAINT_{operation.Name}_{defaultValue.Key} ")
.AppendLine($"DEFALT {defaultValue.Value} FOR {defaultValue.Key};")
.EndCommand();
}
}
}
}
This will override the normal behavior and remenber to do this for AlterColumn and AddColumn also, the logic its the same
Edit. It seams the tag for code is a little crazy, sry about that
Another workaround, allowing named constrains also for not null bit/int/etc columns without DefaultValueSql specified:
public class MSqlDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer("your connection string")
.ReplaceService<IMigrationsSqlGenerator, MyMSqlMigrationSqlGenerator>();
}
private class MyMSqlMigrationSqlGenerator : SqlServerMigrationsSqlGenerator
{
private string _currentColumnName;
private string _currentTableName;
public MyMSqlMigrationSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider migrationsAnnotations) : base(dependencies, migrationsAnnotations)
{
}
protected override void ColumnDefinition(
string schema,
string table,
string name,
ColumnOperation operation,
IModel model,
MigrationCommandListBuilder builder)
{
_currentTableName = table;
_currentColumnName = name;
base.ColumnDefinition(schema, table, name, operation, model, builder);
}
protected override void DefaultValue(
object defaultValue,
string defaultValueSql,
string columnType,
MigrationCommandListBuilder builder)
{
if (!string.IsNullOrEmpty(_currentTableName)
&& !string.IsNullOrEmpty(_currentColumnName)
&& !(defaultValueSql == null && defaultValue == null))
{
builder.Append($" CONSTRAINT DF__{_currentTableName}__{_currentColumnName}");
}
base.DefaultValue(defaultValue, defaultValueSql, columnType, builder);
}
}
}
This feature seems to have been pending for 4 years now with no resolution. Seems like a fair amount of interest. I know this bugs the heck out of me and was a huge issue on my last contract. Losing functionality is not progress in my book. I see someone even wrote most of the code for you guys so why the lack of responsiveness? Do you need some help?
@FastTracOnline I decided to no longer manage schemas with EF but with https://github.com/rr-wfm/MSBuild.Sdk.SqlProj This allows perfect control over schema.
@FastTracOnline we have many competing priorities, and although this issue has received some upvotes, it isn't even in the 1st page of most-requested features. In addition, there's a workaround to this, i.e. editing the scaffolded migrations to add the constraint name manually; that certainly isn't a perfect solution, but in many other cases there's no workaround at all.
I see someone even wrote most of the code for you guys so why the lack of responsiveness?
The code above may be enough for some cases, but to provide a full, comprehensive fix would probably require more designing and work. But you should definitely use the code above if it solves your problem.
