Table Splitting in Entity Framework

In my previous post I covered the Entity Splitting technique and in this post I am going to talk about just the opposite of that technique.

Table Splitting:- In this scenario there are multiple entities or domain objects but the data is stored in a single table. Lets say in our database we have an employee table which stores basic information like first name, last name, DOB as well their photo. However in our domain model we want to create two different entities.

Lets write our domain objects to see this is action.

Employee.cs

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public DateTime DOB { get; set; }
    public virtual EmployeePhoto Photo { get; set; }
}

EmployeePhoto.cs

public class EmployeePhoto
{
    public int Id { get; set; }
    public byte[] Photo { get; set; }
    public Employee Employee { get; set; }
}

As you can see we have define two entities Employee and EmployeePhoto in our domain model and we have declared EmployeePhoto as virtual, with this declaration we can take advantage of lazy loading and when we query the Employee entity through EF the photo column will not be selected as part of the linq query. Second it also indicates that EmployeePhoto entity cannot exist without an Employee. It is also important that EmployeePhoto has a reference of Employee entity and has an Id properties which we will use to map the two entities into one table.

Lets wire up the relationship in our context class as shown below.

EmployeeContext.cs

public class EmployeeContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
    public DbSet<EmployeePhoto> Photos { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EmployeePhoto>().HasKey(p => p.Id);

        modelBuilder.Entity<Employee>()
            .HasRequired(e => e.Photo)
            .WithRequiredPrincipal(p => p.Employee);

        modelBuilder.Entity<EmployeePhoto>().ToTable("Employee");
        modelBuilder.Entity<Employee>().ToTable("Employee");
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

We first defined the secondary entity( Employee Photo ) that and state that it has an identity key which is identical in our main entity. Next in our Employee entity we defined that the photo property is required with the Employee as the primary entity and then using ToTable function we map both the entity to the same table i.e. Employee.

Advertisement

Entity Splitting in Entity Framework

I have done a lot of post on Entity Framework Code First which includes some simple tips and tricks to more complex scenarios of mapping relationship and here are some of those post if you want to read them.

Entity Framework Tutorial

However recently I was helping a friend of mine to understand Entity Framework modelling for a legacy system. Since the schema is already defined it becomes a bit difficult to create a rich domain model keeping the model in your design. The other thing I have seen is that people simply follow the schema and let schema dictating what your domain is, which in my opinion is not correct.

Anyways, after some discussion I realized that they are not taking advantage of Entity Splitting in Entity Framework, so I thought about blogging it.

Entity Splitting:- In this scenario there is a single entity or domain object but the data is stored in multiple tables with a one to one relationship. For example lets say the domain is a fitness/health industry and you have customer table which stores basic information like first name, last name, DOB in the customer table and other vital stats like resting heat beat,blood type,cholesterol level, blood pressure, Sugar level etc. in their health information table. However in your domain model the customer object is composed of both information.

Lets see this in action.

Customer.cs

public class Customer
{
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public DateTime DOB { get; set; }

    public int RestingHeartBeat { get; set; }

    public string BloodType { get; set; }

    public decimal Cholesterol { get; set; }

    public string BloodPressure { get; set; }
}

So this is my domain object which represents the whole customer object however in the legacy system some information is stored in the Customer table and some in the VitalStats table.

And lets write the configuration for customer in which we will split the entity into two different tables using the Map and ToTable function as shown below.

CustomerConfiguration.cs

public class CustomerConfiguration : EntityTypeConfiguration<Customer>
{
    public CustomerConfiguration()
    {
        this.Map(c =>
       {
           c.Properties(p => new
           {
               p.Id,
               p.FirstName,
               p.LastName,
               p.DOB
           });
           c.ToTable("Customer");
       })
        .Map(v =>
       {
           v.Properties(p => new
           {
               p.Id,
               p.BloodPressure,
               p.BloodType,
               p.Cholesterol,
               p.RestingHeartBeat
           });
           v.ToTable("VitalStats");
       });
    }
}

And here is my DbContext class.

public class HealthContext : DbContext
{
    DbSet<Customer> Customer { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

Refactoring with LINQ

In this post I am going to talk about a simple refactoring technique using LINQ and I hope it will give you some insight into the power of LINQ.

Recently I came across with a very specific requirement where as soon as we persist our aggregate root (Code First POCO) into the database, the entire object need to be stored in an audit table as a key value pair (property and values).

I know it may sound a bit strange but hey requirements are requirements and if it adds value to the business then why not.

I thought it would be easy as I can use JavaScript Serialzer and store it as a JSON object in SQL Server database but that was not the case as the requirement specifically said it has to be just a key value pair.

Also the JavaScript Serializer solution would not have worked as I had one-to-many and then many-to-many relations in EF Code first POCO objects and JavaScript Serializer was giving exception due to circular objects.I could have tried fixing the seriliazer problem but I didn’t pursue it too hard as this technique was not a fool-proof solution.

So I googled it and found this reflection helper class

ReflectionHelper.cs

 public static class ReflectionHelper
{
    public static string DisplayObjectProperties(Object o)
    {
        StringBuilder sb = new StringBuilder();
        System.Type type = o.GetType();
        foreach (PropertyInfo p in type.GetProperties())
        {
            if (p.CanRead)
            {
                object obj = p.GetValue(o, null);
                if (obj != null)
                {
                    sb.AppendLine(String.Concat("-Property name: ", p.Name ));
                    sb.AppendLine(String.Concat("-Property value:", obj.ToString()));
                    sb.AppendLine();
                }
                else sb.Append(String.Concat(p.Name, " # Value is null"));

            }
        }
        return sb.ToString();
    }
}

The code is pretty slick and does what it is supposed to do and worked in my scenario, no problem at all.

I then decided how can I make this code even better and leverage some of the new language feature. For me foreach loop with lots of if else conditions is a code smell and had to do something about it. So here is the refactored code using LINQ.

ReflectionHelper.cs

public static class ReflectionHelper
{
    public static string DisplayObjectProperties(this Object o)
    {
        var sb = new StringBuilder();
        var type = o.GetType();
        var query = from property in type.GetProperties()
                    where property.CanRead &&
                          property.GetValue(o, null) != null
                    select property;

        foreach (var propertyInfo in query)
        {
            sb.AppendFormat("{0}:{1}\n", propertyInfo.Name,
                                propertyInfo.GetValue(o));
        }
        return sb.ToString();
    }
}

As you can see nothing fancy but all the ifs conditions have been converted into a LINQ query,a simple technique but looks good.

Happy Clean Coding !!! 🙂

Getting Started with Entity Framework 5

I finally took the plunge and installed Visual Studio 2012 and Entity Framework 5.Before I delve into any EF 5 code a few things I want to clarify about it.

Entity Framework 5 only comes with Visual Studio 2012 and you can’t get it using Visual Studio 2010, even if you try to use Nuget package manager it will install Entity Framework 4.4 but will report that it has installed Entity Framework 5.0

So if you run the following command

PM> Install-Package EntityFramework

and look into the version property of Entity Framework assembly it will still be 4.4.0.0 and that was a bit of a gotcha for me.

I recommend you install Visual Studio 2012 Web Express if VS2012 is not available and currently there is no Visual Studio Express Edition for Windows 7 so no “Console Application” for the time being.

I think this must be some sort of marketing strategy from Microsoft to force users to upgrade to Windows 8 but in my opinion it is not a good thing. I work mainly on web technologies so it does not matter much to me but out there many people who would like to play with it and write some simple console application and they can’t.

Another thing which was sore to my eyes was the light color visual theme for visual studio it really looks ugly and I recommend change it from light to dark by doing Tools > Options > Environment > General > Visual Experience , trust me it will look and feel much nicer.

See i told it does looks nice and shiny and since I had worked with Expression blend and Sketch flow in the past I actual like this theme.So enough of the intro and lets get back to EF 5

Let’s run the Install package command from VS 2012 as EF is hard wired up with .Net Framework 4.5.

So the feature I am going to explore today is a much awaited feature and that is the support for enumerations and I will use a simple example of a Real Estate Property
as shown below.

As you can see a I have a domain object called Property which holds information of a real estate property.It also two enumeration attributes which are 1) The type of property 2) The Energy rating for a particular property.And this is how the code looks like.

Property.cs

public class Property
{
    public int Id { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public PropertyType PropertyType { get; set; }
    public EnergyRating EnergyRating { get; set; }
    public string Suburb { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }
}

PropertyType.cs

public enum PropertyType
{
    Residential,
    Commercial,
    Governmental
}

PropertyType.cs

public enum EnergyRating
{
    Poor = 0,
    Average = 1,
    Good = 2,
    Excellent = 3
}

And lets write a unit test to see whether we can create the database or not.

[TestMethod]
public void Should_be_able_to_create_RealEstateContext_database()
{
    var context = new RealEstateContext();
    context.Database.Initialize(true);
}

So far so good and when you inspect the database tables you will see the tables have int as their data type for the enum columns,lets go ahead and add some data into the table.

[TestMethod]
public void Should_be_able_to_add_properties()
{
    var property1 = new Property
    {
        AddressLine1 = "333 Baker Street",
        PropertyType = PropertyType.Residential,
        EnergyRating = EnergyRating.Excellent,
        Suburb = "Suburb1",
        State = "ACT",
        ZipCode = "2100"
    };

    var property2 = new Property
    {
        AddressLine1 = "444 Laker Street",
        PropertyType = PropertyType.Commercial,
        EnergyRating = EnergyRating.Average,
        Suburb = "Suburb2",
        State = "ACT",
        ZipCode = "2110"
    };

    var property3 = new Property
    {
        AddressLine1 = "404 Not Found Street",
        PropertyType = PropertyType.Governmental,
        EnergyRating = EnergyRating.Excellent,
        Suburb = "Suburb2",
        State = "ACT",
        ZipCode = "2120"
    };

    var context = new RealEstateContext();
    context.Property.Add(property1);
    context.Property.Add(property2);
    context.Property.Add(property3);

    context.SaveChanges();
}

And as you can see all the three new property data is added to the database.Now lets write another test to retrieve this data using some LINQ and see if it all comes through.

[TestMethod]
public void Should_be_able_filter_by_energy_rating()
{
    var context = new RealEstateContext();
    var query = from p in context.Property
                where p.EnergyRating == EnergyRating.Excellent
                select p;
    var result = query.ToList();
    Assert.IsTrue(result.Count > 0);
}

As you can see I am trying to retrieve properties which have an energy rating of excellent and this test also passes through flying green color.

Entity Framework takes care of all the mapping, storing and retrieving of enum types.It also support both specified values or just as a type enums. For example in the above 2 enums theenergy rating type had specified values and EF 5 worked it out automatically how to map this values and store them in the database.

Below is the returned data in the watch window.

EF Code First:Executing Stored Procedure

In this post I am going to show you how to use stored procedure with Entity Framework 4.1 even though it is official that Entity Framework 4.1 doesn’t support stored procedure and this was announced by the ADO.NET team at the time of the release. What it means that you can’t write stored procedure which return different data structure other than your entity wrapped in with DbSet and currently there is no way to map it a different entity which is not a database table.

Anyway without going into much details that why this feature didn’t make it to its first release, lets focus on how we can execute stored procedure which maps to a table.

I recommend you read my previous post which explains the idea behind POCOContextScript class, so we will add 2 more constants which has the DML for the stored procedures.

POCOContextScript.cs

public static class POCOContextScript
{
   public const string CreateProcGetAllProducts
      = @"create procedure [dbo].[GetAllProducts]
            as
            begin
                set nocount on;
                select * from product
            end";

   public const string CreateProcGetProductById
      = @"create procedure [dbo].[GetByProductId]
             @productid int
           as
           begin
               set nocount on;
               select * from product p
               where p.id = @productid
           end";
}

Nothing special just 2 sql procedure statement to get all products and get a specific product by id. Now we will follow the way we used in the previous post we can hook this into a class which implements the IDatabaseInitializer interface and lets run a unit test to see how we can invoke them.

GetAllProduct

[TestMethod]
public void Should_be_able_to_execute_sql()
{
   var context = new POCOContext();
   var result = context.Product.SqlQuery("GetAllProducts").ToList();
   Assert.IsTrue(result.Count > 0);
}

GetByProductId

[TestMethod]
public void Should_be_able_to_get_product_by_id()
{
   var context = new POCOContext();
   var parameter = new SqlParameter
   {
      DbType = DbType.Int32,
      ParameterName = "ProductId",
      Value = 1
   };

   var result = context.Product.SqlQuery
                                ("GetByProductId @ProductId",
                                   parameter
                                ).FirstOrDefault();

   Assert.AreEqual(1, result.Id);
}

That’s it for now and I hope the ADO.Net team is working on this feature for the next release and along with bulk copy and other features the next version will be even better.

EF Code First:Running SQL scripts

In this post I am going to show you how to run SQL scripts in Entity Framework 4.1 as we have seen that it’s quite easy to define our database entities using the DBSet and all the relationship using the EntityTypeConfiguration class.

The best part of this whole infrastructure classes are that they have pretty simple and clean interface which promotes writing “Clean Code” and have a nice “Single Responsibility Principle” and “Separation of concern”.

In order to run script as part of your database initialization you have to simply implement the IDatabaseInitializer interface and wire it up with your database context object.For example after your database tables are initialized and relationship set you want to create an unclustered index on a particular table and I will show you how we use the above interface to do the job.

Since we are dealing with scripts it means we will have to either write them as string literal or add them as SQL files to your project and somewhere in your project start-up read the script files. I will use the string literal approach for illustration and feel free to choose what ever approach works better for you.

In order to minimize the “Magic String” problem I am going to wrap it inside a static class as constant so that it is easy to call,maintain and unit test.
So this is how the code looks like.

POCOContextScript.cs

public static class POCOContextScript
{
   public const string CreateUnClusteredIndexOnProduct =
   @"if  exists (select * from sys.indexes
    where object_id = object_id('[dbo].[product]')
    and name = 'product_price_un_clustered_index')
    drop index [product_price_un_clustered_index] on
   [dbo].[product] with ( online = off )
    go
   create nonclustered index [product_price_un_clustered_index]
    on [dbo].[product]
    (
       [price] asc
    )
    with (pad_index  = off, statistics_norecompute  = off,
   sort_in_tempdb = off,ignore_dup_key = off, drop_existing = off,
   online = off, allow_row_locks  = on,allow_page_locks  = on)
   on [primary]
   go";
}

and now we write our DbInitializer class which will implment the IDatabaseInitializer interface.

DbInitializer.cs

public class DbInitializer : IDatabaseInitializer<POCOContext>
{
   public void InitializeDatabase(POCOContext context)
   {
      context.Database.SqlCommand (
      POCOContextScript.CreateUnClusteredIndexOnProduct
       );
   }
}

 

Nothing special here just calling the SqlCommand method of the Database object and since we wrapped our SQL inside our static class we can simply refer to the constant “CreateUnClusteredIndexOnProduct” as if it’s a property of POCOContext class.

Lets write a unit test to see how we are going to invoke and wire up rest of the stuff.

UnitTest.cs

[TestMethod]
public void Should_be_able_to_initialize_database()
{
   var dbInitializer = new DbInitializer();
   DbDatabase.SetInitializer(dbInitializer);
   var target = new POCOContext();
   target.Database.Initialize(true);
}

As you can I am creating a new instance of our DbInitializer class and passing that object to SetInitializer method of DbDatabase object provided by Entity Framework 4.1 and when I call the initialize method of my POCOContext instance the script gets executed and the unclustered index gets created.

In a large scale real life project you probably will have many configuration class and database initializer class and the above could should be wired up with a BootStrapper class and should use an IoC.

EF Code First: Many to Many Self referencing relationship

I have been doing a series of post about Entity Framework Code First and in one of the post I explained various types of relationships.One particular section was about Many to Many relationship and in today’s post I am going to show you how to map a self referencing many to many relationships as it’s a very interesting scenario.

Generally I start with a class diagram and some code but since it’s about an entity referencing to itself, there is not much one can explain through a class diagram. Instead I’ll try to explain it from a database diagram point of view.

Suppose we are creating a domain model for an article which has a title and published date and with that article we want to associate “Related articles” and “Other Related articles”. To keep things simple I will not delve into domain specific rules or algorithms on how an article gets associated as a “related article” and for “Other related article”.

The idea is that when a reader reads an article he gets a list of related article which are “tightly” related to the article and list of “other related article” which are “loosely” related. But as you can see an article can have many “related” and “other related” articles but they all are of the same type i.e. article.

Hmmm at this point we can see how we are building up this conceptual design of article, related articles and other articles and thinking in terms of collection of objects.

Lets write some code …

Article.cs

public class Article
{
   public int Id { get; set; }
   public string Title { get; set; }
   public DateTime PublishedDate { get; set; }

   public virtual ICollection<Article> RelatedArticles { get; set; }
   public virtual ICollection<Article> OtherRelatedArticles { get; set; }

   public Article()
   {
      RelatedArticles = new HashSet<Article>();
      OtherRelatedArticles = new HashSet<Article>();
   }
}

ArticleContent.cs

public class ArticleContent
{
   public int Id { get; set; }
   public virtual Article Article { get; set; }
   public string Content { get; set; }
}

As you can see in Article.cs I have declared collection of article entity and named them RelatedArticles and OtherRelatedArticles. Also I’m thinking from a performance point of view and have separated the content field to another table as I’ll be Lazy loading these entities when building up the object hierarchies.

This way I’ll load the article entity and it’s many related entities but not loading the content field as this will be a huge text field and can impact my lazy loading.

Lets write the configuration class which will map all this together.

ArticleConfiguration.cs

public class ArticleConfiguration : EntityTypeConfiguration<Article>
{
   public ArticleConfiguration()
   {
      this.HasMany(x=>x.RelatedArticles)
         .WithMany(x=>x.OtherRelatedArticles)
         .Map(x=>x.ToTable("RelatedArticles"));
   }
}

Nothing complicated so far and let’s put together our context class

POCOContext.cs

public class POCOContext : DbContext
{
   public DbSet<Product> Product { get; set; }
   public DbSet<Article> Article { get; set; }
   public DbSet<ArticleContent> ArticleContent { get; set; }

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      
      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
      modelBuilder.Configurations.Add(new ArticleConfiguration());
   }
}

