Get Database Table Name from Entity Framework MetaData

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?

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.

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

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

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

If you are using the T4 template for POCO classes you can get it by altering the T4 Template. See snippet:

<#	
////////////////////////////////////////////////////////////////////////////////
region.Begin("Custom Properties");

string xPath = "//*[@TypeName='" + entity.FullName + "']";
XmlDocument doc = new XmlDocument();
doc.Load(inputFile);

XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace("edmx", "http://schemas.microsoft.com/ado/2008/10/edmx");

XmlNode item;
XmlElement root = doc.DocumentElement;
item = root.SelectSingleNode(xPath);

#>
	//<#= xPath #>
	//<#= entity.FullName #>
	//<#= (item == null).ToString() #>

<# if (item != null) #>
// Table Name from database
public string TableName { get { return "<#= item.ChildNodes[0].Attributes["StoreEntitySet"].Value #>"; } }
<#

region.End();

////////////////////////////////////////////////////////////////////////////////

A possible workaround (not great, but neither are the alternatives...):

var sql = Context.EntitySetName.ToTraceString();

...then parse the SQL, which should be quite simple.

Here's what I was able to come up with using LINQ to XML. The code gets the mappings for column names as well.

var d = XDocument.Load("MyModel.edmx");
XNamespace n = "http://schemas.microsoft.com/ado/2008/09/mapping/cs";
var l = (from etm in d.Descendants()
            where etm.Name == n + "EntityTypeMapping"
            let s = etm.Attribute("TypeName").Value
            select new
            {
                Name = s.Remove(0, s.IndexOf(".") + 1).Replace(")", ""),
                Table = etm.Element(n + "MappingFragment").Attribute("StoreEntitySet").Value,
                Properties = (from sp in etm.Descendants(n + "ScalarProperty")
                            select new
                            {
                                Name = sp.Attribute("Name").Value,
                                Column = sp.Attribute("ColumnName").Value
                            }).ToArray()
            }).ToArray();

A better way is to use the StoreItemCollection from the Metadata. This guy has already provided an example of using it: https://stackoverflow.com/questions/7493299/get-tables-and-relationships

EF 6.1, code-first:

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

public static string GetTableName(this DbContext context, Type t)
{
	ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
	return objectContext.GetTableName(t);
}

private static readonly Dictionary<Type,string> TableNames = new Dictionary<Type, string>();

public static string GetTableName(this ObjectContext context, Type t)
	{
		string result;

		if (!TableNames.TryGetValue(t, out result))
		{
			lock (TableNames)
			{
				if (!TableNames.TryGetValue(t, out result))
				{
					
					string entityName = t.Name;

					ReadOnlyCollection<EntityContainerMapping> storageMetadata = context.MetadataWorkspace.GetItems<EntityContainerMapping>(DataSpace.CSSpace);

					foreach (EntityContainerMapping ecm in storageMetadata)
					{
						EntitySet entitySet;
						if (ecm.StoreEntityContainer.TryGetEntitySetByName(entityName, true, out entitySet))
						{
							if (String.IsNullOrEmpty(entitySet.Schema))
							{
								result = entitySet.Table;
								break;
							}


							//we must recognize if we are under SQL Server Compact version, which does not support multiple schemas
							//SQL server compact does not support schemas, entity framework sets entitySet.Schema set to "dbo", anyway
							//the System.Data.Entity.Infrastructure.TableExistenceChecker.GetTableName() returns only table name
							//schema is (not) added by the overrides of the method AnyModelTableExistsInDatabase
							//the SqlCeTableExistenceChecker has the knowledge that there is no metadata schema needed
							//the SqlTableExistenceChecker has the knowledge that there is metadata with schema, which should be added to the table names

							var entityConnection = (System.Data.Entity.Core.EntityClient.EntityConnection) context.Connection;

							DbConnection storeConnection = entityConnection.StoreConnection;

							if (storeConnection != null && "SqlCeConnection".Equals(storeConnection.GetType().Name, StringComparison.OrdinalIgnoreCase))
							{
								result = entitySet.Table;
								break;
							}

							result = entitySet.Schema  + "." + entitySet.Table;
							break;
						}
					}

					TableNames.Add(t,result);
				}
			}
		}

		return result;
	}

