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

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.

DbMigration

sp_updateextendedproperty

sp_addextendedproperty

Up

Don't u need to add a DataAnnotations Attribute?
– Luiz Carlos Brazão
Mar 19 '13 at 18:34

5 Answers
5

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:

Migrations/Configuration.cs

Seed

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


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

update-database

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

[Display(Name="Description here")]

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

Create little class. Couple of recommendations. 1) Wrap with SetColumnDescription() with check for virtual properties. Only add props that are not virtual. 2) Create a custom attribute instead of using Display. public class DbTableMetaAttribute : Attribute { private string _description; public virtual string Description { get { return _description; } set { _description = value; } } }
– gnome
Sep 11 '14 at 16:10

You can find a cleaner solution for GetTableName at romiller.com/2014/04/08/ef6-1-mapping-between-types-tables
– Michał Drozdowicz
Aug 25 '15 at 14:27

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_DataYourProjectName.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}rnCurrent 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
}


can you not use the ExceuteSqlCommand method. Here, you can explicitly define whatever meta property you want to add in you Table.

ExceuteSqlCommand

http://msdn.microsoft.com/en-us/library/system.data.entity.database.executesqlcommand(v=vs.103).aspx

thank you Mr.Mahmoodvcs for the great solution.
allow me to modify it just replace "DisplayAttribute" with "DescriptionAttribute"
insted of using :

[Display(Name="Description here")]


you will use :

[Description("Description here")]


so it includes the table as well.

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;
var table_attrs = tableType.GetCustomAttributes(typeof(DescriptionAttribute), false);
if (table_attrs != null && table_attrs.Length > 0)
SetTableDescription(tableName, ((DescriptionAttribute)table_attrs[0]).Description);
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(DescriptionAttribute), false);
if (attrs != null && attrs.Length > 0)
SetColumnDescription(tableName, prop.Name, ((DescriptionAttribute)attrs[0]).Description);
}
}

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));
}
}
private void SetTableDescription(string tableName, string description)
{

string strGetDesc = "select [value] from fn_listextendedproperty('MS_Description','schema','dbo','table',N'" + tableName + "',null,null);";
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;",
new SqlParameter("@table", tableName),
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;",
new SqlParameter("@table", tableName),
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;
}
}


While the question is about EF4, this answer targets EF6, which should be appropriate given the passed time since the question was asked.

I think the Comments belong in the Migration Up and Down methods rather than some Seed method.

Up

Down

Seed

So, as suggested by @MichaelBrown, start with enabling XML Documentation output and include the documentation file as Embedded Resource in your project.

Then, lets turn the comments into a table/column annotation by using a Convention. There are some tweaks to be made for things like multiline comments and getting rid of excessive whitespace.

Convention

public class CommentConvention : Convention
{
public const string NewLinePlaceholder = "<<NEWLINE>>";

public CommentConvention()
{
var docuXml = new XmlDocument();

// Read the documentation xml
using (var commentStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("Namespace.Documentation.xml"))
{
docuXml.Load(commentStream);
}

// configure class/table comment
Types()
.Having(pi => docuXml.SelectSingleNode($"//member[starts-with(@name, 'T:{pi?.FullName}')]/summary"))
.Configure((c, a) =>
{
c.HasTableAnnotation("Comment", GetCommentTextWithNewlineReplacement(a));
});

// configure property/column comments
Properties()
.Having(pi =>
docuXml.SelectSingleNode(
$"//member[starts-with(@name, 'P:{pi?.DeclaringType?.FullName}.{pi?.Name}')]/summary"))
.Configure((c, a) => { c.HasColumnAnnotation("Comment", GetCommentTextWithNewlineReplacement(a)); });
}

// adjust the documentation text to handle newline and whitespace
private static string GetCommentTextWithNewlineReplacement(XmlNode a)
{
if (string.IsNullOrWhiteSpace(a.InnerText))
{
return null;
}
return string.Join(
NewLinePlaceholder,
a.InnerText.Trim()
.Split(new string {"rn", "r", "n"}, StringSplitOptions.None)
.Select(line => line.Trim()));
}
}


Register the convention in the OnModelCreating method.

OnModelCreating

Expected Result:
When a new Migration is created, the comments will be included as annotations like

CreateTable(
"schema.Table",
c => new
{
Id = c.Decimal(nullable: false, precision: 10, scale: 0, identity: true,
annotations: new Dictionary<string, AnnotationValues>
{
{
"Comment",
new AnnotationValues(oldValue: null, newValue: "Commenting the Id Column")
},
}),
// ...


Moving on to the second part: adjust the SQL generator to create comments from annotations.

This one is for Oracle, but MS Sql should be very similar

class CustomOracleSqlCodeGen : MigrationSqlGenerator
{
// the actual SQL generator
private readonly MigrationSqlGenerator _innerSqlGenerator;

public CustomOracleSqlCodeGen(MigrationSqlGenerator innerSqlGenerator)
{
_innerSqlGenerator = innerSqlGenerator;
}

public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
{
var ms = _innerSqlGenerator.Generate(AddCommentSqlStatements(migrationOperations), providerManifestToken);

return ms;
}

// generate additional SQL operations to produce comments
IEnumerable<MigrationOperation> AddCommentSqlStatements(IEnumerable<MigrationOperation> migrationOperations)
{
foreach (var migrationOperation in migrationOperations)
{
// the original inputted operation
yield return migrationOperation;

// create additional operations to produce comments
if (migrationOperation is CreateTableOperation cto)
{
foreach (var ctoAnnotation in cto.Annotations.Where(x => x.Key == "Comment"))
{
if (ctoAnnotation.Value is string annotation)
{
var commentString = annotation.Replace(
CommentConvention.NewLinePlaceholder,
Environment.NewLine);

yield return new SqlOperation($"COMMENT ON TABLE {cto.Name} IS '{commentString}'");
}
}

foreach (var columnModel in cto.Columns)
{
foreach (var columnModelAnnotation in columnModel.Annotations.Where(x => x.Key == "Comment"))
{
if (columnModelAnnotation.Value is AnnotationValues annotation)
{
var commentString = (annotation.NewValue as string)?.Replace(
CommentConvention.NewLinePlaceholder,
Environment.NewLine);

yield return new SqlOperation(
$"COMMENT ON COLUMN {cto.Name}.{columnModel.Name} IS '{commentString}'");
}
}
}
}
}
}
}


In the DbMigrationsConfiguration constructor, register the new code generator (again, this is oracle specific but will be similar for other SQL providers)

DbMigrationsConfiguration

internal sealed class Configuration : DbMigrationsConfiguration<EntityFramework.Dev.ZdbTestContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
var cg = GetSqlGenerator("Oracle.ManagedDataAccess.Client");
SetSqlGenerator("Oracle.ManagedDataAccess.Client", new CustomOracleSqlCodeGen(cg));
}
// ...


Expected result: The comment annotations from the Up and Down methods are translated to SQL statements that alter the comments in database.

Up

Down

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.