Lets write a simple unit test to add some dummy data and set up the relationship before we persist the data in the database.

[TestMethod]
public void Should_be_able_to_Add_Articles()
{
   var context = new POCOContext();

   var article1 = new Article
                             {
                                Title = "Article 1",
                                PublishedDate = DateTime.Now,
                             };

   var article2 = new Article
                             {
                                Title = "Related to Article 1",
                                PublishedDate = DateTime.Now,
                             };

   var article3 = new Article
                           {
                              Title = "Related to Article 1",
                              PublishedDate = DateTime.Now,
                           };

   var article4 = new Article
                           {
                              Title = "Other: Related to Article1",
                              PublishedDate = DateTime.Now,
                           };

   var article5 = new Article
                           {
                              Title = "Other: Related to Article1",
                              PublishedDate = DateTime.Now,
                           };

   article1.RelatedArticles.Add(article2);
   article1.RelatedArticles.Add(article3);
   article1.OtherRelatedArticles.Add(article4);
   article1.OtherRelatedArticles.Add(article5);

   context.Article.Add(article1);
   context.SaveChanges();
}

As you can see article2 and article3 are “tightly” related to article 1 and article4 and 5 are “loosely” related to article1.

Lets write a unit test and see how Entity Framework retrieves this object and its entire hierarchy.

