实体框架 - 获取表列表 [英] Entity Framework - Get List of Tables

问题描述

就是这样.这很简单.我有一个 edmx，希望能够动态查询它的表，并且(希望如此)针对该表动态构建.这可能吗?

That's it. It's pretty simple. I've got an edmx and want to be able to dynamically query it for tables and (hopefully), dynamically build against that table. Is that possible?

==========

更新:

我在上下文中包含了所有数据库表，但没有包含视图或 SP.我们有很多输入信息的表(带有 id).例如，颜色或文件类型或协议类型.我希望能够对可能包含类型信息(文件、文件类型)的表进行类型(文件)查询，并使用 id 返回它.

I've included all the DB tables, but no views or SP's, in the context. We have lots of tables that type info (with id's). So, for example, colors or file type or protocol type. I want to be able to take a type (file) query for tables that might hold the type info (File, FileType) and return it with id.

因此，我可能会查找...业务单位(或颜色或文件)，代码将启动并搜索业务单位(或颜色或文件)和业务单位类型(或颜色类型或文件类型)的上下文.如果它找到任何一个，它将查询它并返回所有行，以便我可以查看它是否包含类型信息(稍后我将对其进行改进以仅返回 ID 和描述、缩写或名称字段以及限制行等)并且能够找到特定对象的关联 ID.

So, I may look for... Business Unit (or Color, or File) and the code would go off and search the context for BusinessUnit (or Color or File) and BusinessUnitType (or ColorType or FileType). If it finds either one, it will query it and will return all the rows so I can see if this holds type information (I'll refine it later to only return ID and Description, Abbreviation or Name fields as well as limiting rows etc) and be able to find the associated ID for a particular whatever.

推荐答案

对于如何枚举数据库中的表的第一个问题，此代码将为您获取它们，当然是那些已经导入到您的 EDM 中的这不一定是数据存储中的所有表.

For your first question on how to enumerate the tables in the database, this code will get them for you, of course the ones that has been imported to your EDM which necessarily is not all the tables in your data store.

var tableNames = context.MetadataWorkspace.GetItems(DataSpace.SSpace)
                        .Select(t => t.Name)
                        .ToList();

此代码将导致带有以下消息的 InvalidOperationException:
空间SSpace"没有关联的集合
这是因为与 CSpace 不同，SSpace (ssdl) 在需要时才会加载.并尝试使用 MetadataWorkspace 读取它们不算是需要的.在查询编译期间需要它，然后在对象具体化时又需要它.因此，为了欺骗 MetadataWorkspace 为我们加载它，我们需要在运行提供表名的主查询之前运行如下查询.

This code will cause an InvalidOperationException with this message:
The space 'SSpace' has no associated collection
And that's because unlike CSpace, SSpace (ssdl) is not loaded until it is needed. and trying to read them with the MetadataWorkspace doesn't count as being needed. It is needed during query compilation, then again at object materialization. So to trick the MetadataWorkspace to load it for us we need to run a query like below just before we run the main query that gives us table names.

string temp = ((ObjectQuery)context.[EntitySetName]).ToTraceString();

您可以从这里阅读更多信息:强制加载 MetadataWorkspace ItemCollections 的快速技巧

You can read more from here: Quick Trick for forcing MetadataWorkspace ItemCollections to load

但是，如果您的意图是针对您的类型表构建动态查询，那么您就不需要使用 SSpace，您必须从 CSpace(概念模型)中获取它.下面是一个示例代码，说明如何构建只有部分表名的动态查询:

However, if your intention is to build a dynamic query against your type tables, then you don't need to mess around with SSpace, you have to get it from the CSpace (Conceptual Model). Below is a sample code on how to build a dynamic query with having only a part of table name:

ObjectResult<DbDataRecord> GetAllTypes(string name) {
    using (TypeEntities context = new TypeEntities()) {

    MetadataWorkspace metadataWorkspace = context.MetadataWorkspace;
    EntityContainer container = metadataWorkspace.GetItems<EntityContainer>
                                                      (DataSpace.CSpace).First();
    string namespaceName = metadataWorkspace.GetItems<EntityType>
                                        (DataSpace.CSpace).First().NamespaceName;

    string setName = string.Empty;
    string entityName = name + "Type";

    EntitySetBase entitySetBase = container.BaseEntitySets
            .FirstOrDefault(set => set.ElementType.Name == entityName);

    if (entitySetBase != null) {
        setName = entitySetBase.Name;
    }
    EntityType entityType = metadataWorkspace
         .GetItem<EntityType>(namespaceName + "." + entityName, DataSpace.CSpace);

    StringBuilder stringBuilder = new StringBuilder().Append("SELECT entity ");
    stringBuilder
       .Append(" FROM " + container.Name.Trim() + "." + setName + " AS entity ");
    string eSQL = stringBuilder.ToString();

    ObjectQuery<DbDataRecord> query = context.CreateQuery(eSQL);
    ObjectResult<DbDataRecord> results = query.Execute(MergeOption.AppendOnly);
    return results;
    }
}

代码说明:我的假设是您的类型表名称以Type"结尾作为后缀(例如 ColorType)，因此您可以调用 GetAllType("Color") 并在您的模型中搜索 ColorType EntityObject 并将给你所有可能的值.代码可能看起来很吓人，但它是非常简单的东西.基本上它所做的就是根据方法参数从 MetaData(如 EntitySet 名称、命名空间名称等)中获取所有必需的信息，然后动态构建 EntitySQL 查询，然后执行它并返回结果.

Code Explanation: My assumption was that your type table names are ended in "Type" as a postfix (e.g. ColorType), so you can call GetAllType("Color") and it search for ColorType EntityObject in your model and will give you all the possible values. The code might looks scary but it's pretty simple stuff. Basically all it does is that it gets all the required information from the MetaData (like EntitySet name, Namespace name, etc...) based on the method parameter and then build up an EntitySQL query on the fly, then execute it and return the results.

这篇关于实体框架 - 获取表列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！