.net 如何首先使用迁移向EntityFramework4.3代码中的列添加描述？

qnzebej0  于 2023-01-10  发布在  .NET
关注(0)|答案(6)|浏览(107)

我首先使用实体框架4.3.1代码进行显式迁移。如何在实体配置类或迁移中添加列的描述，以便它最终成为SQL Server（例如2008 R2）中列的描述？
我知道我可能可以为DbMigration类编写一个扩展方法，该方法将sp_updateextendedproperty或sp_addextendedproperty过程调用注册为迁移事务中的sql迁移操作，并在迁移Up方法中创建表后调用该扩展。但是，是否有一种优雅的内置方式，我还没有发现？如果有一个属性，迁移'改变检测逻辑可以拾取并生成搭建迁移中的适当方法调用。

.net

6条答案

按热度按时间
nbysray5

nbysray51#

我也需要这个。所以我花了一天时间，它就在这里：

    • 法典**
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方法的末尾：

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

然后在软件包管理器控制台中键入update-database并按Enter键。
代码使用实体类属性上的[Display(Name="Description here")]属性来设置描述。
请报告任何错误或提出改进建议。
"感谢"
我已经使用这些代码从其他人，我想说谢谢：
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

赞(0）分享  回复(0）举报  2023-01-10
vsaztqbk

vsaztqbk2#

Note对当前的答案非常满意（但这是工作的 prop ！），我希望有一种方法来拉我的类中现有的注解标记，而不是使用属性。在我看来，我不知道为什么微软不支持这一点，因为它似乎显然应该存在！
首先，打开XML文档文件：项目属性-〉生成-〉XML文档文件-〉App_Data\您的项目名称.XML
其次，将该文件作为嵌入式资源包括在内。生成您的项目，转到App_Data，显示隐藏文件并包括生成的XML文件。选择“嵌入式资源”和“复制（如果较新（这是可选的，你可以显式地指定路径，但在我看来这更简洁）。注意，你必须使用这个方法，因为标记不存在于程序集中，并且将使您不必查找XML的存储位置。
下面是代码实现，它是已接受答案的修改版本：

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

以及从visual studio生成的XML文档文件中获取注解标记的类：

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
}
赞(0）分享  回复(0）举报  2023-01-10
jfewjypa

jfewjypa3#

你可以不使用ExceuteSqlCommand方法。在这里，你可以显式地定义你想添加到你的表中的任何 meta属性。
http://msdn.microsoft.com/en-us/library/system.data.entity.database.executesqlcommand(v=vs.103).aspx

赞(0）分享  回复(0）举报  2023-01-10
2nc8po8w

2nc8po8w4#

谢谢你Mahmoodvcs先生的伟大解决方案。请允许我修改它，只需将“显示属性”替换为“描述属性”，而不是使用：

[Display(Name="Description here")]

您将用途：

[Description("Description here")]

所以它也包括table。

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;
    }
}
赞(0）分享  回复(0）举报  2023-01-10
yfwxisqw

yfwxisqw5#

虽然问题是关于EF4的，但此答案针对的是EF6，考虑到自提出问题以来经过的时间，这应该是适当的。
我认为注解属于迁移Up和Down方法，而不是某个Seed方法。
因此，正如@MichaelBrown所建议的，从启用XML文档输出开始，并将文档文件作为嵌入式资源包含在项目中。
然后，让我们使用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[] {"\r\n", "\r", "\n"}, StringSplitOptions.None)
                .Select(line => line.Trim()));
    }
}

在OnModelCreating方法中注册约定。
预期结果：创建新迁移时，注解将作为注解包括在内，如

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

接下来是第二部分：调整SQL生成器以从注解创建注解。
这是针对Oracle的，但MS Sql应该非常相似

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}'");
                        }
                    }
                }
            }
        }
    }
}

在DbMigrationsConfiguration构造函数中，注册新的代码生成器（同样，这是Oracle特有的，但对于其他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));
    }
    // ...

预期结果：Up和Down方法中的注解注解被转换为SQL语句，这些语句可更改数据库中的注解。

赞(0）分享  回复(0）举报  2023-01-10
dy2hfwbg

dy2hfwbg6#

现在到了2023年，我已经将Mahmood Dehghan提供的解决方案更新为.Net 6和Entity Framework Core 6 here，并做了一些小的修改，以包括来自基类的属性（如果主类中不存在），并将扩展属性添加到表中。

赞(0）分享  回复(0）举报  2023-01-10
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com