EF Code First Mapping Between Types & Tables


Starting in EF6.1 there is an easier way to do this.

See my new EF6.1 Mapping Between Types & Tables post for details.


 

Entity Framework includes the MetadataWorkspace which gives you access to the metadata EF keeps about the shape of your model. The problem is that the Mapping section of this – the bit that maps entities types to tables and properties to columns – isn’t public.

We do have a work item to make this public in one of the upcoming releases, but in EF6 and earlier releases it is still internal. Why, you ask, is it internal… because the metadata APIs in EF are a mess. The metadata APIs that are already public are bad… but the mapping one is worse. Whilst we can’t justify spending the time to rewrite the whole API – we do want to clean it up a bit before making it public.

There is, however, a way to get at the information – albeit extremely hacky. For Code First you can get the metadata written out to the EDMX format (that’s the xml format the designer uses). From there, we can use LINQ to XML to get the mapping info.

 

What about the EF Designer

The code in this post works for Code First, however it’s using the same xml format that the EF Designer uses, so you could easily adapt it to use the EDMX file created by the designer.

 

The object model

We’re going to write a helper library to return mapping information in an easy to consume format. We’ll use the following object model to represent the metadata.

using System;
using System.Collections.Generic;
using System.Reflection;

namespace MappingDemo
{
    /// <summary>
    /// Represents the mapping of an entitiy type to one or mode tables in the database
    ///
    /// A single entity can be mapped to more than one table when 'Entity Splitting' is used
    /// Entity Splitting involves mapping different properties from the same type to different tables
    /// See http://msdn.com/data/jj591617#2.7 for more details
    /// </summary>
    public class TypeMapping
    {
        /// <summary>
        /// The type of the entity from the model
        /// </summary>
        public Type EntityType { get; set; }

        /// <summary>
        /// The table(s) that the entity is mapped to
        /// </summary>
        public List<TableMapping> TableMappings { get; set; }
    }

    /// <summary>
    /// Represents the mapping of an entity to a table in the database
    /// </summary>
    public class TableMapping
    {
        /// <summary>
        /// The name of the table the entity is mapped to
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// Details of the property-to-column mapping
        /// </summary>
        public List<PropertyMapping> PropertyMappings { get; set; }
    }

    /// <summary>
    /// Represents the mapping of a property to a column in the database
    /// </summary>
    public class PropertyMapping
    {
        /// <summary>
        /// The property from the entity type
        /// </summary>
        public PropertyInfo Property { get; set; }

        /// <summary>
        /// The column that property is mapped to
        /// </summary>
        public string ColumnName { get; set; }
    }
}

 

Populating the object model

Now that we have an object model we can write some hacky  less-than-intuitive interesting code to populate it.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.IO;
using System.Linq;
using System.Xml;
using System.Xml.Linq;

namespace MappingDemo
{
    /// <summary>
    /// Represents that mapping between entity types and tables in an EF model
    /// </summary>
    public class EfMapping
    {
        /// <summary>
        /// Mapping information for each entity type in the model
        /// </summary>
        public List<TypeMapping> TypeMappings { get; set; }

