Entity Framework - how to get database column datatype from metadata

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

Community · Accepted Answer · 2017-05-23 12:28:30Z

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.

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Sep 29, 2010 at 2:15

Alex James

20.9k3 gold badges52 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

KristoferA Over a year ago

But... there are conceptual model attributes that contain that information for fields where it apply. Assuming that info is kept up to date you can load it from the CSDL rather than having to look up mappings etc.

KristoferA Over a year ago

Btw, if/when those attributes get out of sync it is a breeze to update them again with the model comparer in my add-in... ( huagati.blogspot.com/2010/07/… )

soslo Over a year ago

@KristoferA - Huagati.com - it does look like the CSDL file contains the length data that I am looking for - thank you.

Brian Hanf · Accepted Answer · 2013-08-07 18:26:47Z

0

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.

answered Aug 7, 2013 at 18:26

Brian Hanf

5642 gold badges11 silver badges31 bronze badges

Comments

InteXX · Accepted Answer · 2015-02-20 23:45:31Z

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

Collectives™ on Stack Overflow

Entity Framework - how to get database column datatype from metadata

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related