How to get the Schema name of a Table in Entity Framework?

Question

I use a User-Schema Separation technology to separate tables in the Database. Could you tell me how to get Schema names of EntitySets (Tables) in Entity Framework? Thanks.

Answer 1

This must be an old topic by now, but for those still lurking with EF6+ (as in not EF core), based on the very same excellent blog post of Rowan Miller from back in the days, check my approach for surfacing some meta information about a given entity

public class DbTableMeta
{
    public string Schema { get; set; }

    public string Name { get; set; }

    public IEnumerable<string> Keys { get; set; }
}


public static DbTableMeta Meta(this DbContext context, Type type)
{
    var metadata = ((IObjectContextAdapter) context).ObjectContext.MetadataWorkspace;

    // Get the part of the model that contains info about the actual CLR types
    var items = (ObjectItemCollection) metadata.GetItemCollection(DataSpace.OSpace);

    // Get the entity type from the model that maps to the CLR type
    var entityType = metadata
        .GetItems<EntityType>(DataSpace.OSpace)
        .Single(p => items.GetClrType(p) == type);

    // Get the entity set that uses this entity type
    var entitySet = metadata
        .GetItems<EntityContainer>(DataSpace.CSpace)
        .Single()
        .EntitySets
        .Single(p => p.ElementType.Name == entityType.Name);

    // Find the mapping between conceptual and storage model for this entity set
    var mapping = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace)
        .Single()
        .EntitySetMappings
        .Single(p => p.EntitySet == entitySet);

    // Find the storage entity set (table) that the entity is mapped
    var table = mapping
        .EntityTypeMappings.Single()
        .Fragments.Single()
        .StoreEntitySet;

    return new DbTableMeta
    {
        Schema = (string) table.MetadataProperties["Schema"].Value ?? table.Schema,
        Name = (string) table.MetadataProperties["Table"].Value ?? table.Name,
        Keys = entityType.KeyMembers.Select(p => p.Name),
    };
}

Answer 2

I found the following blog entry that explains how to get at that information from the metadataworkspace:

https://romiller.com/2014/04/08/ef6-1-mapping-between-types-tables/

Here's the function that does the magic:

public static string GetTableName(Type type, DbContext context)
{
    var metadata = ((IObjectContextAdapter)context).ObjectContext.MetadataWorkspace;

    // Get the part of the model that contains info about the actual CLR types
    var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace));

    // Get the entity type from the model that maps to the CLR type
    var entityType = metadata
            .GetItems<EntityType>(DataSpace.OSpace)
            .Single(e => objectItemCollection.GetClrType(e) == type);

    // Get the entity set that uses this entity type
    var entitySet = metadata
        .GetItems<EntityContainer>(DataSpace.CSpace)
        .Single()
        .EntitySets
        .Single(s => s.ElementType.Name == entityType.Name);

    // Find the mapping between conceptual and storage model for this entity set
    var mapping = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace)
            .Single()
            .EntitySetMappings
            .Single(s => s.EntitySet == entitySet);

    // Find the storage entity set (table) that the entity is mapped
    var table = mapping
        .EntityTypeMappings.Single()
        .Fragments.Single()
        .StoreEntitySet;

    // Return the table name from the storage entity set
    return (string)table.MetadataProperties["Table"].Value ?? table.Name;
}

Answer 3

Extension methods for DbContext and ObjectContext:

public static class ContextExtensions
{
    public static string GetTableName<T>(this DbContext context) where T : class
    {
        ObjectContext objectContext = ((IObjectContextAdapter) context).ObjectContext;

        return objectContext.GetTableName<T>();
    }

    public static string GetTableName<T>(this ObjectContext context) where T : class
    {
        string sql = context.CreateObjectSet<T>().ToTraceString();
        Regex regex = new Regex("FROM (?<table>.*) AS");
        Match match = regex.Match(sql);

        string table = match.Groups["table"].Value;
        return table;
    }
}

Using a ObjectContext object:

ObjectContext context = ....;
string table = context.GetTableName<Foo>();

Using a DbContext object:

DbContext context = ....;
string table = context.GetTableName<Foo>();

More info here:

Entity Framework: Get mapped table name from an entity

Answer 4

For those using code-first, the schema name is set in the OnModelCreating override of your context;

public static readonly string DefaultSchemaName = "Entities";

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema(DefaultSchemaName);

    ...

So you should be able to reference DefaultSchemaName from elsewhere in your code.

This answer is slightly less obvious if you didn't build the model context and EF stuff out in the first place.

Answer 5

Sample used for truncating tables where both table name and schema are required.

public void ClearTable<T>() where  T: class
    {
        var dbContext = (DbContext) _dbContext;
        
        var annotations = dbContext.Model.FindEntityType(typeof(T)).GetAnnotations().ToList();

        var tableName = annotations.First(c => c.Name == "Relational:TableName").Value.ToString();
        var schema = annotations.First(c => c.Name == "Relational:Schema").Value.ToString();

        dbContext.Database.ExecuteSqlRaw($"truncate table {schema}.{tableName}");

    }

Answer 6

Using Entity Framework 6.1.3 you can query the scheme and the tablename in the following way:

  string tableNameWithScheme = context.Db<T>().Schema+"."+context.Db<T>().TableName;

Where T is the type of your Entity and context an instance of your derived System.Data.Entity.DBContext.