Get Database Table Name from Entity Framework Meta

Question

I'm trying to figure out a way to get the underlying SQL table name for a given entity type. I've experimented around with the MetadataWorkspace queries and while I can get lots of information from the object or the storage space, I can't seem to figure out how to map between the two.

So say I have a type in the object model called Lookup - how do I find the tablename (wws_lookups) in the database?

I can query all the EntityType objects for CSpace and SSpace and I can see both listed correctly but I can't figure out how to get SSpace from CSpace.

Is there any way to do this?

Answer 1

For EF6, mixing/compressing code from other answers here and around (VB, I'm sorry):

    Public Function getDBTableName(data As myDataModel, ByVal entity As Object) As String
        Dim context = CType(data, IObjectContextAdapter).ObjectContext
        Dim sName As String = entity.GetType.BaseType.Name 'use BaseType to avoid proxy names'
        Dim map = context.MetadataWorkspace.GetItems(Of EntityContainerMapping)(DataSpace.CSSpace).FirstOrDefault

        Return (From esmap In map.EntitySetMappings 
                Select esmap.EntityTypeMappings.First(
                    Function(etm) 
                     etm.EntityType.Name = sName
                   ).Fragments.First.StoreEntitySet.Name).FirstOrDefault
        'TODO: use less .first everywhere but filter the correct ones'
    End Function

It works for db-first.
Relatively easy to understand following an .edmx file.

Answer 2

Adding another answer for the special case when you use annotations to explicitly tell EF which table name to use. For example, if you have:

[Table("tblCompany")]
public class Company
{
}

You can easily access the TableAttribute annotation to find the table name:

((System.ComponentModel.DataAnnotations.Schema.TableAttribute)typeof(YourNamespace.BO.Company).GetCustomAttribute(typeof(System.ComponentModel.DataAnnotations.Schema.TableAttribute))).Name

which is tblCompany for the given sample.

As ready-to-use method:

using System.Reflection;
using System.ComponentModel.DataAnnotations.Schema;

public static string GetTableName<T>() {
    return ((TableAttribute)typeof(T).GetCustomAttribute(typeof(TableAttribute))).Name;
}

(I am aware that this will not help the OP but given the title of the question, people may end up here who may be looking for this answer.)

Answer 3

If you're doing codefirst in EF6, you can just add something like the following to your dbcontext class.

    public string GetTableName(Type entityType)
    {
        var sql = Set(entityType).ToString();
        var regex = new Regex(@"FROM \[dbo\]\.\[(?<table>.*)\] AS");
        var match = regex.Match(sql);

        return match.Groups["table"].Value;
    }

Answer 4

Here is a version assuming you have context and have a selected entity in memory that you need to find the real table name for.

    
    public static class ObjectContextExtentions
    {
        public static string TableNameFor(this ObjectContext context, ObjectStateEntry entry)
        {
            var generic =
                context.GetType().GetProperties().ToList().First(p => p.Name == entry.EntityKey.EntitySetName);
            var objectset = generic.GetValue(context, null);

            var method = objectset.GetType().GetMethod("ToTraceString");
            var sql = (String)method.Invoke(objectset, null);

            var match = Regex.Match(sql, @"FROM\s+\[dbo\]\.\[(?<TableName>[^\]]+)\]", RegexOptions.Multiline);
            if (match.Success)
            {
                return match.Groups["TableName"].Value;
            }

            throw new ArgumentException("Unable to find Table name.");
        } 
    }

Answer 5

EDIT This answer now obsolete due to new feature in EF 6.1 : mapping between table types. Go there first!

I had a problem with the other answers because I have a derived type. I got this method (inside my context class) to work - I have only one layer of inheritance in my model at the moment

private readonly static Dictionary<Type, EntitySetBase> _mappingCache 
       = new Dictionary<Type, EntitySetBase>();

private ObjectContext _ObjectContext
{
    get { return (this as IObjectContextAdapter).ObjectContext; }
}

private EntitySetBase GetEntitySet(Type type)
{
    if (_mappingCache.ContainsKey(type))
        return _mappingCache[type];

    type = GetObjectType(type);
    string baseTypeName = type.BaseType.Name;
    string typeName = type.Name;

    ObjectContext octx = _ObjectContext;
    var es = octx.MetadataWorkspace
                    .GetItemCollection(DataSpace.SSpace)
                    .GetItems<EntityContainer>()
                    .SelectMany(c => c.BaseEntitySets
                                    .Where(e => e.Name == typeName 
                                    || e.Name == baseTypeName))
                    .FirstOrDefault();

    if (es == null)
        throw new ArgumentException("Entity type not found in GetEntitySet", typeName);

    // Put es in cache.
    _mappingCache.Add(type, es);

    return es;
}

internal String GetTableName(Type type)
{
    EntitySetBase es = GetEntitySet(type);

    //if you are using EF6
    return String.Format("[{0}].[{1}]", es.Schema, es.Table);

    //if you have a version prior to EF6
    //return string.Format( "[{0}].[{1}]", 
    //        es.MetadataProperties["Schema"].Value, 
    //        es.MetadataProperties["Table"].Value );
}

internal Type GetObjectType(Type type)
{
    return System.Data.Entity.Core.Objects.ObjectContext.GetObjectType(type);
}

NB There are plans to improve the Metadata API and if this isn't getting what we want then we can look at EF Code First Mapping Between Types & Tables

Answer 6

Here is another way to find the table name. It is a bit odd but works. VB:

For Each Table In northwind.MetadataWorkspace.GetItemCollection(New System.Data.Metadata.Edm.DataSpace)
        'adds table name to a list of strings all table names in EF have the project namespace in front of it.'
        If Table.ToString.Contains("namespace of project") then
            'using substring to remove project namespace from the table name.'
            TableNames.Add(Table.ToString.Substring("length of namespace name"))      
        End If
    Next