EF6.1 Mapping Between Types & Tables

Posted on April 8, 2014. Filed under: Entity Framework | Tags: , , , , , , |

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

Read Full Post | Make a Comment ( None so far )

Recently on RoMiller.com…

EF6/6.1 Level 300-400 Talk-in-a-Box

Posted on February 21, 2014. Filed under: Entity Framework, Visual Studio | Tags: , , , , , |

EF6 Level 100-200 Talk-in-a-Box

Posted on February 11, 2014. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

EF Code First Mapping Between Types & Tables

Posted on September 24, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

EF5 & EF6 on VS Toolbox (Source Code Included)

Posted on August 27, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , , , , , , , |

EF6 Suspendable Execution Strategy

Posted on August 19, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , , , , |

TechEd 2013 Talks & Source Code

Posted on June 10, 2013. Filed under: ASP.NET, Entity Framework, Visual Studio | Tags: , , , , |

How to Drop a Database from Visual Studio 2012

Posted on May 17, 2013. Filed under: Visual Studio | Tags: , , , |

Running EF T4 Code Generation Templates from Command Line

Posted on May 15, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , , |

EF6: Switching Identity On/Off with a Custom Migration Operation

Posted on April 30, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , , |

Processor named ‘T4VSHost’ could not be found for the directive named ‘CleanupBehavior’

Posted on March 21, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , , , |

EF6: Writing Your Own Code First Migration Operations

Posted on February 27, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , , |

Extending And Customizing Code First Models – Part 2 of 2

Posted on February 15, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

Extending and Customizing Code First Models – Part 1 of 2

Posted on February 5, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

EF6 Code First: Configuring Unmapped Base Types

Posted on January 29, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

EF6 Code First: Mapping All Private Properties Using Custom Conventions

Posted on January 23, 2013. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

Code First Migrations – Customizing Scaffolded Code

Posted on November 30, 2012. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

//build/ 2012 Talk Video and Source Code

Posted on November 20, 2012. Filed under: Entity Framework, Visual Studio | Tags: , , , , , |

Mapping to Private Properties with Code First

Posted on October 1, 2012. Filed under: Entity Framework, Visual Studio |

Code First Stored Procedures with Multiple Results

Posted on August 15, 2012. Filed under: Entity Framework | Tags: , , , |

    About

    Rowan works as a Program Manager for the ADO.NET Entity Framework team at Microsoft. He speaks at technical conferences and blogs at romiller.com. Rowan lives in Seattle, Washington with his wife Athalie. Prior to moving to the US he resided in the small state of Tasmania in Australia. Outside of technology Rowan's passions include snowboarding, mountain biking, horse riding, rock climbing and pretty much anything else that involves being active. The primary focus of his life, however, is to follow Jesus.

    RSS

    Subscribe Via RSS

    • Subscribe with Bloglines
    • Add your feed to Newsburst from CNET News.com
    • Subscribe in Google Reader
    • Add to My Yahoo!
    • Subscribe in NewsGator Online
    • The latest comments to all posts in RSS

    Meta

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

Join 147 other followers