Using EF Core with SQL server it's as easy as:

_context.Model.FindEntityType(YOUR_VAR_TYPE).SqlServer().TableName

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

You can try MappingAPI extension: https://efmappingapi.codeplex.com/

It is really easy to use

context.Db<YourEntityType>().TableName

In [tag:entity-framework-core] versions 3.0 and 3.1 it's rather trivial:

var myClassTableName = _context.Model.FindEntityType(typeof(MyClass)).GetTableName();

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.");
    } 
}

Using EF5 and a litle bit o reflection, something like the following should do the trick:

using System;
using System.Collections;
using System.Data.Entity.Infrastructure;
using System.Data.Metadata.Edm;
using System.Linq;
using System.Reflection;

namespace EFHelpers {
    public class EFMetadataMappingHelper {
        public static string GetTableName(MetadataWorkspace metadata, DbEntityEntry entry) {
            var entityType = entry.Entity.GetType();

            var objectType = getObjectType(metadata, entityType);
            var conceptualSet = getConceptualSet(metadata, objectType);
            var storeSet = getStoreSet(metadata, conceptualSet);
            var tableName = findTableName(storeSet);

            return tableName;
        }

        private static EntitySet getStoreSet(MetadataWorkspace metadata, EntitySetBase entitySet) {
            var csSpace = metadata.GetItems(DataSpace.CSSpace).Single();
            var flags = BindingFlags.NonPublic | BindingFlags.Instance;
            var entitySetMaps = (ICollection)csSpace.GetType().GetProperty("EntitySetMaps", flags).GetValue(csSpace, null);
        
            object mapping = null;

            foreach (var map in entitySetMaps) {
                var set = map.GetType().GetProperty("Set", flags).GetValue(map, null);
                if (entitySet == set) {
                    mapping = map;
                    break;
                }
            }

            var m_typeMappings = ((ICollection)mapping.GetType().BaseType.GetField("m_typeMappings", flags).GetValue(mapping)).OfType<object>().Single();
            var m_fragments = ((ICollection)m_typeMappings.GetType().BaseType.GetField("m_fragments", flags).GetValue(m_typeMappings)).OfType<object>().Single();
            var storeSet = (EntitySet) m_fragments.GetType().GetProperty("TableSet", flags).GetValue(m_fragments, null);

            return storeSet;
        }

        private static string findTableName(EntitySet storeSet) {
            string tableName = null;

            MetadataProperty tableProperty;

            storeSet.MetadataProperties.TryGetValue("Table", true, out tableProperty);
            if (tableProperty == null || tableProperty.Value == null)
                storeSet.MetadataProperties.TryGetValue("http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator:Table", true, out tableProperty);

            if (tableProperty != null)
                tableName = tableProperty.Value as string;

            if (tableName == null)
                tableName = storeSet.Name;

            return tableName;
        }

        private static EntityType getObjectType(MetadataWorkspace metadata, Type entityType) {                
            var objectItemCollection = (ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace);

            var edmEntityType = metadata
                .GetItems<EntityType>(DataSpace.OSpace)
                .First(e => objectItemCollection.GetClrType(e) == entityType);

            return edmEntityType;
        }

        private static EntitySetBase getConceptualSet(MetadataWorkspace metadata, EntityType entityType) {
            var entitySetBase = metadata
                .GetItems<EntityContainer>(DataSpace.CSpace)
                .SelectMany(a => a.BaseEntitySets)
                .Where(s => s.ElementType.Name == entityType.Name)
                .FirstOrDefault();

            return entitySetBase;
        }
    }
}

Call it is like this:

