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

我使用奈杰尔的方法(从 .ToTraceString() )但是做了一些修改,因为如果表不在默认的SQL Server模式中,他的代码就不能工作( dbo.{table-name}

我已经为 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;
    }
}

更多详情请点击此处:
Entity Framework: Get mapped table name from an entity

由于EF6.1中的新功能,这个答案现在已经过时: mapping between table types 先去那儿!

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有计划 improve the Metadata API EF Code First Mapping Between Types & Tables

不,不幸的是,使用元数据api无法获取给定实体的表名。

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

如果你 需要这样做的话,您可以通过解析MSL自己构建映射。这不是为胆小的人准备的,但它应该是可能的,除非你正在使用 QueryViews (这是非常罕见的),在这一点上,它对于所有意图和目的都是不可能的(您必须解析ESQL。。。啊!)

亚历克斯·詹姆斯

微软。

http://nigelfindlater.blogspot.com/2010/04/how-to-delete-objects-in-ef4-without.html

技巧是将IQueriable强制转换为ObjectQuery并使用ToTraceString方法。然后编辑生成的sql字符串。它可以工作,但你需要小心,因为你绕过了EF为保持依赖性和矛盾而建立的机制。但出于表演的原因,我认为这样做是可以的。。。。

玩得高兴。。。

奈杰尔。。。

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

here 并对其进行了一些改进(去掉First()和Single()之类的内容,将它们转换为Where()和SelectMany()之类的内容并返回模式名)。

这适用于EF6.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;
    }

如果您将T4模板用于POCO类,则可以通过更改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();

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

如果要在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;
    }

var sql = Context.EntitySetName.ToTraceString();

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

下面是我使用LINQ to 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();

更好的方法是使用元数据中的StoreItemCollection。这家伙已经提供了一个使用它的例子: Get Tables and Relationships

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

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

这是另一种查找表名的方法。这有点奇怪,但管用。

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

您可以尝试映射API扩展: https://efmappingapi.codeplex.com/

context.Db<YourEntityType>().TableName

在使用时为特殊情况添加另一个答案 显式地告诉EF要使用哪个表名。例如,如果您有:

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

您可以轻松访问 TableAttribute 查找表名的注释:

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

那就是 tblCompany

即用方法:

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

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

这里是一个版本,假设您有上下文,并且在内存中有一个选定的实体,您需要为其找到真正的表名。

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

实际上,我也遇到过同样的问题,我已经生成了一个抽象的代码片段,其中给出了两个 Dictionary<string,List<string>> 第一个有数据库表+列名列表,第二个有本地EF实体+属性

当然,你可以增加对数据类型的检查,顺便说一句,这会迫使你编写非常复杂的代码。

损益表

P、 对不起,我是个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());
}

如果您只想快速而肮脏地获取表名(并且在应用程序代码中不需要它),可以查看为Model.edmx生成的XML。在edmx:Mappings部分找到您的实体,行:MappingFragment StoreEntitySet=“your table name”将给出表的实际名称。

使用EF5和一个很小的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;
        }
    }
}

叫它像这样:

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

把我对另一个问题的答案抄过来。

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

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

因为它使用对象上下文,所以它启动数据库连接,因此第一次运行将取决于上下文的复杂性。

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

要使用它,只需创建一个helper静态类,并添加上面的函数;然后它就和调用

var tabCols = context.GetTableColumns(typeof(EntityType));

    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

它先为数据库工作。
在.edmx文件之后比较容易理解。

在使用EntityFrameworkCore2.0+的情况下,可以使用提供的API轻松获取关系元数据。

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

您需要安装Microsoft.EntityFrameworkCore.Relational Nuget包才能使用 方法。