Entity Framework - Get List of Tables

Question

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?

=========

UPDATE:

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.

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.

Answer 1

This sample code from post What Tables Are In My EF Model? And My Database?

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

Answer 2

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

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

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

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

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.

Answer 3

Just in case this helps, I pulled these from one of my ObjectContextExtension classes.

You can query your object context and get the names as below. Feel free to modify this however you want.

public static class ObjectContextExtensions
{
    public static string GetEntitySetName<T>(this ObjectContext theContext, T eo) where T : EntityObject
    {
        string entitySetName = "";
        if (eo.EntityKey != null)
        {
            entitySetName = eo.EntityKey.EntitySetName;
        }
        else
        {
            string className = typeof(T).Name;
            var container =
                   theContext.MetadataWorkspace.GetEntityContainer(theContext.DefaultContainerName, DataSpace.CSpace);
            entitySetName = (from meta in container.BaseEntitySets
                             where meta.ElementType.Name == className
                             select meta.Name
                            ).First();

        }

        return entitySetName;
    }
    public static IEnumerable<EntitySetBase> GetEntitySets(this ObjectContext theContext) 
    {
            var container =
                   theContext.MetadataWorkspace
                      .GetEntityContainer(
                            theContext.DefaultContainerName,
                            DataSpace.CSpace);

            return container.BaseEntitySets;
    }
    public static IEnumerable<ObjectQuery> GetObjectQueries(this ObjectContext theContext) 
    {
        IEnumerable<ObjectQuery> queries =
              from pd in theContext
                           .GetType()
                           .GetProperties()
              where pd.PropertyType
                       .IsSubclassOf(typeof(ObjectQuery))
              select (ObjectQuery)pd.GetValue(theContext, null);
        return queries;
    }
}

---

When you use it:

IEnumerable<EntitySetBase> lookAtMe = context.GetEntitySets();
//ElementType (type of entity the set represents)
//Entity Set Name
//Other fun goodies ;)

//Example of how to get the entity set to query on it.
File f = new File();
//or some entity you selected.
f = context.Files.FirstOrDefault();
string name = context.GetEntitySetName(f);

The other one I left out was GetObjectQueries and it just returns all of the ObjectQueries, which are the things in your context that you query on. context.SomeTable or context.Products.

I am not sure what you are doing, so there might be a better way to do this....once you update your end goal I will edit accordingly.