SQL column default value with Entity Framework

Question

I am trying to use Code-First EF6 with default SQL values.

For example, I have a "CreatedDate" column/property not null with a default in SQL of "getdate()"

How do I represent this in my code Model? Currently I have:

<DatabaseGenerated(DatabaseGeneratedOption.Computed)>
Public Property CreatedDate As DateTime

Will this work, or will I need to use a nullable even though the actual column should be not null, so EF doesn't send a value when it hasn't been set:

<DatabaseGenerated(DatabaseGeneratedOption.Computed)>
Public Property CreatedDate As DateTime?

Or is there a better solution out there?

I don't want EF to handle my defaults - I know this is available to me but not possible in my current situation.

please refere [https://stackoverflow.com/a/59551802/8403632](https://stackoverflow.com/a/59551802/8403632) — shalitha senanayaka, Jan 01 '20 at 11:05
thanks @shalithasenanayaka but the answer you referred to is computed by logic in the app, my original question (though long answered) was about allowing SQL to provide defaults. The answer you linked to is not a viable solution to this problem. Thanks anyway. — Carl, Jan 02 '20 at 13:18

Faris Zacina · Accepted Answer · 2014-11-20T17:02:49.187

Currently in EF6 there is not an attribute to define database functions used for a certain property default value. You can vote on Codeplex to get it implemented:

https://entityframework.codeplex.com/workitem/44

The accepted way to implement something like that is to use Computed properties with Migrations where you specify the default database function.

Your class could look like this in C#:

public class MyEntity
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime Created { get; set; }
}

The computed property doesn't have to be nullable.

Then you have to run a migration and modify it by hand to include the default SQL function. A migration could look like:

public partial class Initial : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.MyEntities",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    Created = c.DateTime(nullable: false, defaultValueSql: "GetDate()"),
                })
            .PrimaryKey(t => t.Id);

    }

    public override void Down()
    {
        DropTable("dbo.MyEntities");
    }
}

You will notice the defaultValueSql function. That is the key to get the computation working

Nate Anderson · Answer 2 · 2019-01-18T20:13:26.837

Accepted answer is correct for EF6, I'm only adding EF Core solution; (also my solution focuses on changing the default-value, rather than creating it properly the first time)

There is still no Data-Attribute in EF Core.

And you must still use the Fluent API; it does have a HasDefaultValue

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.Rating)
        .HasDefaultValue(3);
}

Note, there is also HasDefaultValueSql for NULL case:

        .HasDefaultValueSql("NULL");

And you can also use the Migrations Up and Down methods, you can alter the defaultValue or defaultValueSql but you may need to drop Indexes first. Here's an example:

public partial class RemovingDefaultToZeroPlantIdManualChange : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_TABLE_NAME_COLUMN_NAME",
            table: "TABLE_NAME"
        );

        migrationBuilder.AlterColumn<int>(
            name: "COLUMN_NAME",
            table: "TABLE_NAME",
            nullable: true,
            //note here, in the Up method, I'm specifying a new defaultValue:
            defaultValueSql: "NULL",
            oldClrType: typeof(int));

        migrationBuilder.CreateIndex(
            name: "IX_TABLE_NAME_COLUMN_NAME",
            table: "TABLE_NAME",
            column: "COLUMN_NAME"
        );
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_TABLE_NAME_COLUMN_NAME",
            table: "TABLE_NAME"
        );

        migrationBuilder.AlterColumn<int>(
            name: "COLUMN_NAME",
            table: "TABLE_NAME",
            nullable: true,
            //note here, in the Down method, I'll restore to the old defaultValue:
            defaultValueSql: "0",
            oldClrType: typeof(int));

        migrationBuilder.CreateIndex(
            name: "IX_TABLE_NAME_COLUMN_NAME",
            table: "TABLE_NAME",
            column: "COLUMN_NAME"
        );


    }
}

score 0 · Answer 3 · answered Feb 20 '20 at 13:21

[mysql]

For those, who don't want to use computed and rewrite it after every db update, I wrote extension method for database partial class. Sure, there are thing that has to be improved or added, but for now it is enough for our using, enjoy.