Unit Test

[TestMethod]
public void Should_be_able_get_article_by_id()
{
   var context = new POCOContext();
   var article = context.Article
                        .Include("RelatedArticles")
                        .Include("OtherRelatedArticles")
                        .Where(x => x.Id == 1).SingleOrDefault();

   Assert.IsNotNull(article);
}

And the unit test passes with flying colours and now I’ll show the “article” object in watch window to show you that results are exactly what we expected.

And no surprises there we got the related articles and other related article for article id 1.

With this post and myprevious post on managing relationship, I have covered almost all major possible scenario for a real life production system and as you can see Entity Framework 4.1 handles it quite well.

EF Code First:Tips & Tricks

In this post I am going to show you some small tips & tricks about EF Code First which are quite useful.

Pluralization: By default Entity Framework pluralizes the table name, so if I have a domain object called ‘Employee’ it will create a table ‘Employees’. In order to turn it off you have to remove the Pluralizing table name from the convention collection of the modelbuilder object.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}

which is by the way is in the following namespace.

using System.Data.Entity.ModelConfiguration.Conventions.Edm.Db;

And the DbSet declaration is like this.

public DbSet<Employee> Employee { get; set; }

Ignoring properties: Suppose you have a property in your domain model which is quite important from the domain perspective but is not stored in the database. For example below is an Employee domain entity which has a property ‘IsQualifiedForTask’ which is computational property and is not stored in the database.

