How to add description to columns in Entity Framework 4.3 code first using migrations?

Question

I'm using Entity Framework 4.3.1 code first with explicit migrations. How do I add descriptions for columns either in the entity configuration classes or the migrations, so that it ends up as the description of a column in SQL server (e.g. 2008 R2)?

I know I can probably write an extension method for the DbMigration class that would register the sp_updateextendedproperty or sp_addextendedproperty procedure call as a sql migration operation inside the migration transaction and call that extension after table creation in the migration Up method. But is there an elegant built in way that I've yet to discover? Would be nice to have an attribute that the migrations' change detection logic can pick up on and generate appropritate method calls in the scaffolded migration.

Answer 1

I needed this too. So I spent a day and here it is:

The Code

    public class DbDescriptionUpdater<TContext>         where TContext : System.Data.Entity.DbContext     {         public DbDescriptionUpdater(TContext context)         {             this.context = context;         }          Type contextType;         TContext context;         DbTransaction transaction;         public void UpdateDatabaseDescriptions()         {             contextType = typeof(TContext);             this.context = context;             var props = contextType.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);             transaction = null;             try             {                 context.Database.Connection.Open();                 transaction = context.Database.Connection.BeginTransaction();                 foreach (var prop in props)                 {                     if (prop.PropertyType.InheritsOrImplements((typeof(DbSet<>))))                     {                         var tableType = prop.PropertyType.GetGenericArguments()[0];                         SetTableDescriptions(tableType);                     }                 }                 transaction.Commit();             }             catch             {                 if (transaction != null)                     transaction.Rollback();                 throw;             }             finally             {                 if (context.Database.Connection.State == System.Data.ConnectionState.Open)                     context.Database.Connection.Close();             }         }          private void SetTableDescriptions(Type tableType)         {             string fullTableName = context.GetTableName(tableType);             Regex regex = new Regex(@"(\[\w+\]\.)?\[(?<table>.*)\]");             Match match = regex.Match(fullTableName);             string tableName;             if (match.Success)                 tableName = match.Groups["table"].Value;             else                 tableName = fullTableName;              var tableAttrs = tableType.GetCustomAttributes(typeof(TableAttribute), false);             if (tableAttrs.Length > 0)                 tableName = ((TableAttribute)tableAttrs[0]).Name;             foreach (var prop in tableType.GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance))             {                 if (prop.PropertyType.IsClass && prop.PropertyType != typeof(string))                     continue;                 var attrs = prop.GetCustomAttributes(typeof(DisplayAttribute), false);                 if (attrs.Length > 0)                     SetColumnDescription(tableName, prop.Name, ((DisplayAttribute)attrs[0]).Name);             }         }          private void SetColumnDescription(string tableName, string columnName, string description)         {             string strGetDesc = "select [value] from fn_listextendedproperty('MS_Description','schema','dbo','table',N'" + tableName + "','column',null) where objname = N'" + columnName + "';";             var prevDesc = RunSqlScalar(strGetDesc);             if (prevDesc == null)             {                 RunSql(@"EXEC sp_addextendedproperty  @name = N'MS_Description', @value = @desc, @level0type = N'Schema', @level0name = 'dbo', @level1type = N'Table',  @level1name = @table, @level2type = N'Column', @level2name = @column;",                                                        new SqlParameter("@table", tableName),                                                        new SqlParameter("@column", columnName),                                                        new SqlParameter("@desc", description));             }             else             {                 RunSql(@"EXEC sp_updateextendedproperty  @name = N'MS_Description', @value = @desc, @level0type = N'Schema', @level0name = 'dbo', @level1type = N'Table',  @level1name = @table, @level2type = N'Column', @level2name = @column;",                                                        new SqlParameter("@table", tableName),                                                        new SqlParameter("@column", columnName),                                                        new SqlParameter("@desc", description));             }         }          DbCommand CreateCommand(string cmdText, params SqlParameter[] parameters)         {             var cmd = context.Database.Connection.CreateCommand();             cmd.CommandText = cmdText;             cmd.Transaction = transaction;             foreach (var p in parameters)                 cmd.Parameters.Add(p);             return cmd;         }         void RunSql(string cmdText, params SqlParameter[] parameters)         {             var cmd = CreateCommand(cmdText, parameters);             cmd.ExecuteNonQuery();         }         object RunSqlScalar(string cmdText, params SqlParameter[] parameters)         {             var cmd = CreateCommand(cmdText, parameters);             return cmd.ExecuteScalar();         }      }     public static class ReflectionUtil     {          public static bool InheritsOrImplements(this Type child, Type parent)         {             parent = ResolveGenericTypeDefinition(parent);              var currentChild = child.IsGenericType                                    ? child.GetGenericTypeDefinition()                                    : child;              while (currentChild != typeof(object))             {                 if (parent == currentChild || HasAnyInterfaces(parent, currentChild))                     return true;                  currentChild = currentChild.BaseType != null                                && currentChild.BaseType.IsGenericType                                    ? currentChild.BaseType.GetGenericTypeDefinition()                                    : currentChild.BaseType;                  if (currentChild == null)                     return false;             }             return false;         }          private static bool HasAnyInterfaces(Type parent, Type child)         {             return child.GetInterfaces()                 .Any(childInterface =>                 {                     var currentInterface = childInterface.IsGenericType                         ? childInterface.GetGenericTypeDefinition()                         : childInterface;                      return currentInterface == parent;                 });         }          private static Type ResolveGenericTypeDefinition(Type parent)         {             var shouldUseGenericType = true;             if (parent.IsGenericType && parent.GetGenericTypeDefinition() != parent)                 shouldUseGenericType = false;              if (parent.IsGenericType && shouldUseGenericType)                 parent = parent.GetGenericTypeDefinition();             return parent;         }     }      public static class ContextExtensions     {         public static string GetTableName(this DbContext context, Type tableType)         {             MethodInfo method = typeof(ContextExtensions).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;         }     } 

