sql-server - Entity Framework Core 编写 MSSQL Server Extended Properties

Question

是否可以通过 Fluent-API 或 DataAnnotation 为表/模式创建 MSSQL Server 特定的扩展属性？我想将我的文档包含到 sql server 表中以满足我们的 DBA。

亲切的问候

Answer 1

我开始使用 EntityFrameworkCore.Scaffolding.Handlebars 来实现，但没时间了。这些是调查结果:

添加

    public class ScaffoldingDesignTimeServices : IDesignTimeServices
    {
        public void ConfigureDesignTimeServices(IServiceCollection services)
        {
            services.AddSingleton<IDatabaseModelFactory, SqlServerDatabaseModelFactory2>();

            var options = ReverseEngineerOptions.DbContextAndEntities;

            services.AddHandlebarsScaffolding(options);

            // https://github.com/TrackableEntities/EntityFrameworkCore.Scaffolding.Handlebars/issues/30
            Handlebars.RegisterHelper("f-pn", FormatPropertyName);
        }

        void FormatPropertyName(TextWriter writer, object context, object[] args)
        {
            writer.WriteSafeString(args[0].ToString());
        }
    }

从复制 SqlServerDatabaseModelFactory SqlServerDatabaseModelFactory并使用这样的功能对其进行自定义

    DbConnection connection,
    IReadOnlyList<DatabaseTable> tables,
    string tableFilter,
    IReadOnlyDictionary<string, (string storeType, string typeName)> typeAliases)
        {
            using (var command = connection.CreateCommand())
            {
                var commandText = @"
SELECT      u.name AS [table_schema],
            t.name AS [table_name],
            td.value AS [table_desc],
            c.name AS [column_name],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder";

                command.CommandText = commandText;

                using (var reader = command.ExecuteReader())
                {
                    var tableColumnGroups = reader.Cast<DbDataRecord>()
                        .GroupBy(
                            ddr => (tableSchema: ddr.GetValueOrDefault<string>("table_schema"),
                                tableName: ddr.GetValueOrDefault<string>("table_name")));

                    foreach (var tableColumnGroup in tableColumnGroups)
                    {
                        var tableSchema = tableColumnGroup.Key.tableSchema;
                        var tableName = tableColumnGroup.Key.tableName;

                        var table = tables.Single(t => t.Schema == tableSchema && t.Name == tableName);

                        foreach (var dataRecord in tableColumnGroup)
                        {
                            var columnName = dataRecord.GetValueOrDefault<string>("column_name");
                            var tableDesc = dataRecord.GetValueOrDefault<string>("table_desc");
                            var columnDesc = dataRecord.GetValueOrDefault<string>("column_desc");

                            //_logger.ColumnFound(
                            //    DisplayName(tableSchema, tableName),
                            //    columnName,
                            //    ordinal,
                            //    DisplayName(dataTypeSchemaName, dataTypeName),
                            //    maxLength,
                            //    precision,
                            //    scale,
                            //    nullable,
                            //    isIdentity,
                            //    defaultValue,
                            //    computedValue);

                            table.Description = tableDesc; ???

                            table.Columns.FirstOrDefault(x => x.Name == columnName)?.Description = columnDesc; ???;
                        }
                    }
                }
            }
        }

制作表格和列描述的字典并使用 Handlebars Helpers/Transformers EntityFrameworkCore.Scaffolding.Handlebars