CODEWITHSUNDEEP
c#entity-frameworkschemaentity
Luke Lee
Luke Lee

Reputation: 71

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

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.

Upvotes: 5

Views: 14202

Answers (7)

Akbar Asghari
Akbar Asghari

Reputation: 681

Approved answer is true when Type is Model.{Entity} or type.BaseType be Object but if Type be System.Data.Entity.DynamicProxies you will get exception

Sequence contains no matching element

for fix this issue just modify entityType like this

var entityType = metadata
                    .GetItems<EntityType>(DataSpace.OSpace)
                    .Single(e => objectItemCollection.GetClrType(e) == type || objectItemCollection.GetClrType(e) == type.BaseType);

Upvotes: 0

BDarley
BDarley

Reputation: 1265

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

    }

Upvotes: 0

Dan Dohotaru
Dan Dohotaru

Reputation: 3079

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

Upvotes: 4

John
John

Reputation: 7049

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

Upvotes: 2

Diversity
Diversity

Reputation: 1900

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.

Upvotes: -3

Luke Puplett
Luke Puplett

Reputation: 45115

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.

Upvotes: 1

Rui Jarimba
Rui Jarimba

Reputation: 17979

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

Upvotes: 2

Related Questions