public string GetTableName(DbContext db, DbEntityEntry entry) {
    var metadata = ((IObjectContextAdapter)db).ObjectContext.MetadataWorkspace;
    return EFMetadataMappingHelper.GetTableName(metadata, entry);
}

Actually I've been through the same problem, and I've produced an abstract code snippet which gives you two Dictionary<string,List<string>>($table_name,$columns_name_list). First one has database Table + column name list, second one has local EF Entities + properties

Of course you can add more checks against data type, btw imho that would force you to write insanely complicated code.

P&L

P.S. Sorry for the compressed style, I'm a lambda fanatic

using (EFModelContext efmc = new EFModelContext("appConfigConnectionName"))
{
    string schemaName = "dbo";
    string sql = @"select o.name + '.' + c.name
               from sys.all_objects o 
               	inner join sys.schemas s on s.schema_id = o.schema_id
               	inner join sys.all_columns c on c.object_id = o.object_id
               where Rtrim(Ltrim(o.type)) in ('U') and s.name = @p0";

    Dictionary<string, List<string>> dbTableColumns = new Dictionary<string, List<string>>();

    efmc.Database.SqlQuery<string>(sql, schemaName).Select(tc =>
    {
        string[] splitted = System.Text.RegularExpressions.Regex.Split(tc, "[.]");
        return new { TableName = splitted[0], ColumnName = splitted[1] };
    }).GroupBy(k => k.TableName, k => k.ColumnName).ToList().ForEach(ig => dbTableColumns.Add(ig.Key, ig.ToList()));

    Dictionary<string, List<string>> efTableColumns = new Dictionary<string, List<string>>();

    efTableColumns = ((IObjectContextAdapter)uc).ObjectContext.MetadataWorkspace
				 .GetItems(DataSpace.SSpace).OfType<EntityType>()
				 .ToDictionary( eft => eft.MetadataProperties
									 .First(mp => mp.Name == "TableName").Value.ToString(),
								eft => eft.Properties.Select(p => p.Name).ToList());
}

In the case of using Entity Framework Core 2.0+, you can easily get the relational metadata using provided APIs.

foreach (var entityType in dbContext.Model.GetEntityTypes())
{
    var tableName = entityType.Relational().TableName;
    foreach (var propertyType in entityType.GetProperties())
    {
        var columnName = propertyType.Relational().ColumnName;
    }
}

You need to have Microsoft.EntityFrameworkCore.Relational Nuget package installed to be able to use Relational() method.

If you want just a quick and dirty way to get the table name (and you don't need it in your application's code), you can look at the XML generated for your Model.edmx. Find your entity in the edmx:Mappings section and the line: MappingFragment StoreEntitySet="YourTableName" will give you the table's actual name.

Alex is right - this is a sad limitation in the Metadata API. I have to just load the MSL as an XML document and do lookups of S-space entities as I process my C-space model.

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.

Copying my answer to another question here.

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

You can use extension method to get Table name from your entity class
In this case, You wont need DbContext to get table name , it gets it inside extension method

public static string GetTableName<T>(this T entity) where T : class
{
    var object_context = GetObjectContext(entity);

    if (object_context == null || object_context.TransactionHandler == null)
        return null;

     var dbcontext=object_context.TransactionHandler.DbContext;
    var query= dbcontext.Set(entity.GetType()).ToString();
    var reg = new Regex(@"FROM \[dbo\]\.\[(?<table>.*)\] AS");
    var match = reg.Match(query);

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

private static ObjectContext GetObjectContext(object entity)
{
    var field = entity.GetType().GetField("_entityWrapper");

    if (field == null)
        return null;

    var val= field.GetValue(entity);
    var property = val.GetType().GetProperty("Context");
    var context = (ObjectContext)property.GetValue(val, null);

    return context;
}

This is where we reach to DbContext:

var object_context = GetObjectContext(entity)

Usage:

var tableName = entity.GetTableName();