Take into account, that due to database_schema access it is not the fastest and also you need to have same entity name as table name (or rewrite it somehow).

    public static bool GetDBDefaults(object entity)
    {
        try
        {
            string table_name = entity.GetType().Name;

            string q = $"select column_name, column_default from information_schema.columns where column_default is not null and table_schema not in ('information_schema', 'sys', 'performance_schema', 'mysql') and table_name = '{table_name}' order by table_schema, table_name, ordinal_position;";

            List<DBDefaults> dbDefaults = new List<DBDefaults>();
            using (DatabaseModelFull db = new DatabaseModelFull())
            {
                dbDefaults = db.Database.SqlQuery<DBDefaults>(q).ToList();
            }

            Type myType = entity.GetType();
            IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());
            IList<FieldInfo> fields = new List<FieldInfo>(myType.GetFields());

            foreach (var dbDefault in dbDefaults)
            {
                var prop = props.SingleOrDefault(x => x.Name == dbDefault.column_name);

                if (prop != null)
                {
                    if (dbDefault.column_default.Equals("CURRENT_TIMESTAMP"))
                        prop.SetValue(entity, System.Convert.ChangeType(DateTime.Now, prop.PropertyType));
                    else
                        prop.SetValue(entity, System.Convert.ChangeType(dbDefault.column_default, prop.PropertyType));
                    continue;
                }

                var field = fields.SingleOrDefault(x => x.Name == dbDefault.column_name);

                if (field != null)
                {
                    if (dbDefault.column_default.Equals("CURRENT_TIMESTAMP"))
                        field.SetValue(entity, System.Convert.ChangeType(DateTime.Now, field.FieldType));
                    else
                        field.SetValue(entity, System.Convert.ChangeType(dbDefault.column_default, field.FieldType));
                }
            }
            return true;
        }
        catch
        {
            return false;
        }
    }


    public class DBDefaults
    {
        public string column_name { get; set; }
        public string column_default { get; set; }
    }

score 0 · Answer 4 · answered May 16 '21 at 17:19

Adding this solution after having this same problem. I want to use default values defined in SQL Server rather than having to set a default value in the C# code.

This behavior is controlled by the DatabaseGeneratedOption value. Whether EF uses the default value, a NULL value, or the specified value varies depending on which option is used. Here is a simplified example of creating a new database entry for each of the options and whether the default value is used.

// Item database declaration
public Item()
{
    public string id { get; set; }
    public string description { get; set }
}

// Add item to database code.  First one uses the default value, the second is
// overriding it using the specified value.  Anything inside brackets uses your
// database context and class definition objects.
var itemToAdd1 = new [dbClass].Item
{
    id = "CodeTest1"
};
var itemToAdd2 = new new[DBClass].Item
{
    id = "CodeTest2",
    description = "Default value override in code"
};
[dbContext].Add(itemToAdd1);
[dbContext].Add(itemToAdd2);
[dbContext].SaveChanges();


// The behavior changes based on the DatabaseGeneratedOption setting on the database
// class definition for the description property.

// DatabaseGeneratedOption:  None
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string description { get; set; }

Result:
id = "CodeTest1",                                   // First item, using default value
description = NULL                                  // Code sent null value and SQL used it instead of the default

id = "CodeTest2",                                   // Second item, overriding description in code
description = "Default value override in code"      // Code override value was used by SQL as expected


// DatabaseGeneratedOption:  Identity
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string description { get; set; }

Result:
id = "CodeTest1",                                   // First item, using default value
description = "SQL Default Value"                   // Code did not send any value and SQL used the DB default value as expected

id = "CodeTest2",                                   // Second item, overriding description in code
description = "Default value override in code"      // Code override value was used by SQL as expected


// DatabaseGeneratedOption:  Computed
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public string description { get; set; }

Result:
id = "CodeTest1",                                   // First item, using default value
description = "SQL Default Value"                   // Code did not send any value and SQL used the DB default value as expected

id = "CodeTest2",                                   // Second item, overriding description in code
description = "SQL Default Value"                   // The SQL default value was still used despite setting this property in code.

TLDR: DatabaseGeneratedOption.Identity should give you the result you are looking for.

score 0 · Answer 5 · answered Sep 25 '21 at 05:52

This is my solution. I used it in a EF Core DbContext in a .Net Core 3.1 DLL. It works with automatic migrations and is using an attribute, which is searched with reflection. The attribute contains the default sql value, which is then set in OnModelCreating().

Step 1 - Add a new Attribute

[AttributeUsage(AttributeTargets.Property)]
public class DefaultValueSqlAttribute : Attribute
{
    public string DefaultValueSql { get; private set; } = "";
    
    public DefaultValueSqlAttribute(string defaultValueSql)
    {
        DefaultValueSql = defaultValueSql;
    }
}

Step 2 - Use that Attribute in your data classes

