实体框架 - 如何从元数据中获取数据库列数据类型

Is there a way to get the database column DataType length information given a table's EntityType?

有没有办法在给定表的EntityType的情况下获取数据库列DataType长度信息?

Example SQL (SQL Server) that you can run to see precisely what information I am looking for:

您可以运行示例SQL(SQL Server)以准确查看我要查找的信息:

select 
    sys.tables.name as 'Table Name', 
    sys.columns.name as 'Column Name', 
    sys.systypes.name as 'DataType', 
    sys.columns.max_length as 'Max Length', 
    sys.columns.precision as 'Precision'
from 
    sys.columns, sys.systypes, sys.tables
where 
    sys.columns.system_type_id = sys.systypes.xtype 
    and sys.systypes.name <> 'sysname' 
    and sys.tables.type = 'U' 
    and sys.tables.name <> 'sysdiagrams'
    and sys.columns.object_id=sys.tables.object_id
order by 
    sys.tables.name, sys.columns.column_id;

The last 3 columns contain the data that I would like to have access to because I'm generating some documentation. One example reason for the documentation is: Entity Framework will throw an Exception by default if a string is set on a property that can't support it's length. A developer without access to the database metadata has a challenge with the discoverability of length requirements in this case.

最后3列包含我想要访问的数据,因为我正在生成一些文档。文档的一个示例原因是:如果在不支持其长度的属性上设置字符串,则实体框架将默认抛出异常。在这种情况下,无法访问数据库元数据的开发人员对长度要求的可发现性提出了挑战。

Thanks, Aaron

3 个解决方案

#1

Unfortunately no.

Even if that information is correctly captured in the SSDL (i.e. the Storage Schema Definition language) there is no public API in EF to go from C-Space (conceptual model) property to S-Space (storage model) column.

即使在SSDL中正确捕获了该信息(即存储模式定义语言),EF中也没有公共API可以从C空间(概念模型)属性转到S空间(存储模型)列。

If your model is simple you can perhaps infer that information, using the EF metadata workspace and some simple heuristics, but once things get even a little complicated, those heuristics will break down.

如果您的模型很简单,您可以使用EF元数据工作区和一些简单的启发式方法推断出这些信息,但是一旦事情变得有点复杂,那些启发式算法就会崩溃。

Your only option at that point is to write code to interpret MSL (mapping or CS-Space) files, and use that in conjunction with the MetadataWorkspace to go from C-Space to S-Space.

此时您唯一的选择是编写代码来解释MSL(映射或CS空间)文件,并将其与MetadataWorkspace一起用于从C-Space到S-Space。

EDIT: as pointed out by KristoferA you often have the attribute on the C-Space property, so you can go to directly to that. Unfortunately that is not always the case, and often it gets out of sync with the database.

编辑:正如KristoferA所指出的,你经常拥有C-Space属性的属性,所以你可以直接去那个。不幸的是,并非总是如此,并且通常它与数据库不同步。

#2

I'm pretty sure that Julie Lerman's book covers how to get maxlength, at least a tool to validate against it, by making changes in the POCO creation. Chapter 13, starts around page 356. Example 13-12 covers it, it starts with

我很确定Julie Lerman的书中介绍了如何通过改变POCO创建来获得maxlength,至少是一个验证它的工具。第13章,从第356页开始。例13-12涵盖了它,它从一开始

 string MaxLengthValidation(EdmProperty prop)...

it's copyrighted material so I won't cut/paste it, but I hope you can buy a copy of her book and get the info.

它是受版权保护的材料,所以我不会剪切/粘贴它,但我希望你能买到她的书的副本并获取信息。

#3

Yes, this is possible: (EF6.1)

是的,这是可能的:(EF6.1)

<Extension>
Public Function GetColumns(Of TEntity)(Db As IObjectContextAdapter) As List(Of DataColumn)
  Dim oMetadata As MetadataWorkspace
  Dim oObjects As ObjectItemCollection
  Dim oContext As ObjectContext
  Dim oColumn As DataColumn
  Dim oQuery As Func(Of EdmProperty, Boolean)
  Dim oType As EntityType

  GetColumns = New List(Of DataColumn)

  oContext = Db.ObjectContext
  oMetadata = oContext.MetadataWorkspace
  oObjects = oMetadata.GetItemCollection(DataSpace.OSpace)

  oType = oMetadata.GetItems(Of EntityType)(DataSpace.OSpace).
    Single(Function(EntityType As EntityType) oObjects.GetClrType(EntityType) Is GetType(TEntity))

  oQuery = Function(EdmProperty As EdmProperty) EdmProperty.DeclaringType.Name = oType.Name

  oType.Properties.ToList.ForEach(Sub(Column As EdmProperty)
                                    oColumn = New DataColumn With
                                              {
                                                .AutoIncrement = Column.IsStoreGeneratedIdentity,
                                                .AllowDBNull = Column.Nullable,
                                                .ColumnName = Column.Name,
                                                .DataType = Column.PrimitiveType.ClrEquivalentType,
                                                .Caption = Column.Name
                                              }

                                    If oColumn.DataType Is GetType(String) Then
                                      oColumn.MaxLength = Column.MaxLength.GetValueOrDefault
                                    Else
                                      oColumn.MaxLength = -1
                                    End If

                                    GetColumns.Add(oColumn)
                                  End Sub)
End Function

#1