public class Employee
{
   public int Id { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public bool IsQualifiedForTask { get; set; }
}

In order to tell Entity Framework to do not persist this property in the database you could use the ‘Ignore’ keyword in the employee configuration class

public class EmployeeConfiguration : EntityTypeConfiguration<Employee>
{
   public EmployeeConfiguration()
   {
      this.Ignore(x => x.IsQualifiedForTask);
   }
}

Restricting Length: Suppose you want to restrict the size of a string column you can use the ‘HasMaxLength’ setting like this.

public class FacultyConfiguration : EntityTypeConfiguration<Faculty>
{
    public FacultyConfiguration()
    {
        this.Property(x => x.AnnuaLeave).IsRequired();
        this.Property(x => x.FirstName).HasMaxLength(100);
    }
}

Max string size: If you have a string property in your domain object and you don’t specify a size, by default Entity Framework will creates a corresponding column of type nvarchar(4000). This works fine for most of the scenario but what if it’s like a notes or a details field where the string can be more than 4000 characters. For example below is an article class which has a content column like this.

public class Article
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}

and in the configuration class I will map the ‘Content’ column to max size like this.

public class ArticleConfiguration : EntityTypeConfiguration<Article>
{
    public ArticleConfiguration()
    {
        this.Property(x => x.Content).HasColumnType("nvarchar(max)");
    }
}

