Question
I need to be able to set a DateTime column's initial value.
When I specify "getutcdate() or DateTime.UtcNow
entity.Property(e => e.ShipDate).DefaultValue("getutcdate()")
entity.Property(e => e.ShipDate).DefaultValue(DateTime.UtcNow)
and then run dnx . ef migration add InitialMigration EF generates a migration snapshot with:
b.Property<DateTime?>("ShipDate")
.Required()
.Annotation("Relational:ColumnDefaultValue", "getutcdate()")
.Annotation("Relational:ColumnDefaultValueType", "System.String");
when I use DateTime.UtcNow...
b.Property<DateTime?>("ShipDate")
.Annotation("Relational:ColumnDefaultValue", "635754129448768827")
.Annotation("Relational:ColumnDefaultValueType", "System.DateTime");
and an initial migration:
ShipDate = table.Column(
type: "datetime2",
nullable: false,
defaultValue: "getutcdate()"),
when I use DateTime.UtcNow...
ShipDate = table.Column(
type: "datetime2",
nullable: true,
defaultValue: new DateTime(2015, 8, 17, 12, 55, 44, 876, DateTimeKind.Unspecified)),
It would seem like it has to work, but when I insert data into the table, the default value for the column is the 'instant' in which the timestamp was created.
Am I missing something?
ALSO, since in the same vein, how to specify the IDENTITY SEED in EF7?
Thanks
UPDATE After generating the sql script , with both options I get:
If using "getutcdate()":
[ShipDate] datetime2 DEFAULT 'getutcdate()',
which does not work because of the quotes
or if using DateTime.utcNow:
[ShipDate] datetime2 DEFAULT '2015-08-17 12:55:44.8760000'
which explains the static value I was getting.
I guess I can manage with this. Is this is a bug or is there a correct way of doing this? Thanks
Accepted Answer
You want to set the default value SQL, not a constant value:
entity.Property(e => e.ShipDate).HasDefaultValueSql("getutcdate()");
Popular Answer
A more flexible solution using attributes for EF Core:
in your DbContext:
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
// Add your customizations after calling base.OnModelCreating(builder);
CustomDataTypeAttributeConvention.Apply(builder);
DecimalPrecisionAttributeConvention.Apply(builder);
SqlDefaultValueAttributeConvention.Apply(builder);
}
and create these classes:
public static class SqlDefaultValueAttributeConvention
{
public static void Apply(ModelBuilder builder)
{
ConventionBehaviors
.SetSqlValueForPropertiesWithAttribute<SqlDefaultValueAttribute>(builder, x => x.DefaultValue);
}
}
public static class DecimalPrecisionAttributeConvention
{
public static void Apply(ModelBuilder builder)
{
ConventionBehaviors
.SetTypeForPropertiesWithAttribute<DecimalPrecisionAttribute>(builder,
x => $"decimal({x.Precision}, {x.Scale})");
}
}
public class CustomDataTypeAttributeConvention
{
public static void Apply(ModelBuilder builder)
{
ConventionBehaviors
.SetTypeForPropertiesWithAttribute<DataTypeAttribute>(builder,
x => x.CustomDataType);
}
}
public static class ConventionBehaviors
{
public static void SetTypeForPropertiesWithAttribute<TAttribute>(ModelBuilder builder, Func<TAttribute, string> lambda) where TAttribute : class
{
SetPropertyValue<TAttribute>(builder).ForEach((x) => {
x.Item1.Relational().ColumnType = lambda(x.Item2);
});
}
public static void SetSqlValueForPropertiesWithAttribute<TAttribute>(ModelBuilder builder, Func<TAttribute, string> lambda) where TAttribute : class
{
SetPropertyValue<TAttribute>(builder).ForEach((x) =>
{
x.Item1.Relational().DefaultValueSql = lambda(x.Item2);
});
}
private static List<Tuple<IMutableProperty, TAttribute>> SetPropertyValue<TAttribute>(ModelBuilder builder) where TAttribute : class
{
var propsToModify = new List<Tuple<IMutableProperty, TAttribute>>();
foreach (var entity in builder.Model.GetEntityTypes())
{
var properties = entity.GetProperties();
foreach (var property in properties)
{
var attribute = property.PropertyInfo
.GetCustomAttributes(typeof(TAttribute), false)
.FirstOrDefault() as TAttribute;
if (attribute != null)
{
propsToModify.Add(new Tuple<IMutableProperty, TAttribute>(property, attribute));
}
}
}
return propsToModify;
}
}
and custom attributes:
/// <summary>
/// Set a default value defined on the sql server
/// </summary>
[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
public class SqlDefaultValueAttribute : Attribute
{
/// <summary>
/// Default value to apply
/// </summary>
public string DefaultValue { get; set; }
/// <summary>
/// Set a default value defined on the sql server
/// </summary>
/// <param name="value">Default value to apply</param>
public SqlDefaultValueAttribute(string value)
{
DefaultValue = value;
}
}
/// <summary>
/// Set the decimal precision of a decimal sql data type
/// </summary>
[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
public class DecimalPrecisionAttribute : Attribute
{
/// <summary>
/// Specify the precision - the number of digits both left and right of the decimal
/// </summary>
public int Precision { get; set; }
/// <summary>
/// Specify the scale - the number of digits to the right of the decimal
/// </summary>
public int Scale { get; set; }
/// <summary>
/// Set the decimal precision of a decimal sql data type
/// </summary>
/// <param name="precision">Specify the precision - the number of digits both left and right of the decimal</param>
/// <param name="scale">Specify the scale - the number of digits to the right of the decimal</param>
public DecimalPrecisionAttribute(int precision, int scale)
{
Precision = precision;
Scale = scale;
}
public DecimalPrecisionAttribute(int[] values)
{
Precision = values[0];
Scale = values[1];
}
}
Then you will be able to decorate your table properties with any of these attributes (or make your own custom ones):
[DecimalPrecision(18, 9)]
[SqlDefaultValue("getutcdate()")]
[DataType("decimal(18,9)")]