.net 从实体框架元数据中获取数据库表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1895455/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Database Table Name from Entity Framework MetaData
提问by Rick Strahl
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 的类型 - 我如何在数据库中找到表名（wws_lookups）？
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?
有没有办法做到这一点？
回答by Rui Jarimba
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 DbContextand ObjectContextobjects:
我已经为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
此处有更多详细信息：
实体框架：从实体获取映射表名称
回答by Colin
EDITThis 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
我对其他答案有疑问，因为我有一个派生类型。我让这个方法（在我的上下文类中）起作用了 - 目前我的模型中只有一层继承
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 APIand if this isn't getting what we want then we can look at EF Code First Mapping Between Types & Tables
NB 有计划改进元数据 API，如果这没有得到我们想要的，那么我们可以查看EF Code First Mapping between Types & Tables
回答by N73k
Most of the answers here don't work with derived classes. This one does. And gives you the schema too. I combined the answers hereand 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).
这里的大多数答案不适用于派生类。这个可以。并为您提供架构。我结合了这里的答案并对其进行了一些改进（通过取出 First() 和 Single() 之类的东西并将它们转换为 Where() 和 SelectMany() 之类的东西并返回架构名称）。
This works with EF 6.1+
这适用于 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;
}
回答by Alex James
No, unfortunately it is impossible using the Metadata APIs to get to the tablename for a given entity.
不，不幸的是，使用元数据 API 无法获取给定实体的表名。
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.
这是因为 Mapping 元数据不是公开的，因此无法使用 EF 的 API 从 C-Space 转到 S-Space。
If you reallyneed 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.
微软。
回答by Nigel Findlater
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: //nigelfindlater.blogspot.com/2010/04/how-to-delete-objects-in-ef4 -没有.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));
}
回答by Shayne Boyer
If you are using the T4 template for POCO classes you can get it by altering the T4 Template. See snippet:
如果您将 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();
////////////////////////////////////////////////////////////////////////////////
回答by Eric Bynum
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;
}
回答by Craig Stuntz
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，这应该很简单。
回答by Jon Miller
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 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();
回答by King Mikey
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。这家伙已经提供了一个使用它的例子： Get Tables and Relationships
