[C#.NET][Entity Framework] Code First , Set default DateTime field to GETDATE()

2014-08-05

4318
0
Entity Framework 6
2015-12-28

Code First 沒有設定預設值這個行為，不過我們還是可以透過以下幾種方式來完成：

DbContext.Database.ExecuteSqlCommand() 方法


[TestMethod]
public void ExecuteSqlCommandTestMethod()
{
    MyDbContext context = new MyDbContext("localdb");
    var sql = string.Format("ALTER TABLE {0} ADD DEFAULT (getdate()) FOR {1}", "Accounts", "CurrentDate");
    context.Database.ExecuteSqlCommand(sql);
}

透過 Migration 直接將預設值帶入特定欄位，這僅是用在"建立資料表"或"新增欄位"時使用


CreateTable(
    "dbo.Accounts",
    c => new
        {
            Id = c.Guid(nullable: false, identity: true),
            CurrentDate = c.DateTime(nullable: false, defaultValueSql: "GETDATE()"),
            UserId = c.String(),
        })
    .PrimaryKey(t => t.Id);

若覺得手動加太遜，可以自己寫 Migration generate code，可以由這邊手動加上 Default - GETDATE()

http://stackoverflow.com/questions/8594431/possible-to-default-datetime-field-to-getdate-with-entity-framework-migrations

不過個人覺得利用 Attribute 的方式比較適合我

http://stackoverflow.com/questions/8262590/entity-framework-code-first-fluent-api-adding-indexes-to-columns

我要將上述 link 中的某一個範例改成擴充方法，下面的範例簡單來講，就是找出有哪些欄位有套用 SqlAttribute，找到之後就組合 SQL 並執行

這個範例目前只有處理 GetDate()，但並沒有判斷資料型態是不是 Datetime，若欄位已經存在default，則會跳出例外，我沒有做更仔細的防呆，請自行修改的更嚴謹一些


public enum SqlOption
{
    Active = 1,
    GetDate = 2,
    Default = 3,
}

[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)]
public class SqlAttribute : Attribute
{
    public SqlAttribute(SqlOption selectedOption = SqlOption.Active)
    {
        this.SqlOption = selectedOption;
    }

    public SqlOption SqlOption { get; set; }
}



public static class DbContextExtensions
{
    private const string GETDATE_TEMPLATE = "ALTER TABLE {tableName} ADD DEFAULT (getdate()) FOR {columnName};";
    private const string ACTIVE_TEMPLATE = "ALTER TABLE [dbo].[{tableName}] ADD DEFAULT (1) FOR [{columnName}];";

    public static string GetTableName(this DbContext context, Type tableType)
    {
        MethodInfo method = typeof(DbContextExtensions).GetMethod("GetTableName", new Type[] { typeof(DbContext) })
                         .MakeGenericMethod(new Type[] { tableType });
        return (string)method.Invoke(context, new object[] { context });
    }

    public static string GetTableName<T>(this DbContext context) where T : class
    {
        ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;

        return objectContext.GetTableName<T>();
    }

    public static string GetTableName<T>(this ObjectContext context) where T : class
    {
        string sql = context.CreateObjectSet<T>().ToTraceString();
        Regex regex = new Regex("FROM (?<table>.*) AS");
        Match match = regex.Match(sql);

        string table = match.Groups["table"].Value;
        return table;
    }

    public static void UpdateDescription<T>(this DbContext context) where T : class
    {
        if (context.Database.CreateIfNotExists())
        {
            return;
        }
        var sql = string.Empty;
        //取出DbSet<T>
        var queryEntities = from p in context.GetType().GetProperties()
                            where p.PropertyType.IsGenericType
                                  && p.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>)
                            let entityType = p.PropertyType.GetGenericArguments().First()
                            select entityType;

        var annotationAttribute = typeof(SqlAttribute);

        foreach (var entity in queryEntities)
        {
            //取出SQL中真實的資料表
            
            var tableName = context.GetTableName(entity);

            //找出有哪些 Column 使用 SqlAttribute
            var propertiesWithAnnotations = entity.GetProperties()
                 .Where(c => Attribute.IsDefined(c, annotationAttribute));

            foreach (var annotatedProperty in propertiesWithAnnotations)
            {
                var columnName = annotatedProperty.Name;
                var annotationProperties = annotatedProperty.GetCustomAttributes(annotationAttribute, true).ToList();
                //Column 用了哪些SqlAttribute
                foreach (SqlAttribute annotationProperty in annotationProperties)
                {
                    switch (annotationProperty.SqlOption)
                    {
                        case SqlOption.Active: 
                            sql += ACTIVE_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                            //sql += INDEX_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                            break;

                        case SqlOption.GetDate: 
                            //ALTER TABLE Account ADD DEFAULT (getdate()) FOR CreatedDate
                            sql += GETDATE_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                            //sql += INDEX_TEMPLATE.Replace("{tableName}", tableName).Replace("{columnName}", columnName);
                            break;
                    }
                }
            }
        }
        context.Database.ExecuteSqlCommand(sql);
    }
}

這時在 POCO 加上 Sql(SqlOption.Datetime)

調用程式碼時就可以把預設值填上


[TestMethod]
public void UpdateDescriptionTestMethod()
{
    MyDbContext context = new MyDbContext("localdb");
    context.UpdateDescription<MyDbContext>();
}

執行結果如下圖：