How To Use

In your Migrations/Configuration.cs file, add this at the end of the Seed method:

DbDescriptionUpdater<ContextClass> updater = new DbDescriptionUpdater<ContextClass>(context); updater.UpdateDatabaseDescriptions(); 

Then in Package Manager Console type update-database and hit Enter. That's it.

The code uses [Display(Name="Description here")] attribute on your entity class properties to set the description.

Please report any bug or suggest improvements.

Thanks to

I've used these code from other people and I want to say thanks:

adding a column description

Check if a class is derived from a generic class

Get Database Table Name from Entity Framework MetaData

Generics in C#, using type of a variable as parameter

Answer 2

Note quite satisfied with the current answer (but props for the work!), I wanted a way to pull the existing comment markup in my classes instead of using attributes. And in my opinion, I don't know why the hell Microsoft didn't support this as it seems obvious it should be there!

First, turn on XML Documentation file: Project Properties->Build->XML documentation file->App_Data\YourProjectName.XML

Second, include the file as an embedded resource. Build your project, go to App_Data, show hidden files and include the XML file that was generated. Select embedded resource and Copy if newer (this is optional, you could specify the path explicitly but in my opinion this is cleaner). Note, you must use this method as the markup is not present in the assembly, and will save you from locating where your XML is stored.

Here is the code implementation which is a modified version of the accepted answer:

