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.
Following is the context class. Step 1 − Before running the application you need to enable migration. Step 2 − Open Package Manager Console from Tools → NuGet Package Manger → Package Manger Console. Step 3 − Migration is already enabled, now add migration in your application by executing the following command.
Run Enable-Migrations command in a Package Manager console. This command added two more classes to your project in the Migrations folder. This migration was generated because Code First already created a database for us before we enabled migrations. It allows you to configure how Migrations behave for your context.
For more information, type: "get-help Add-Migration -detailed". For technical information, type: "get-help Add-Migration -full". After creating a migration file using the add-migration command, you have to update the database. Execute the Update-Database command to create or modify a database schema.
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
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 }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With