A while back I blogged about how to find what table(s) a given entity is mapped to. The solution in that post worked around the fact that the API for accessing this information was internal. In EF6.1 we made the mapping API public, so it’s now a lot easier.

The other advantage of this code is that it will work for Code First and EF Designer models.

 

The Code

Without further ado, here is the code to find the table name of a given CLR type. I’ve included the complete listing of a Console app that demonstrated the code in action, but you can just grab the GetTableName method if that’s all you need.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Mapping;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Linq;

namespace MappingDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BloggingContext())
            {
                Console.WriteLine("Blog maps to: {0}", GetTableName(typeof(Blog), db));
                Console.WriteLine("Post maps to: {0}",  GetTableName(typeof(Post), db));
            }
        }

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

    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>().ToTable("t_blog");
            modelBuilder.Entity<Post>().ToTable("t_post");
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        public List<Post> Posts { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Body { get; set; }

        public int BlogId { get; set; }
        public Blog Blog { get; set; }
    }
}

 

A Tweak for Advanced Mappings

EF supports an advanced mapping pattern called ‘Entity Splitting’. In this pattern you can split the properties of an entity between multiple tables. Here is an example of a Fluent API call that entity splits the Post class.

modelBuilder.Entity<Post>()
    .Map(m =>
    {
        m.Properties(p => new { p.PostId, p.Title, p.BlogId });
        m.ToTable("t_post");
    })
    .Map(m =>
    {
        m.Properties(p => new { p.PostId, p.Body });
        m.ToTable("t_post_body");
    });

To handle this we can update the GetTableName method to return an enumerable of the tables that the type maps to. The only changes to the previous implementation are the last two code blocks that find the table name from the mapping fragment.

public static IEnumerable<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 sets (tables) that the entity is mapped
    var tables = mapping
        .EntityTypeMappings.Single()
        .Fragments;

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

 

Other variations

Some folks have taken the code from this post and extended it to accommodate other mapping scenarios too:

• Mapping between properties and columns – Benjamin Marty
• Mapping with inheritance in the model – Brian Lowry