public class SchemaDescriptionUpdater<TContext> where TContext : DbContext {     Type contextType;     TContext context;     DbTransaction transaction;     XmlAnnotationReader reader;     public SchemaDescriptionUpdater(TContext context)     {         this.context = context;         reader = new XmlAnnotationReader();     }     public SchemaDescriptionUpdater(TContext context, string xmlDocumentationPath)     {         this.context = context;         reader = new XmlAnnotationReader(xmlDocumentationPath);     }      public void UpdateDatabaseDescriptions()     {         contextType = typeof(TContext);         var props = contextType.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);         transaction = null;         try         {             context.Database.Connection.Open();             transaction = context.Database.Connection.BeginTransaction();             foreach (var prop in props)             {                 if (prop.PropertyType.InheritsOrImplements((typeof(DbSet<>))))                 {                     var tableType = prop.PropertyType.GetGenericArguments()[0];                     SetTableDescriptions(tableType);                 }             }             transaction.Commit();         }         catch         {             if (transaction != null)                 transaction.Rollback();             throw;         }         finally         {             if (context.Database.Connection.State == System.Data.ConnectionState.Open)                 context.Database.Connection.Close();         }     }      private void SetTableDescriptions(Type tableType)     {         string fullTableName = context.GetTableName(tableType);         Regex regex = new Regex(@"(\[\w+\]\.)?\[(?<table>.*)\]");         Match match = regex.Match(fullTableName);         string tableName;         if (match.Success)             tableName = match.Groups["table"].Value;         else             tableName = fullTableName;          var tableAttrs = tableType.GetCustomAttributes(typeof(TableAttribute), false);         if (tableAttrs.Length > 0)             tableName = ((TableAttribute)tableAttrs[0]).Name;          // set the description for the table         string tableComment = reader.GetCommentsForResource(tableType, null, XmlResourceType.Type);         if (!string.IsNullOrEmpty(tableComment))             SetDescriptionForObject(tableName, null, tableComment);          // get all of the documentation for each property/column         ObjectDocumentation[] columnComments = reader.GetCommentsForResource(tableType);         foreach (var column in columnComments)         {             SetDescriptionForObject(tableName, column.PropertyName, column.Documentation);         }     }      private void SetDescriptionForObject(string tableName, string columnName, string description)     {         string strGetDesc = "";         // determine if there is already an extended description         if(string.IsNullOrEmpty(columnName))             strGetDesc = "select [value] from fn_listextendedproperty('MS_Description','schema','dbo','table',N'" + tableName + "',null,null);";         else             strGetDesc = "select [value] from fn_listextendedproperty('MS_Description','schema','dbo','table',N'" + tableName + "','column',null) where objname = N'" + columnName + "';";         var prevDesc = (string)RunSqlScalar(strGetDesc);          var parameters = new List<SqlParameter>         {             new SqlParameter("@table", tableName),             new SqlParameter("@desc", description)         };          // is it an update, or new?         string funcName = "sp_addextendedproperty";         if (!string.IsNullOrEmpty(prevDesc))             funcName = "sp_updateextendedproperty";          string query = @"EXEC " + funcName + @" @name = N'MS_Description', @value = @desc,@level0type = N'Schema', @level0name = 'dbo',@level1type = N'Table',  @level1name = @table";          // if a column is specified, add a column description         if (!string.IsNullOrEmpty(columnName))         {             parameters.Add(new SqlParameter("@column", columnName));             query += ", @level2type = N'Column', @level2name = @column";         }         RunSql(query, parameters.ToArray());     }      DbCommand CreateCommand(string cmdText, params SqlParameter[] parameters)     {         var cmd = context.Database.Connection.CreateCommand();         cmd.CommandText = cmdText;         cmd.Transaction = transaction;         foreach (var p in parameters)             cmd.Parameters.Add(p);         return cmd;     }     void RunSql(string cmdText, params SqlParameter[] parameters)     {         var cmd = CreateCommand(cmdText, parameters);         cmd.ExecuteNonQuery();     }     object RunSqlScalar(string cmdText, params SqlParameter[] parameters)     {         var cmd = CreateCommand(cmdText, parameters);         return cmd.ExecuteScalar();     }  }  public static class ReflectionUtil {     public static bool InheritsOrImplements(this Type child, Type parent)     {         parent = ResolveGenericTypeDefinition(parent);          var currentChild = child.IsGenericType                                ? child.GetGenericTypeDefinition()                                : child;          while (currentChild != typeof(object))         {             if (parent == currentChild || HasAnyInterfaces(parent, currentChild))                 return true;              currentChild = currentChild.BaseType != null                            && currentChild.BaseType.IsGenericType                                ? currentChild.BaseType.GetGenericTypeDefinition()                                : currentChild.BaseType;              if (currentChild == null)                 return false;         }         return false;     }      private static bool HasAnyInterfaces(Type parent, Type child)     {         return child.GetInterfaces()             .Any(childInterface =>             {                 var currentInterface = childInterface.IsGenericType                     ? childInterface.GetGenericTypeDefinition()                     : childInterface;                  return currentInterface == parent;             });     }      private static Type ResolveGenericTypeDefinition(Type parent)     {         var shouldUseGenericType = true;         if (parent.IsGenericType && parent.GetGenericTypeDefinition() != parent)             shouldUseGenericType = false;          if (parent.IsGenericType && shouldUseGenericType)             parent = parent.GetGenericTypeDefinition();         return parent;     } }  public static class ContextExtensions {     public static string GetTableName(this DbContext context, Type tableType)     {         MethodInfo method = typeof(ContextExtensions).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;     } } 

And the class that gets the comment markup from the visual studio generated XML documentation file:

public class XmlAnnotationReader {     public string XmlPath { get; protected internal set; }     public XmlDocument Document { get; protected internal set; }      public XmlAnnotationReader()     {         var assembly = Assembly.GetExecutingAssembly();         string resourceName = String.Format("{0}.App_Data.{0}.XML", assembly.GetName().Name);         this.XmlPath = resourceName;         using (Stream stream = assembly.GetManifestResourceStream(resourceName))         {             using (StreamReader reader = new StreamReader(stream))             {                 XmlDocument doc = new XmlDocument();                 //string result = reader.ReadToEnd();                 doc.Load(reader);                 this.Document = doc;             }         }     }      public XmlAnnotationReader(string xmlPath)     {         this.XmlPath = xmlPath;         if (File.Exists(xmlPath))         {             XmlDocument doc = new XmlDocument();             doc.Load(this.XmlPath);             this.Document = doc;         }         else             throw new FileNotFoundException(String.Format("Could not find the XmlDocument at the specified path: {0}\r\nCurrent Path: {1}", xmlPath, Assembly.GetExecutingAssembly().Location));     }      /// <summary>     /// Retrievethe XML comments documentation for a given resource     /// Eg. ITN.Data.Models.Entity.TestObject.MethodName     /// </summary>     /// <returns></returns>     public string GetCommentsForResource(string resourcePath, XmlResourceType type)     {          XmlNode node = Document.SelectSingleNode(String.Format("//member[starts-with(@name, '{0}:{1}')]/summary", GetObjectTypeChar(type), resourcePath));         if (node != null)         {             string xmlResult = node.InnerText;             string trimmedResult = Regex.Replace(xmlResult, @"\s+", " ");             return trimmedResult;         }         return string.Empty;     }      /// <summary>     /// Retrievethe XML comments documentation for a given resource     /// Eg. ITN.Data.Models.Entity.TestObject.MethodName     /// </summary>     /// <returns></returns>     public ObjectDocumentation[] GetCommentsForResource(Type objectType)     {         List<ObjectDocumentation> comments = new List<ObjectDocumentation>();         string resourcePath = objectType.FullName;          PropertyInfo[] properties = objectType.GetProperties();         FieldInfo[] fields = objectType.GetFields();         List<ObjectDocumentation> objectNames = new List<ObjectDocumentation>();         objectNames.AddRange(properties.Select(x => new ObjectDocumentation() { PropertyName = x.Name, Type = XmlResourceType.Property }).ToList());         objectNames.AddRange(properties.Select(x => new ObjectDocumentation() { PropertyName = x.Name, Type = XmlResourceType.Field }).ToList());          foreach (var property in objectNames)         {             XmlNode node = Document.SelectSingleNode(String.Format("//member[starts-with(@name, '{0}:{1}.{2}')]/summary", GetObjectTypeChar(property.Type), resourcePath, property.PropertyName ));             if (node != null)             {                 string xmlResult = node.InnerText;                 string trimmedResult = Regex.Replace(xmlResult, @"\s+", " ");                 property.Documentation = trimmedResult;                 comments.Add(property);             }         }         return comments.ToArray();     }      /// <summary>     /// Retrievethe XML comments documentation for a given resource     /// </summary>     /// <param name="objectType">The type of class to retrieve documenation on</param>     /// <param name="propertyName">The name of the property in the specified class</param>     /// <param name="resourceType"></param>     /// <returns></returns>     public string GetCommentsForResource(Type objectType, string propertyName, XmlResourceType resourceType)     {         List<ObjectDocumentation> comments = new List<ObjectDocumentation>();         string resourcePath = objectType.FullName;          string scopedElement = resourcePath;         if (propertyName != null && resourceType != XmlResourceType.Type)             scopedElement += "." + propertyName;         XmlNode node = Document.SelectSingleNode(String.Format("//member[starts-with(@name, '{0}:{1}')]/summary", GetObjectTypeChar(resourceType), scopedElement));         if (node != null)         {             string xmlResult = node.InnerText;             string trimmedResult = Regex.Replace(xmlResult, @"\s+", " ");             return trimmedResult;         }         return string.Empty;     }      private string GetObjectTypeChar(XmlResourceType type)     {         switch (type)         {             case XmlResourceType.Field:                 return "F";             case XmlResourceType.Method:                 return "M";             case XmlResourceType.Property:                 return "P";             case XmlResourceType.Type:                 return "T";          }         return string.Empty;     } }  public class ObjectDocumentation {     public string PropertyName { get; set; }     public string Documentation { get; set; }     public XmlResourceType Type { get; set; } }  public enum XmlResourceType {     Method,     Property,     Field,     Type }