        /// <summary>
        /// Initializes an instance of the EfMapping class
        /// </summary>
        /// <param name="db">The context to get the mapping from</param>
        public EfMapping(DbContext db)
        {
            this.TypeMappings = new List<TypeMapping>();

            var metadata = ((IObjectContextAdapter)db).ObjectContext.MetadataWorkspace;

            // Conceptual part of the model has info about the shape of our entity classes
            var conceptualContainer = metadata.GetItems<EntityContainer>(DataSpace.CSpace).Single();

            // Storage part of the model has info about the shape of our tables
            var storeContainer = metadata.GetItems<EntityContainer>(DataSpace.SSpace).Single();

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

            // Mapping part of model is not public, so we need to write to xml and use 'LINQ to XML'
            var edmx = GetEdmx(db);

            // Loop thru each entity type in the model
            foreach (var set in conceptualContainer.BaseEntitySets.OfType<EntitySet>())
            {
                var typeMapping = new TypeMapping
                {
                    TableMappings = new List<TableMapping>()
                };
                this.TypeMappings.Add(typeMapping);

                // Get the CLR type of the entity
                typeMapping.EntityType = metadata
                    .GetItems<EntityType>(DataSpace.OSpace)
                    .Select(e => objectItemCollection.GetClrType(e))
                    .Single(e => e.FullName == set.ElementType.FullName);

                // Get the mapping fragments for this type
                // (types may have mutliple fragments if 'Entity Splitting' is used)
                var mappingFragments = edmx
                    .Descendants()
                    .Single(e =>
                        e.Name.LocalName == "EntityTypeMapping"
                        && e.Attribute("TypeName").Value == set.ElementType.FullName)
                    .Descendants()
                    .Where(e => e.Name.LocalName == "MappingFragment");

                foreach (var mapping in mappingFragments)
                {
                    var tableMapping = new TableMapping
                    {
                        PropertyMappings = new List<PropertyMapping>()
                    };
                    typeMapping.TableMappings.Add(tableMapping);

                    // Find the table that this fragment maps to
                    var storeset = mapping.Attribute("StoreEntitySet").Value;
                    tableMapping.TableName = (string)storeContainer
                        .BaseEntitySets.OfType<EntitySet>()
                        .Single(s => s.Name == storeset)
                        .MetadataProperties["Table"].Value;

                    // Find the property-to-column mappings
                    var propertyMappings = mapping
                        .Descendants()
                        .Where(e => e.Name.LocalName == "ScalarProperty");

                    foreach (var propertyMapping in propertyMappings)
                    {
                        // Find the property and column being mapped
                        var propertyName = propertyMapping.Attribute("Name").Value;
                        var columnName = propertyMapping.Attribute("ColumnName").Value;

                        tableMapping.PropertyMappings.Add(new PropertyMapping
                        {
                            Property = typeMapping.EntityType.GetProperty(propertyName),
                            ColumnName = columnName
                        });
                    }
                }
            }
        }

        private static XDocument GetEdmx(DbContext db)
        {
            XDocument doc;
            using (var memoryStream = new MemoryStream())
            {
                using (var xmlWriter = XmlWriter.Create(
                    memoryStream, new XmlWriterSettings
                    {
                        Indent = true
                    }))
                {
                    EdmxWriter.WriteEdmx(db, xmlWriter);
                }

                memoryStream.Position = 0;

                doc = XDocument.Load(memoryStream);
            }
            return doc;
        }
    }
}

 

Testing it out

Now lets test out our code by running a Code First model through it. You’ll notice I’ve included Entity Splitting to demonstrate why we need to have a List of tables that an entity maps to.

using System;
using System.Collections.Generic;
using System.Data.Entity;

namespace MappingDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<BloggingContext>());

            using (var db = new BloggingContext())
            {
                var mappingInfo = new EfMapping(db);
                foreach (var item in mappingInfo.TypeMappings)
                {
                    Console.WriteLine(item.EntityType.FullName);
                    foreach (var table in item.TableMappings)
                    {
                        Console.WriteLine(" => {0}", table.TableName);
                        foreach (var column in table.PropertyMappings)
                        {
                            Console.WriteLine(
                                "        {0} => {1}",
                                column.Property.Name,
                                column.ColumnName);
                        }
                    }
                }
            }
        }
    }

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Rename a column so that not all property/column names match
            modelBuilder.Entity<Post>()
                .Property(p => p.PostId)
                .HasColumnName("post_id");

            // Perform 'Entity Splitting' on the Blog entity to test
            // mapping a single entity to multiple tables
            modelBuilder.Entity<Blog>()
                .Map(m =>
                    {
                        m.Properties(b => new { b.Name, b.Url });
                        m.ToTable("Blog_Details");
                    })
                .Map(m =>
                {
                    m.Properties(b => new { b.Image });
                    m.ToTable("Blog_Photo");
                });
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
        public string Url { get; set; }
        public byte[] Image { get; set; }

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

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

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

 

Running our application results in the following output.

Screen

Advertisements