Get a List of Tables in Entity Framework

entity-framework

Question

I'm done now. It's quite easy. I want to be able to dynamically search for tables in my edmx and (ideally) build against those tables. That seems feasible.

=========

UPDATE:

All the database tables are present in the context, but neither views nor SPs are. There are several tables containing typed information (including IDs). Colors, file types, or protocol types are a few examples. I want to be able to take a type (file) query and return it with an id for tables that may include the type information (File, FileType).

I may search for... The code would launch a search for Business Unit Type and Business Unit (or Color, or File), depending on the circumstances (or ColorType or FileType). If it locates either of them, it will search it and return all the rows so I can determine whether this contains type information (I'll later refine it to only return ID and Description, Abbreviation or Name fields as well as limiting rows, etc.) and be able to locate the associated ID for a specific whatever.

1
21
10/8/2010 7:04:30 PM

Accepted Answer

In response to your first query about how to list all of the tables in the database, this code will do so for you—but only those that have been imported into your EDM, which inevitably excludes all of the other tables in your data store.

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

With the following message, this code will result in an InvalidOperationException:
There is no connected collection with the space "SSpace."
And the reason for this is because SSpace (ssdl), in contrast to CSpace, is only loaded when necessary. and using the MetadataWorkspace to attempt to read them does not constitute as being required. Both query composition and object materialization need it. Therefore, we must execute a query similar to the one below shortly before we run the primary query that returns the table names in order to deceive the MetadataWorkspace into loading it for us.

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

Click here for additional information: Simple Method for Forcing the Loading of MetadataWorkspace Item Collections

However, you don't need to fiddle with SSpace if your goal is to create a dynamic query against your type tables; instead, you must get it from the CSpace (Conceptual Model). Here is some example code for creating a dynamic query using just a portion of the 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;
    }
}


Code Definition: My presumption was that the names of your type tables ended in "Type" as a postfix (for instance, ColorType), so you could run GetAllType("Color") to find the ColorType EntityObject in your model and get all the available values. Although the code seems frightening, it is really rather easy. Basically, all it does is gather all the necessary data from the MetaData depending on the method argument (such as EntitySet name, Namespace name, etc.), compose an EntitySQL query on the fly, run it, and then provide the results.

16
10/9/2010 2:47:26 AM

Popular Answer

This example code was found in post How Many Tables Does My EF Model Have? My Database, too?.

using (var dbContext = new YourDbContext())
{
    var metadata = ((IObjectContextAdapter)dbContext).ObjectContext.MetadataWorkspace;

    var tables = metadata.GetItemCollection(DataSpace.SSpace)
        .GetItems<EntityContainer>()
        .Single()
        .BaseEntitySets
        .OfType<EntitySet>()
        .Where(s => !s.MetadataProperties.Contains("Type")
        || s.MetadataProperties["Type"].ToString() == "Tables");

    foreach (var table in tables)
    {
        var tableName = table.MetadataProperties.Contains("Table")
            && table.MetadataProperties["Table"].Value != null
            ? table.MetadataProperties["Table"].Value.ToString()
            : table.Name;

        var tableSchema = table.MetadataProperties["Schema"].Value.ToString();

        Console.WriteLine(tableSchema + "." + tableName);
    }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow