问题描述
我首先使用带有显式迁移的 Entity Framework 4.3.1 代码.如何在实体配置类或迁移中添加列的描述,使其最终成为 SQL Server 中列的描述(例如 2008 R2)?
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)?
我知道我可能可以为 DbMigration
类编写一个扩展方法,它将 sp_updateextendedproperty
或 sp_addextendedproperty
过程调用注册为 sql 迁移迁移事务内部的操作,并在迁移 Up
方法中创建表后调用该扩展.但是有没有一种我还没有发现的优雅的内置方式?如果有一个属性,迁移的更改检测逻辑可以获取并在脚手架迁移中生成适当的方法调用,那就太好了.
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.
推荐答案
我也需要这个.所以我花了一天时间,结果如下:
I needed this too. So I spent a day and here it is:
守则
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;
}
}
如何使用
在您的 Migrations/Configuration.cs
文件中,将其添加到 Seed
方法的末尾:
In your Migrations/Configuration.cs
file, add this at the end of the Seed
method:
DbDescriptionUpdater<ContextClass> updater = new DbDescriptionUpdater<ContextClass>(context);
updater.UpdateDatabaseDescriptions();
然后在包管理器控制台中键入 update-database
并按 Enter.就是这样.
Then in Package Manager Console type update-database
and hit Enter.
That's it.
代码使用实体类属性上的 [Display(Name="Description here")]
属性来设置描述.
The code uses [Display(Name="Description here")]
attribute on your entity class properties to set the description.
请报告任何错误或提出改进建议.
Please report any bug or suggest improvements.
感谢
我使用了其他人的这些代码,我想说声谢谢:
I've used these code from other people and I want to say thanks:
添加列说明
检查类是否派生自泛型类
从实体框架元数据中获取数据库表名
C# 中的泛型,使用变量类型作为参数
这篇关于如何首先使用迁移向 Entity Framework 4.3 代码中的列添加描述?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!