从实体框架元数据获取数据库表名

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.

我正试图找到一种方法来获取给定实体类型的底层SQL表名。我尝试过使用MetadataWorkspace查询，虽然我可以从对象或存储空间中获得大量信息，但我似乎无法找到如何在两者之间进行映射。

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

假设我在对象模型中有一个名为Lookup的类型——如何在数据库中找到tablename (wws_lookup) ?

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.

我可以查询CSpace和SSpace的所有EntityType对象，我可以正确地看到这两个对象，但是我不知道如何从CSpace获取SSpace。

Is there any way to do this?

有什么办法吗?

18 个解决方案

#1

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

我使用Nigel的方法(从. totracestring()提取表名)，但是进行了一些修改，因为如果表不在默认的SQL Server模式(dbo.{table-name})中，那么他的代码就不能工作。

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

我为DbContext和ObjectContext对象创建了扩展方法:

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

这里有更多细节:实体框架:从实体获取映射表名

#2

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

编辑这个答案现在已经过时了，因为EF 6.1中的新特性:表类型之间的映射。先去那里!

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

我对其他答案有疑问，因为我有一个派生类型。我让这个方法(在我的context类中)起作用——我目前在模型中只有一层继承

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

NB有改进元数据API的计划，如果没有得到我们想要的，那么我们可以先查看EF代码在类型和表之间的映射

#3

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

不，不幸的是，使用元数据api来获取给定实体的tablename是不可能的。

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.

这是因为映射元数据不是公共的，所以无法使用EF的api从C-Space到S-Space。

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

如果您确实需要这样做，您可以通过解析MSL自己构建映射。这并不适合胆小的人，但应该是有可能的，除非您正在使用QueryViews(这是非常罕见的)，在这种情况下，根本不可能实现任何目的(您必须解析ESQL……)啊!)

Alex James

亚历克斯·詹姆斯

Microsoft.

微软。

#4

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

有一种方法可以使用EF来删除数据，而不需要加载它。我在http://nigelfind外侧. blogspot.com/2010/04/howto -delete- objects-inef4 -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....

诀窍是将IQueriable转换为ObjectQuery并使用ToTraceString方法。然后编辑生成的sql字符串。它是有效的，但是您需要小心，因为您绕过了EF用于维护依赖和约束的机制。但由于性能原因我认为没关系为此....

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

#5

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

如果您正在为POCO类使用T4模板，您可以通过修改T4模板来获得它。看到片段:

<#  
////////////////////////////////////////////////////////////////////////////////
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();

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

#6

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

一种可能的变通方法(不是很好，但也不是替代方法……)

var sql = Context.EntitySetName.ToTraceString();

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

…然后解析SQL，这应该非常简单。

#7

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

下面是我使用LINQ到XML的方法。代码还获取列名的映射。

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

#8

A better way is to use the StoreItemCollection from the Metadata. This guy has already provided an example of using it: Get Tables and Relationships

更好的方法是使用元数据中的StoreItemCollection。这个家伙已经提供了一个使用它的例子:获取表和关系

#9

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

如果您在EF6中使用codefirst，您可以将以下内容添加到dbcontext类中。

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

#10

EF 6.1, code-first:

EF 6.1,优先:

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

#11

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

下面是另一种查找表名的方法。这有点奇怪，但很有效。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

#12

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

您可以尝试MappingAPI扩展:https://efmappingapi.codeplex.com/

It is really easy to use

它真的很容易使用

context.Db<YourEntityType>().TableName

#13

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

#14

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

实际上我也遇到过同样的问题，我生成了一个抽象代码片段，它给了您两个字典 >($table_name，$columns_name_list)。第一种是数据库表+列名列表，第二种是本地EF实体+属性 ,list

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

当然，您可以针对数据类型添加更多的检查，顺便说一句，imho将迫使您编写极其复杂的代码。

P&L

损益表

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

附注:对不起，我是lambda*分子

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

#15

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.

Alex是对的——这是元数据API的一个可悲的限制。我必须将MSL作为XML文档加载，并在处理c空间模型时对s空间实体进行查找。

#16

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

使用EF5和litle bit o反射，类似以下的操作应该可以做到:

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

#17

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.

如果有人还在看，这是我的做法。这是DBContext的扩展方法，它接受一个类型并返回物理列名及其属性。

This utilizes object context to get physical columns list, then uses the "PreferredName" metadata property to map each column it its property.

它利用对象上下文获取物理列列表，然后使用“PreferredName”元数据属性映射其属性的每个列。

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

#18

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

对于EF6，混合/压缩代码从这里和周围的答案(VB，对不起):

    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.

它适用于db-first。遵循.edmx文件相对容易理解。