That’s all folks, until next time enjoy playing around with Entity Framework Code First.

EF Code First: Table Per Type

In this post I am going to show you another type of mapping inheritance relationship which is called Table Per Type and it is quite similar to the Table Per hierarchy from the last post. The only difference is that here all the inheritance types will have their own table rather than combining them into one table.

Let start with a class diagram and see what we are trying to map.

Person.cs

public abstract class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

Student.cs

public class Student : Person
{
    public int CreditScore { get; set; }
}

Faculty.cs

public class Faculty : Person
{
    public int AnnuaLeave { get; set; }
}

PersonConfiguration.cs

public class PersonConfiguration : EntityTypeConfiguration<Person>
{
   public PersonConfiguration()
   {
      this.Map<Student>(x => x.ToTable("Student"))
          .Map<Faculty>(x => x.ToTable("Faculty"));
   }
}

POCOContext.cs

public class POCOContext : DbContext
{
   public DbSet<Person> People { get; set; }
   public DbSet<Student> Student { get; set; }
   public DbSet<Faculty> Faculty { get; set; }

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      modelBuilder.Configurations.Add(new PersonConfiguration());
   }
}

as you can see we just specify in our configuration class that each of the entity maps to an individual table. And now when you create the database context you will see the following relationship between the Person,Student and Faculty tables.

