实体框架 - 获取表格列表 [英] 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?

=========

=========

更新：

在上下文中，我已经包含了所有的DB表，但没有包含视图或SP。我们有许多表格输入信息（有id）。所以，例如，颜色或文件类型或协议类型。我想能够对可能保存类型info（File，FileType）的表进行类型（文件）查询，并使用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.

所以，我可能会查找... Business Unit（或颜色或文件），代码将关闭并搜索BusinessUnit（或颜色或文件）和BusinessUnitType（或ColorType或FileType）的上下文。如果它找到一个，它会查询它，并将返回所有的行，以便我可以看到它是否包含类型信息（稍后我会改进，只返回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（Conceptual模型）。以下是如何构建动态查询的示例代码，其中仅包含表名的一部分：

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名称，Namespace name等）获取所有必需的信息，基于方法参数，然后即时构建一个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屋！