In Entity Framework, Table Per Hierarchy (TPT) mapping inheritance is a way to express closely related entities stored in a single table. We might store blue collar workers, middle managers and sales persons in a single Employee table, but represent them in our model as ‘distinct’ entities.

In this example we have our Music model with an Artist table filled with all kinds of bands, performers, singer-songwriters, studio projects and so on. We need to query for the singer-songwriters type every so often by using the IsSingerSongwriter bit.

With Entity Framework we can inherit from Artist to create a special purpose SingerSongwriter entity so we don’t need to specify the IsSingerSongwriter property every time. We do this by creating a new entity SingerSongwriter, with a base type Artist.

We do the same for the rest of the Artist rows and assume if IsSingerSongwriter is false that these are all Bands. The SingerSongwriter and Band entities inherit all properties from Artist.

Edit the properties of the Artist entity and set the Abstract property to true.

We need to set mapping conditions for both derived entities.

And as a last step we need to remove the IsSingerSongwriter property from our Artist base class (since we can’t map a property twice).

To demonstrate the use of the Artist base entity we have a (rather ugly and dependent) test that will pass only when both Ane Brun (a great singer-songwriter) and Genesis (the greatest pop band ever when Peter Gabriel was still a member) are returned. We also wrote a test where we query only the SingerSongwriter entity. This test will pass only when Ane Brun is present and Genesis is not.

using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Diagnostics;
using System.Linq;
using Entities;
using Microsoft.VisualStudio.TestTools.UnitTesting;
 
namespace MusicEntitiesTests
{
    [TestClass]
    public class ArtistTest
    {
        [TestMethod]
        public void SelectFromArtistReturnsAllArtistTypes()
        {
            using (var context = new MusicModelContainer())
            {
                ObjectSet<Artist> query = context.Artists;
                List<Artist> artistDump = query.ToList();
 
                WriteSqlToDebug(query);
                WriteArtistsToDebug(artistDump);
 
                // Has singer-songwriters
                Assert.IsTrue(artistDump.Any(a => a.Name.Equals("Ane Brun")));
                // Has bands
                Assert.IsTrue(artistDump.Any(a => a.Name.Equals("Genesis")));
            }
        }
 
        [TestMethod]
        public void SelectFromSingerSongwriterReturnsThisSingerSongwriterOnly()
        {
            using (var context = new MusicModelContainer())
            {
                ObjectQuery<SingerSongwriter> query =
                    context.Artists.OfType<SingerSongwriter>();
                List<SingerSongwriter> artistDump = query.ToList();
 
                WriteSqlToDebug(query);
                WriteArtistsToDebug(artistDump);
 
                // Has singer-songwriters
                Assert.IsTrue(artistDump.Any(a => a.Name.Equals("Ane Brun")));
                // Has no bands
                Assert.IsFalse(artistDump.Any(a => a.Name.Equals("Genesis")));
            }
        }
 
        private static void WriteSqlToDebug(object query)
        {
            Debug.WriteLine(String.Empty);
            Debug.WriteLine(((ObjectQuery) query).ToTraceString());
            Debug.WriteLine(String.Empty);
        }
 
        private static void WriteArtistsToDebug(object artistDump)
        {
            foreach (Artist artist in (IEnumerable<Artist>) artistDump)
            {
                Debug.WriteLine(artist.Name);
            }
        }
    }
}

Both tests pass. The following debug output is for the Artist query. Entity Framework doesn’t filter the result and returns all artists.

The debug output shown below is for the SingerSongwriter test. Entity Framework automatically filtered out the singer-songwriters for us. Even if we create a new SingerSongwriter entity and attach it to the datacontext it will automatically set the IsSingerSongwriter bit to true on the Artist table when inserted into the database.