EF6 Code First Use Data Annotations to populate description on SQL Table and Colums

Question

My work has a standard that requires each table and column (and other database objects) to have a description on it. I would do this via property windows in SQL Management Studio or TSQL Script.

Is it possible to use data-annotation e.g. Description to generate a description on the migrations.

    [Description("List of Valid System Parameter Groups")]
    public class SystemParameterGroup
    {
        public SystemParameterGroup()
        {
            this.SystemParameters = new ObservableCollection<SystemParameter>();
        }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]   
        [Description("PK: Unique Record Identifer")]
        public int SystemParameterGroupId { get; set; }

        [Required(ErrorMessage="Group Name is Required")]
        [MinLength(5, ErrorMessage="Must be at least 5 Characters")]
        [MaxLength(50, ErrorMessage="Must be Less than 50 Characters")]
        [Description("Name of System Parameter Group")]
        public string SystemParameterGroupName { get; set; }

        #region Child Records

        public virtual ObservableCollection<SystemParameter> SystemParameters { set; get; }
        #endregion
    }

How do I Get the Description to be used using code first?

possible duplicate of [How to add description to columns in Entity Framework 4.3 code first using migrations?](http://stackoverflow.com/questions/10080601/how-to-add-description-to-columns-in-entity-framework-4-3-code-first-using-migra) — DavidG, May 28 '14 at 07:31

score 1 · Accepted Answer · answered Jun 03 '14 at 09:03

I would recommend to create a custom migrations operation see this post about this: http://dolinkamark.wordpress.com/2014/05/03/creating-a-custom-migration-operation-in-entity-framework/

This way you can encapsulate adding and removing description of tables however this is only enough for adding these operations manually, code first won't find it in your model automatically.

To achieve adding this automatically you have to use the MigrationScaffolder class. Specifically you have to create a new class that inherits from the MigrationScaffolder class and overwrites its Scaffold function. Currently I'm working on a post about how to do this exaclty if I'm finished I will extend this post with a link to that.

score 1 · Answer 2 · answered Jul 17 '15 at 08:18

I took some advice from Mark and created my own Comment Code

I created these two functions CreateTableDescription and CreateColumnDescription Code :

    /// <summary>
    /// Add Descriptions to New Tables and/or Columns
    /// </summary>
    /// <param name="tableName">Name of Table</param>
    /// <param name="tableDescription">Description of Table</param>
    /// <returns></returns>
    public static string CreateTableDescription(string tableName, string tableDescription )
    {

            return string.Format("IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('{0}') AND [name] = N'MS_Description' AND [minor_id] = 0) EXECUTE sp_addextendedproperty N'MS_Description', '{1}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', NULL, NULL ELSE EXECUTE sp_updateextendedproperty N'MS_Description', '{1}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', NULL, NULL", tableName, tableDescription);

    }
    /// <summary>
    /// Updates Descriptions on Tables and Columns
    /// </summary>
    /// <param name="tableName">Name of Table for Column</param>
    /// <param name="columnName">Name of Column</param>
    /// <param name="columDescription">Description for Column</param>
    /// <returns></returns>
    public static string CreateColumnDescription(string tableName, string columnName , string columDescription )
    {
        return string.Format("IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('{0}') AND [name] = N'MS_Description' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '{1}' AND [object_id] = OBJECT_ID('{0}'))) EXECUTE sp_addextendedproperty N'MS_Description', '{2}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', N'COLUMN', N'{1}' ELSE EXECUTE sp_updateextendedproperty N'MS_Description', '{2}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', N'COLUMN', N'{1}'", tableName, columnName, columDescription);
    }

Then in my Up() of my migrations I run the following as example

RunSqlTransaction(CommonCode.CreateTableDescription("__MigrationHistory", "Database Version History Table - Administration User Only"));

    RunSqlTransaction(CommonCode.CreateColumnDescription("__MigrationHistory", "ContextKey", "Defining the Migration Context"));
            RunSqlTransaction(CommonCode.CreateColumnDescription("__MigrationHistory", "MigrationId", "Unique Migration Identifier"));
            RunSqlTransaction(CommonCode.CreateColumnDescription("__MigrationHistory", "Model", "Binary of the SQL Change Model"));
            RunSqlTransaction(CommonCode.CreateColumnDescription("__MigrationHistory", "ProductVersion", "Version of The Database Change"));

the code for the RunSqlTransaction is

private void RunSqlTransaction(string sqlStatement)
        {
            //split the script on "GO" commands
            string[] splitter = new string[] { "\r\nGO\r\n" };
            string[] commandTexts = sqlStatement.Split(splitter,
              StringSplitOptions.RemoveEmptyEntries);
            foreach (string commandText in commandTexts)
            {
            Sql(commandText.Replace("GO", ""));
            }
        }

EF6 Code First Use Data Annotations to populate description on SQL Table and Colums

2 Answers2