Entity Framework - how to get database column data

Question

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

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

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.

Thanks, Aaron

Answer 1

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

 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.

Answer 2

Yes, this is possible: (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

Answer 3

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.

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.

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.

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.