Table per type in Entity Framework

In our previous article we have discussed that, there are three different implementation of inheritance in Entity Framework, and those are

1.          Table per hierarchy
2.          Table per Type
3.          Table per concrete type

In our previous article we implemented Table per Hierarchy; in this article we will learn Table per Type implementation.  

The concept of table per type is , Table will get created for each and every type.  For example , If there is class relationship like this.

Then for all three classes, three different tables will get create.  Let’s see in example.  Have a look on below code.

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Data.Entity;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApp

{

    public class Person

    {

        [Key]

        public int PersonId { get; set; }

        [MaxLength(10)]

        public string name { get; set; }

        public string surname { get; set; }

    }

    [Table("Friend")]

    public class Friend : Person

    {

        public Boolean IsClassmate { get; set; }

    }

    [Table("BestFriend")]

    public class BestFriend : Person

    {

        public string Address { get; set; }

    }

   

    public class personContext : DbContext

    {

        public personContext()

            : base("DBConnectionString")

        {

        }

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

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

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

    }

}

The implementation is very simple to understand, We have used Data annotation technique to map relationship between entity and relational database. Friend and BestFriend class has derived from Person class.

Now, point to discuss, to implement Table per type, just we have to add “Table” attribute above the class definition, that’s all and Entity Framework will create separate table for each class.

In hierarchical relationship, it’s not necessary to decorate base class with “Table” attribute. So that, we did not add in this example  and even those lines too optional, It’s enough to specify the base type.

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

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

Once you run the code and seed database, you will find below database structure, here we are seeing three tables for three different classes.   

Save data in three tables

Now, we will try to save data in those tables and we will observer, how data get save in tables.  Have a look on below code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ConsoleApp.Company;

using System.Collections;

namespace ConsoleApp

{

    public class Program

    {

        public static void Main(string[] args)

        {

            using (var ctx = new personContext())

            {

                //ctx.Database.Create();

                //Save Person Object

                ctx.Person.Add(new Person { name = "sourav", surname = "kayal" });

                ctx.SaveChanges();

                //Save Friend Object

                ctx.Friend.Add(new Friend { name = "foo", surname = "foo", IsClassmate = false });

                ctx.SaveChanges();

                //Save BestFriend Object

                ctx.BestFriend.Add(new BestFriend { name = "Ram", surname = "Shyam", Address = "Kolkata" });

                ctx.SaveChanges();

            }

        }

    }

}

The code is pretty simple to understand, If we run the code and open database, we will see that the data got saved in this structure in three different tables. 

Fine, we have see how data get save in database, now we will retrieve data in various form.

Retrieve “Friend” object using query

We will fetch only “Friend” object from Person table, please try to remember that, Person is the base table in this hierarchy. We can use OfType in LINQ to access only property of Person object. Here is sample example.

      public static void Main(string[] args)

        {

            using (var ctx = new personContext())

            {

                var data = (from m in ctx.Person.OfType<Friend>() select m).ToList();

                Console.WriteLine("Name :" + data[0].name);

                Console.WriteLine("Surname :" + data[0].surname);

                Console.WriteLine("Is Classmate :" + data[0].IsClassmate);

                Console.WriteLine("Person Id :" + data[0].PersonId);

            }

        }

Retrieve BestFriend object :

This is similar to previous one, if we want only object of “BestFriend” then we can pass the parameter through OfType.

public static void Main(string[] args)

        {

            using (var ctx = new personContext())

            {

              

                var data = (from m in ctx.Person.OfType<BestFriend>() select m).ToList();

                Console.WriteLine("Name :" + data[0].name);

                Console.WriteLine("Surname :" + data[0].surname);

                Console.WriteLine("Address :" + data[0].Address);

                Console.WriteLine("Person Id :" + data[0].PersonId);

               

            }

        }

And here is the output of this example.

Retrieve person object

Now, if we want to access base class object, we need not specify anything in query. In this example we will retrieve object of person class.

public static void Main(string[] args)

        {

            using (var ctx = new personContext())

            {

                var data = (from m in ctx.Person select m).ToList();

                Console.WriteLine("Name :" + data[0].name);

                Console.WriteLine("Surname :" + data[0].surname);

                Console.WriteLine("Person Id :" + data[0].PersonId);

               

            }

        }

And only we are getting the property of person class.

Border Line

In this article we have learned the concept of Table per Type implementation to implement inheritance in Entity Framework, hope you have got the fundamental idea. In our previous article we will discuss the concept of “Table per concrete type”.