So you can see mapping inheritance in EF Code First is relatively simple but be careful when choosing one over the other. For example inheritance is great and it just works but when it comes to reporting it can lead to some complication, so think from that point of view as well.

EF Code First: Table Per Hierarchy

Continuing with my previous blog post about Entity framework today I am going to show you how to use Table Per Hierarchy relationship with Code First Approach.

It’s again a very useful mapping scenario and can be used for filtering specific types of domain objects.I am going to use a very simple real life scenario like a Person table and with that we have 2 derived types Players and Coaches, I know this may not be the best example for this kind of scenario but for illustration purpose and keep the example as simple as possible.

Lets first start with a domain model where we want to conceptually map 2 derived entities to a base entity.

and the code for 3 classes are as following :-

public abstract class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

public class Player : Person
{
    public int SportId { get; set; }
    public int NoOfGames { get; set; }
}

public class Coach : Person
{
    public DateTime ContractStartDate { get; set; }
    public DateTime ContractEndDate { get; set; }
}

and this is how we define the relationship in the configuration file.

public class PersonConfiguration : EntityTypeConfiguration<Person>
{
    public PersonConfiguration()
    {
        this.Map<Player>(x => x.Requires("PersonType").HasValue("P"))
            .Map<Coach>(x => x.Requires("PersonType").HasValue("C"));
    }
}

