Entity Framework - how do I get the columns?

Question

I wish to get a list of columns names, types and whether the column is a PK of a table object in Entity Framework.

How do I do this in C# (4.0) (ideally generically)?

The winning answer will be one that does it efficiently and most importantly generically.

Answer 1

Got it - I used a linq based reflection query:

IEnumerable<FieldList> properties = from p in typeof(T).GetProperties()
                                    where (from a in p.GetCustomAttributes(false)
                                    where a is EdmScalarPropertyAttribute   
                                    select true).FirstOrDefault()

Sorted! Thanks for the suggestions all.

FYI - I am creating a dynamic where clause using LINQ, dynamic lambda expressions to build e.g. search which will automatically search through all columns by default. But I also needed the column names to verify because I will allow this to be overridden and these calls will be done via javascript ajax post whose input cannot be trusted - so needed to verify the column names.

I used the above to place the results into a custom object with properties called FieldName, FieldType, PrimaryKey. Ta daaa.

Customise it further with

IEnumerable<FieldList> properties = from p in typeof(T).GetProperties()
                                    where (from a in p.GetCustomAttributes(false)
                                    where a is EdmScalarPropertyAttribute
                                    select true).FirstOrDefault()
                                    select new FieldList
                                    {
                                       FieldName = p.Name,
                                       FieldType = p.PropertyType,
                                       FieldPK = p.GetCustomAttributes(false).Where(a => a is EdmScalarPropertyAttribute && ((EdmScalarPropertyAttribute)a).EntityKeyProperty).Count() > 0
                                     };    

Answer 2

if you want only column names then ,i got the best answer :
var properties = (from t in typeof(YourTableName).GetProperties() select t.Name).ToList(); var name= properties[0];

Answer 3

If you do not want to use reflection, see answer here. Replace entity name below with your entity name

var cols = from meta in ctx.MetadataWorkspace.GetItems(DataSpace.CSpace)
                       .Where(m=> m.BuiltInTypeKind==BuiltInTypeKind.EntityType)
                    from p in (meta as EntityType).Properties
                       .Where(p => p.DeclaringType.Name == "EntityName")
                   select new
                      {
                       PropertyName = p.Name,
                       TypeUsageName = p.TypeUsage.EdmType.Name, //type name
                       Documentation = p.Documentation != null ?               
                                       p.Documentation.LongDescription : null //if primary key
        };

Answer 4

If anyone is still looking, Here's how I did it. This is an extension method for the DBContext that takes a type and returns physical column names and their properties.

This utilizes object context to get physical columns list, then uses the "PreferredName" metadata property to map each column it its property.

Since it uses object context, it initiates a database connection, so the first run will be slow depending on the complexity of the context.

public static IDictionary<String, PropertyInfo> GetTableColumns(this DbContext ctx, Type entityType)
{
    ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
    EntityType storageEntityType = octx.MetadataWorkspace.GetItems(DataSpace.SSpace)
        .Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>()
        .Single(x => x.Name == entityType.Name);

    var columnNames = storageEntityType.Properties.ToDictionary(x => x.Name,
        y => y.MetadataProperties.FirstOrDefault(x => x.Name == "PreferredName")?.Value as string ?? y.Name);

    return storageEntityType.Properties.Select((elm, index) =>
            new {elm.Name, Property = entityType.GetProperty(columnNames[elm.Name])})
        .ToDictionary(x => x.Name, x => x.Property);
}

To use it, just create a helper static class, and add above function; then it's as simple as calling

var tabCols = context.GetTableColumns(typeof(EntityType));

Answer 5

typeof(TableName).GetProperties().Select(x => x.Name).ToList();

Answer 6

I don't have a a code sample for you, but just so that you're pointed in the right direction, you might want to look into using the Sql Management Objects (SMO); you can use this to get an object hierarchy for an Sql Server instance, which you can then enumerate and pick out the information you need.

Have a look at this set of tutorials to get you started with the programming - http://www.codeproject.com/KB/database/SMO_Tutorial_1.aspx http://www.codeproject.com/KB/database/SMO_Tutorial_2.aspx

Answer 7

If you're using DB First or Model First, open up the .edmx file EF generated in a text editor. It's just an XML file, and it contains everything you need. Here's an example from a model of mine. Note that I'm using Oracle's EF driver, so yours won't look identical (but it should be pretty close).

        <EntityType Name="STRATEGIC_PLAN">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Type="Decimal" Name="Id" Nullable="false" Precision="8" Scale="0" annotation:StoreGeneratedPattern="None" />
          <Property Type="Decimal" Name="CreatedById" Nullable="false" Precision="8" Scale="0" />
          <Property Type="DateTime" Name="CreatedDate" Nullable="false" />
          <Property Type="Decimal" Name="DepartmentId" Nullable="false" Precision="4" Scale="0" />
          <Property Type="String" Name="Name_E" Nullable="false" MaxLength="2000" FixedLength="false" Unicode="false" />
          <Property Type="String" Name="Name_F" MaxLength="2000" FixedLength="false" Unicode="false" />
          <Property Type="Decimal" Name="UpdatedById" Precision="8" Scale="0" />
          <Property Type="DateTime" Name="UpdatedDate" />
          <Property Type="DateTime" Name="Timestamp" Nullable="false" Precision="6" annotation:StoreGeneratedPattern="Computed" />
          <NavigationProperty Name="AnnualPlans" Relationship="StrategicPlanningModel.R_51213" FromRole="STRATEGIC_PLAN" ToRole="STRAT_ANNUAL_PLAN" />
          <NavigationProperty Name="Department" Relationship="StrategicPlanningModel.R_51212" FromRole="STRATEGIC_PLAN" ToRole="DEPARTMENT" />
          <NavigationProperty Name="CreatedBy" Relationship="StrategicPlanningModel.R_51210" FromRole="STRATEGIC_PLAN" ToRole="STAFF" />
          <NavigationProperty Name="UpdatedBy" Relationship="StrategicPlanningModel.R_51211" FromRole="STRATEGIC_PLAN" ToRole="STAFF" />
          <Property Type="String" Name="Desc_E" MaxLength="2000" FixedLength="false" Unicode="false" />
          <Property Type="String" Name="Desc_F" MaxLength="2000" FixedLength="false" Unicode="false" />
          <NavigationProperty Name="Goals" Relationship="StrategicPlanningModel.R_51219" FromRole="STRATEGIC_PLAN" ToRole="STRATEGIC_PLAN_GOAL" />
        </EntityType>

You can use an XML parser to parse the file and get what you need. The .edmx file contains data on both the entities and the SQL tables, so you'll need to make sure you're getting the right part of it to get what you want.

Answer 8

To get only columns names without table relations eg. foreign key.

var columnNames = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Where(p => p.CanRead && !p.GetGetMethod()!.IsVirtual)
            .Select(property => property.Name)
            .ToList();

In DB context relations are usually marked as virtual objects (unless in my Postgres test).