Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

Entity Framework Metadata

Sometimes it is useful to know something about the physical storage of a code first model. Maybe it’s for SQL reporting or for any other matter, I sometimes have the need to look up the table name where an entity is stored or the column that holds some relation.

Entity Framework does not directly offer such information, but it is quite easy to get it. The key point here is the MetadataWorkspace property of the ObjectContext class. It exposes a cryptic GetItems method from which we can get to basically anywhere in the internal metadata configuration store. I have written wrappers to simplify using the configuration store, namely:

  • GetTables: return all of the mapped entities and their backend tables;
  • GetTableName: the name of the physical table where an entity is stored;
  • OneToMany/OneToOne/ManyToOne/ManyToMany: returns the collection of properties that support each of these relations for a specific entity type;
  • GetProperties: returns all of the scalar properties that are mapped for an entity type;
  • GetNavigationProperties: all of the navigation properties (one-to-one, one-to-many, many-to-one, many-to-many) exposed by an entity type;
  • GetIdProperties: the properties that store the id of an entity;
  • GetTableColumns: the mapped properties of an entity and their corresponding columns;
  • GetTableKeyColumns: the mapped properties that store the id of an entity and their corresponding columns;
  • GetTableNavigationColumns: the mapped properties that store the relations of an entity and their corresponding columns.

OK, so here is the code, as extension methods to DbContext:

   1: public static class DbContextExtensions
   2: {
   3:     public static IDictionary<Type, String> GetTables(this DbContext ctx)
   4:     {
   5:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
   6:         IEnumerable<EntityType> entities = octx.MetadataWorkspace.GetItemCollection(DataSpace.OSpace).GetItems<EntityType>().ToList();
   7:  
   8:         return(entities.ToDictionary(x => Type.GetType(x.FullName), x => GetTableName(ctx, Type.GetType(x.FullName))));
   9:     }
  10:  
  11:     public static String GetTableName(this DbContext ctx, Type entityType)
  12:     {
  13:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  14:         EntitySetBase et = octx.MetadataWorkspace.GetItemCollection(DataSpace.SSpace)
  15:             .GetItems<EntityContainer>()
  16:             .Single()
  17:             .BaseEntitySets
  18:             .Where(x => x.Name == entityType.Name)
  19:             .Single();
  20:  
  21:         String tableName = String.Concat(et.MetadataProperties["Schema"].Value, ".", et.MetadataProperties["Table"].Value);
  22:  
  23:         return (tableName);
  24:     }
  25:  
  26:     public static IEnumerable<PropertyInfo> OneToMany(this DbContext ctx, Type entityType)
  27:     {
  28:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  29:         EntityType et = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  30:  
  31:         return (et.NavigationProperties.Where(x => x.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && x.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).Select(x => entityType.GetProperty(x.Name, BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.SetProperty)).ToList());
  32:     }
  33:  
  34:     public static IEnumerable<PropertyInfo> OneToOne(this DbContext ctx, Type entityType)
  35:     {
  36:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  37:         EntityType et = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  38:  
  39:         return (et.NavigationProperties.Where(x => (x.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One || x.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne) && (x.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One || x.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne)).Select(x => entityType.GetProperty(x.Name, BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.SetProperty)).ToList());
  40:     }
  41:  
  42:     public static IEnumerable<PropertyInfo> ManyToOne(this DbContext ctx, Type entityType)
  43:     {
  44:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  45:         EntityType et = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  46:  
  47:         return (et.NavigationProperties.Where(x => x.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many && x.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One).Select(x => entityType.GetProperty(x.Name, BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.SetProperty)).ToList());
  48:     }
  49:  
  50:     public static IEnumerable<PropertyInfo> ManyToMany(this DbContext ctx, Type entityType)
  51:     {
  52:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  53:         EntityType et = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  54:         
  55:         return(et.NavigationProperties.Where(x => x.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many && x.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.Many).Select(x => entityType.GetProperty(x.Name, BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.SetProperty)).ToList());
  56:     }
  57:  
  58:     public static IEnumerable<PropertyInfo> GetIdProperties(this DbContext ctx, Type entityType)
  59:     {
  60:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  61:         EntityType et = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  62:  
  63:         return (et.KeyMembers.Select(x => entityType.GetProperty(x.Name)).ToList());
  64:     }
  65:  
  66:     public static IEnumerable<PropertyInfo> GetNavigationProperties(this DbContext ctx, Type entityType)
  67:     {
  68:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  69:         EntityType et = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  70:  
  71:         return (et.NavigationProperties.Select(x => entityType.GetProperty(x.Name)).ToList());
  72:     }
  73:  
  74:     public static IEnumerable<PropertyInfo> GetIdProperties(this DbContext ctx, Type entityType)
  75:     {
  76:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  77:         EntityType et = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  78:  
  79:         return (et.KeyMembers.Select(x => entityType.GetProperty(x.Name)).ToList());
  80:     }
  81:  
  82:     public static IDictionary<String, PropertyInfo> GetTableKeyColumns(this DbContext ctx, Type entityType)
  83:     {
  84:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  85:         EntityType storageEntityType = octx.MetadataWorkspace.GetItems(DataSpace.SSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  86:         EntityType objectEntityType = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  87:         return (storageEntityType.KeyMembers.Select((elm, index) => new { elm.Name, Property = entityType.GetProperty((objectEntityType.MetadataProperties["Members"].Value as IEnumerable<EdmMember>).ElementAt(index).Name) }).ToDictionary(x => x.Name, x => x.Property));
  88:     }
  89:  
  90:     public static IDictionary<String, PropertyInfo> GetTableColumns(this DbContext ctx, Type entityType)
  91:     {
  92:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
  93:         EntityType storageEntityType = octx.MetadataWorkspace.GetItems(DataSpace.SSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  94:         EntityType objectEntityType = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
  95:         return (storageEntityType.Properties.Select((elm, index) => new { elm.Name, Property = entityType.GetProperty(objectEntityType.Members[index].Name) }).ToDictionary(x => x.Name, x => x.Property));
  96:     }
  97:  
  98:     public static IDictionary<String, PropertyInfo> GetTableNavigationColumns(this DbContext ctx, Type entityType)
  99:     {
 100:         ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
 101:         EntityType storageEntityType = octx.MetadataWorkspace.GetItems(DataSpace.SSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
 102:         EntityType objectEntityType = octx.MetadataWorkspace.GetItems(DataSpace.OSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).OfType<EntityType>().Where(x => x.Name == entityType.Name).Single();
 103:         return (storageEntityType.NavigationProperties.Select((elm, index) => new { elm.Name, Property = entityType.GetProperty(objectEntityType.Members[index].Name) }).ToDictionary(x => x.Name, x => x.Property));
 104:     }
 105: }

All of these methods have a generic version and a non-generic version; each take a Type for which to retrieve the metadata.

If you need some more information, feel free to ask me! Winking smile

Comments

Adam said:

Thanks for these.

What about the opposite of GetTableName; takes the table name as a parameter and returns the entity type?

# June 12, 2013 7:15 PM

Ricardo Peres said:

Adam:

ctx.GetTables().Single(x => x.Value == "TableName").Key;

;-)

# June 13, 2013 4:47 AM

Adam said:

Ah, of course. Thanks.

# June 13, 2013 3:58 PM

Cyrus Neah said:

This could be very useful when building modeling tools on top of Entity Framework.

# June 13, 2013 6:20 PM