public class Entity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("Id", Order=0)]
    [DefaultValueSql("newId()")]
    public Guid Id { get; set; }


    [Column("DateCreated", Order = 100)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [DefaultValueSql("GETUTCDATE()")]
    public DateTime DateCreated { get; set; } = DateTime.UtcNow;
}

[Table("tbl_User")]
public class User: Entity
{
    [Required]
    [Column(Order = 1)]
    public string EMail { get; set; }

    [Column(Order = 2)]
    public string Name { get; set; }

    [Column(Order = 3)]
    public string Forename { get; set; }

    [Column(Order = 4)]
    public string Street { get; set; }

    [Column(Order = 5)]
    public string Postalcode { get; set; }

    [Column(Order = 6)]
    public string MobileNumber { get; set; }
}

Step 3 - Add your classes to the DbContext

public DbSet<User> tbl_User { get; set; }

Step 4 - Add this code your DbContext (you have to change it to your needs...) It assumes, that all relevant data classes live in one certain assembly and one certain namespace. In my case it is a .NetStandard 2.0 DLL.

protected override void OnModelCreating(ModelBuilder mb)
{
    //Uncomment this line, if you want to see what is happening when you fire Add-Migration
    //Debugger.Launch();

    base.OnModelCreating(mb);

    OnModelCreatingAddDefaultSqlValues(mb);
}

private void OnModelCreatingAddDefaultSqlValues(ModelBuilder mb)
{
    var assemblyName = "Ik.Shared";
    var nameSpace = "Ik.Shared.Entities";

    var asm = Assembly.Load(assemblyName);

    //Read all types in the assembly Ik.Shared, that are in the namespace Ik.Shared.Entities
    List<Type> types = asm.GetTypes().Where(p => p.Namespace == nameSpace).ToList();
    //Read all properties in DatabaseContext, that are of type DbSet<>
    var dbSets = typeof(DatabaseContext).GetProperties().Where(p => p.PropertyType.Name.ToLower().Contains("dbset")).ToList();
    //A list of types, that are used as a generic argument in a DbSet<T>
    List<Type> dbSetTypes = new List<Type>();
    foreach (PropertyInfo pi in dbSets)
    {
        //Add the type of the generic argument from DbSet<T>
        dbSetTypes.Add(pi.PropertyType.GetGenericArguments()[0]);
    }

    //For all types in Ik.Shared
    foreach (Type t in types)
    {
        //If a type inherited from Entity AND the type itself is not Entity AND the type was used as DbSet<Type> in the DbContext
        if (typeof(Entity).IsAssignableFrom(t) && t.Name != nameof(Entity) && dbSetTypes.Contains(t))
        {
            //Get all properties of that type
            var properties = t.GetProperties().ToList();
            foreach (var p in properties)
            {
                //Check if the property has the DefaultValueSqlAttribute
                var att = p.GetCustomAttribute<DefaultValueSqlAttribute>();
                if (att != null)
                {
                    //If any property has the DefaultValueSqlAttribute, set the the value here. Done. 
                    mb.Entity(t).Property(p.Name).HasDefaultValueSql(att.DefaultValueSql);
                }
            }
        }
    }
}

Step 5 - Fire up a new migration in the packager consoles of Visual Studio

Add-Migration MI_000000 -StartupProject Ik.Ws.Login

Step 6 - See the result: Look at defaultValueSql = Done

//This class was entirely created automatic. No manual changes.
public partial class MI_000000 : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "tbl_User",
            columns: table => new
            {
                Id = table.Column<Guid>(type: "uniqueidentifier", nullable: false, defaultValueSql: "newId()"),
                EMail = table.Column<string>(type: "nvarchar(max)", nullable: false),
                Name = table.Column<string>(type: "nvarchar(max)", nullable: true),
                Forename = table.Column<string>(type: "nvarchar(max)", nullable: true),
                Street = table.Column<string>(type: "nvarchar(max)", nullable: true),
                Postalcode = table.Column<string>(type: "nvarchar(max)", nullable: true),
                MobileNumber = table.Column<string>(type: "nvarchar(max)", nullable: true),
                DateCreated = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql: "GETUTCDATE()")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_tbl_User", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "tbl_User");
    }
}

I hope it helps or inspires you.

score -3 · Answer 6 · answered Jul 17 '19 at 01:49

-3

try with this. This code insert by default the current date

//[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime Created { get; set; } = new DateTime();

answered Jul 17 '19 at 01:49

CESAR NICOLINI RIVERO

123
3
11

2

No, It doesn't. – AFetter Aug 03 '20 at 22:21

SQL column default value with Entity Framework

6 Answers6

Linked