Get Database Table Name from Entity Framework MetaData

Question

I use Nigel's approach (extracting table name from .ToTraceString()) but with some modifications, because his code won't work if the table is not in the default SQL Server schema (dbo.{table-name}).

I've created extension methods for DbContext and ObjectContext objects:

public static class ContextExtensions
{
    public static string GetTableName<T>(this DbContext context) where T : class
    {
        ObjectContext objectContext = ((IObjectContextAdapter) context).ObjectContext;

        return objectContext.GetTableName<T>();
    }

    public static string GetTableName<T>(this ObjectContext context) where T : class
    {
        string sql = context.CreateObjectSet<T>().ToTraceString();
        Regex regex = new Regex(@"FROM\s+(?<table>.+)\s+AS");
        Match match = regex.Match(sql);

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

More details here:
Entity Framework: Get mapped table name from an entity

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 EntitySetBase GetEntitySet(Type type)
{
    //If it's a proxy, get the entity type associated with it
    type = ObjectContext.GetObjectType(type);

    if (_mappingCache.ContainsKey(type))
        return _mappingCache[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

Most of the answers here don't work with derived classes. This one does. And gives you the schema too. I combined the answers here and improved on it a little (by taking out things like First() and Single() and converting them to things like Where() and SelectMany() and returning the schema name).

This works with EF 6.1+

// This can return multiple values because it is possible to have one entity correspond to multiple tables when doing entity splitting.
    public static IEnumerable<string> GetTableName<T>(this DbContext context)
    {
        var type = typeof(T);

        var metadata = ((IObjectContextAdapter)context).ObjectContext.MetadataWorkspace;

        // Get the part of the model that contains info about the actual CLR types
        var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace));

        // Get the entity type from the model that maps to the CLR type
        var entityType = metadata
                .GetItems<EntityType>(DataSpace.OSpace)
                .Single(e => objectItemCollection.GetClrType(e) == type);

        // Get the entity set that uses this entity type
        var entitySet = metadata.GetItems(DataSpace.CSpace).Where(x => x.BuiltInTypeKind == BuiltInTypeKind.EntityType).Cast<EntityType>().Single(x => x.Name == entityType.Name);

        // Find the mapping between conceptual and storage model for this entity set
        var entitySetMappings = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace).Single().EntitySetMappings.ToList();

        // Find the storage entity sets (tables) that the entity is mapped
        //EntitySet table;

        var fragments = new List<MappingFragment>();

        var mappings = entitySetMappings.Where(x => x.EntitySet.Name == entitySet.Name);

        //if (mappings.Count() > 0)
        //return mappings.SelectMany(m => m.EntityTypeMappings.SelectMany(em => em.Fragments)).ToList();

        fragments.AddRange(mappings.SelectMany(m => m.EntityTypeMappings.SelectMany(em => em.Fragments)));

        fragments.AddRange(entitySetMappings.Where(x => x.EntityTypeMappings.Where(y => y.EntityType != null).Any(y => y.EntityType.Name == entitySet.Name))
            .SelectMany(m => m.EntityTypeMappings.Where(x => x.EntityType != null && x.EntityType.Name == entityType.Name).SelectMany(x => x.Fragments)));

        //if (mapping != null)
        //return mapping.EntityTypeMappings.Where(x => x.EntityType != null).Single(x => x.EntityType.Name == entityType.Name).Fragments;

        fragments.AddRange(entitySetMappings.Where(x => x.EntityTypeMappings.Any(y => y.IsOfEntityTypes.Any(z => z.Name == entitySet.Name)))
        .SelectMany(m => m.EntityTypeMappings.Where(x => x.IsOfEntityTypes.Any(y => y.Name == entitySet.Name)).SelectMany(x => x.Fragments)));

        //var fragments = getFragments();

        // Return the table name from the storage entity set

        var tableNames = fragments.Select(f =>
        {
            var schemaName = f.StoreEntitySet.Schema;
            var tableName = (string)f.StoreEntitySet.MetadataProperties["Table"].Value ?? f.StoreEntitySet.Name;
            var name = $"[{schemaName}].[{tableName}]";
            return name;
        }).Distinct().ToList();

        return tableNames;
    }

No, unfortunately it is impossible using the Metadata APIs to get to the tablename for a given entity.

This is because the Mapping metadata is not public, so there is no way to go from C-Space to S-Space using the EF's APIs.

If you really need to do this you could always build the map yourself by parsing the MSL. This is not for the faint of heart, but it should be possible, unless you are using QueryViews (which are incredibly rare), at which point it is for all intents and purposes impossible (you would have to parse ESQL... argh!)

Alex James

Microsoft.

There is a way to delete data using EF without having to load it first I described it in a little more detain in: http://nigelfindlater.blogspot.com/2010/04/how-to-delete-objects-in-ef4-without.html

The trick is to cast the IQueriable into an ObjectQuery and use the ToTraceString method. Then edit the resulting sql string. It works but you need to be careful because you are bypassing the the mechanisms that EF has in place for maintaining dependancies and contraints. But for performance reasons I think it's ok to do this....

have fun...

Nigel...

    private string GetClause<TEntity>(IQueryable<TEntity> clause) where TEntity : class 
    { 
        string snippet = "FROM [dbo].["; 

        string sql = ((ObjectQuery<TEntity>)clause).ToTraceString(); 
        string sqlFirstPart = sql.Substring(sql.IndexOf(snippet)); 

        sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", ""); 
        sqlFirstPart = sqlFirstPart.Replace("[Extent1].", ""); 

        return sqlFirstPart; 
    } 

   public void DeleteAll<TEntity>(IQueryable<TEntity> clause) where TEntity : class 
    { 
        string sqlClause = GetClause<TEntity>(clause); 
        this.context.ExecuteStoreCommand(string.Format(CultureInfo.InvariantCulture, "DELETE {0}", sqlClause)); 
    } 

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