c# - update - ¿Cómo agregar una descripción a las columnas en el código de Entity Framework 4.3 primero usando migraciones?

se han encontrado varios tipos de contexto en el ensamblado

Question

Estoy usando el código de Entity Framework 4.3.1 primero con migraciones explícitas. ¿Cómo agrego descripciones para las columnas en las clases de configuración de la entidad o las migraciones, de modo que termine como la descripción de una columna en el servidor SQL (por ejemplo, 2008 R2)?

Sé que probablemente pueda escribir un método de extensión para la clase DbMigration que registraría la llamada al procedimiento sp_updateextendedproperty o sp_addextendedproperty como una operación de migración de SQL dentro de la transacción de migración y llamar a esa extensión después de la creación de la tabla en el método Up migración. ¿Pero hay una forma elegante construida que aún no he descubierto? Sería bueno tener un atributo en el que la lógica de detección de cambios de las migraciones pueda captar y generar llamadas a métodos apropiados en la migración de andamios.

Answer 1

¿No puedes usar el método ExceuteSqlCommand ? Aquí, puede definir explícitamente cualquier meta propiedad que desee agregar en su Tabla.

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

Answer 2

Gracias Sr.Mahmoodvcs por la gran solución. Permítame modificarlo, simplemente reemplace "DisplayAttribute" con "DescriptionAttribute" en lugar de usar:

[Display(Name="Description here")]

usarás :

[Description("Description here")]

así que incluye la mesa también.

 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;
}
}

Answer 3

Nota bastante satisfecho con la respuesta actual (¡pero con los accesorios para el trabajo!), Quería una forma de obtener el margen de comentarios existente en mis clases en lugar de usar atributos. Y en mi opinión, no sé por qué diablos Microsoft no lo admitió, ya que parece obvio que debería estar allí.

Primero, active el archivo de documentación XML: Propiedades del proyecto-> Crear-> Archivo de documentación XML-> App_Data / YourProjectName.XML

En segundo lugar, incluir el archivo como un recurso incrustado. Cree su proyecto, vaya a App_Data, muestre los archivos ocultos e incluya el archivo XML que se generó. Seleccione el recurso incrustado y Copiar si es más nuevo (esto es opcional, podría especificar la ruta de forma explícita, pero en mi opinión esto es más limpio). Tenga en cuenta que debe usar este método, ya que el marcado no está presente en el ensamblaje y le evitará ubicar dónde se almacena su XML.

Aquí está la implementación del código que es una versión modificada de la respuesta aceptada:

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;
}
}

Y la clase que obtiene el marcado de comentarios del archivo de documentación XML generado por Visual Studio:

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
}

Answer 4

Si bien la pregunta es sobre EF4, esta respuesta apunta a EF6, que debería ser apropiada dado el tiempo transcurrido desde que se hizo la pregunta.

Creo que los comentarios pertenecen a los métodos de migración Up y Down lugar de algún método de Seed .

Entonces, como lo sugiere @MichaelBrown, comience por habilitar el resultado de la documentación XML e incluya el archivo de documentación como recurso incrustado en su proyecto.

Luego, convierta los comentarios en una anotación de tabla / columna utilizando un Convention . Se deben hacer algunos ajustes para cosas como comentarios de varias líneas y para eliminar el espacio en blanco excesivo.

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[] {"/r/n", "/r", "/n"}, StringSplitOptions.None)
.Select(line => line.Trim()));
}
}

Registre la convención en el método OnModelCreating .

Resultado esperado: cuando se crea una nueva migración, los comentarios se incluirán como anotaciones como

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")
},
}),
// ...

Pasando a la segunda parte: ajuste el generador de SQL para crear comentarios a partir de anotaciones.

Este es para Oracle, pero MS Sql debería ser muy 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}''");
}
}
}
}
}
}
}

En el constructor DbMigrationsConfiguration , registre el nuevo generador de código (de nuevo, esto es específico de DbMigrationsConfiguration , pero será similar para otros proveedores de SQL)

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));
}
// ...

Resultado esperado: las anotaciones de comentarios de los métodos de Up y Down se convierten en declaraciones SQL que alteran los comentarios en la base de datos.

Answer 5

Yo también necesitaba esto. Así que pasé un día y aquí está:

El código

 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;
}
}

Cómo utilizar

En su archivo Migrations/Configuration.cs , agregue esto al final del método Seed :

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

Luego, en la consola del administrador de paquetes, escriba update-database y presione Enter. Eso es.

El código usa el atributo [Display(Name="Description here")] en las propiedades de su clase de entidad para establecer la descripción.

Por favor, informe de cualquier error o sugerir mejoras.

Gracias a

He usado estos códigos de otras personas y quiero darles las gracias:

añadiendo una descripción de columna

Compruebe si una clase se deriva de una clase genérica

Obtener el nombre de la tabla de base de datos de Entity Framework MetaData

Genéricos en C #, usando el tipo de una variable como parámetro