What tables are in my EF model? And my Database?

Posted on April 20, 2012. Filed under: Entity Framework, Visual Studio | Tags: Code First, Database First, DbContext, Model First, ObjectContext, StoreItemCollection |

Today I was writing some code that needed to know what tables are present in an EF model… and I realized I’ve written this code multiple times and never remember all the little nuances of it… just in case you ever need to do the same I thought I’d turn it into a blog post Smile

Without further ado, here is the code:

using System;
using System.Data.Entity.Infrastructure;
using System.Data.Metadata.Edm;
using System.Linq;

namespace Demo
{
  class Program
  {
    static void Main(string[] args)
    {
      using (var db = new MyContext())
      {
        var metadata = ((IObjectContextAdapter)db).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);
        }
      }
    }
  }
}

The basic idea is to get the metadata that EF uses and interrogate it to find out what tables it thinks there are in the database. I use the word ‘interrogate’ because working with the metadata API often feels like you are trying to trick it into telling you what you want to know while it silently mocks you.

All the conditional code, null checks, etc. are to take care of the various different ways metadata can express exactly the same thing. Depending on the version of EF you are using and whether you are using Code First or Model/Database First you will get slight differences in which attributes are set.

The code assumes you are working with DbContext, if you’re ~~interrogating~~ working with ObjectContext you just need to replace one line of code:

var metadata = db.MetadataWorkspace;

If you are using Model/Database First and haven’t used the context to access any data in the current AppDomain you may get an InvalidOperationException stating ‘”The space ‘SSpace’ has no associated collection.”. This is because metadata is loaded only when it is needed. The easiest way to fix this is to run a quick query before trying to access the metadata.

What About The Database?

In the code I was writing I wanted to compare the tables in the model to the tables in the database. EF includes some components that allow us to generate metadata based on the database. In the above example we got a StoreItemCollection from the context, this time we can reverse engineer one from the database.

You’ll need a reference to System.Data.Entity.Design.dll. This isn’t included in Client Profile so make sure your application targets the full .NET profile.

var storeGenerator = new EntityStoreSchemaGenerator(
  "System.Data.SqlClient", 
  @"Server=.\SQLEXPRESS;Database=MyDatabase;Trusted_Connection=True;", 
  "namespace");

storeGenerator.GenerateStoreMetadata();

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