CODEWITHSUNDEEP
c#entity-frameworkgenericsasp.net-mvc-3
Dan B
Dan B

Reputation: 936

Entity Framework - how do I get the columns?

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.

Upvotes: 19

Views: 37292

Answers (8)

Losbaltica
Losbaltica

Reputation: 649

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).

Upvotes: 0

Dragon Warrior
Dragon Warrior

Reputation: 327

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

Upvotes: 1

Mahmoud Hanafy
Mahmoud Hanafy

Reputation: 1173

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));

Upvotes: 3

Ashitosh birajdar
Ashitosh birajdar

Reputation: 470

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];

Upvotes: 9

Pete_ch
Pete_ch

Reputation: 1321

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
        };

Upvotes: 5

Dan B
Dan B

Reputation: 936

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
                                     };

Upvotes: 10

Tridus
Tridus

Reputation: 5081

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.

Upvotes: 0

Steve Hobbs
Steve Hobbs

Reputation: 3326

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

Upvotes: 0

Related Questions