and now all we have to do is add the configuration class to the OnModelCreating method of our POCOContext class.

modelBuilder.Configurations.Add(new PersonConfiguration());

Lets write a unit test to make sure the database is getting created and the table are generated with the right column.

[TestMethod()]
public void Should_be_able_to_create_POCOContext_database()
{
    var target = new POCOContext();
    target.Database.Initialize(true);
}

and this is how our People table looks like.

So far so good but the best part is the data is flat combined structure in the database but yet in our domain model it is hierarchical and we can treat even the saving or retrieval of data in terms of domain model and not like treating it a data structure and separating the two kinds of Person with just types.

Lets write some more code to see it in action, first we will try adding a new Player into the table.

[TestMethod()]
public void Should_be_able_add_a_player()
{
    var player = new Player
                 {
                    FirstName = "Joe",
                    LastName = "Blogg",
                    SportId = 1,
                    NoOfGames = 120,
                    DateOfBirth = new DateTime(1980, 12, 12)
                 };

    context.Person.Add(player);
    context.SaveChanges();

}

and lets add a coach as well.

[TestMethod()]
public void Should_be_able_add_a_coach()
{
    var coach = new Coach
                {
                    FirstName = "Joe",
                    LastName = "Blogg",
                    DateOfBirth = new DateTime(1960, 1, 2),
                    ContractStartDate = new DateTime(2009, 12, 1),
                    ContractEndDate = new DateTime(2012,12,1)
                };
    context.Person.Add(coach);
    context.SaveChanges();
}

As you can see it can’t be easier than this and the power of this feature is then you are retrieving records of either a player or a coach type.

[TestMethod()]
public void Should_be_able_to_get_Players()
{
    var players = from p in context.Person.OfType<Player>()
                select p;

    var result = players.ToList();
    Assert.AreEqual(1,result.Count);
}

By just using OfType on the Person Entity the EF is doing the filtering of records based on the type and it’s doing all hard work. Hope you guys find it quite handy and use it in your project.