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

Entity Framework 7: In Memory Testing

I am really excited about the in-memory testing feature of Entity Framework 7 as testing in the previous versions of Entity Framework wasn’t straight forward and painful at times .

Let’s get started by installing the Entity Framework “InMemory” testing nuget package and make sure you have selected the “Include Prerelease” in the nuget dialogue as Entity Framework 7 is still in beta 6.

Alternatively the old-fashioned way

PM>Install-Package EntityFramework.InMemory -Prerelease

 

As you can see that when we install the EntityFramework.InMemory package it also brings the EntityFramework.Core package.These are some of the new changes to Entity Framework as EF has been decomposed into smaller manageable packages.

Lets start writing our domain objects and I am going to use the simple example of Student and Course as show below.

Student.cs

public class Student
{
    public Student()
    {
        Courses = new HashSet<Course>();
    }
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public ICollection<Course> Courses { get; set; }

    public override string ToString()
    {
        return 
            string.Format(
                    "Id = {0}, FirstName = {1}, LastName = {2}", 
                    this.Id, 
                    this.FirstName, 
                    this.LastName);
    }
}

Course.cs

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

    public string Name { get; set; }

    public string Description { get; set; }

}

Next we will define the database context and this is where you will see a significant change to the previous version. In the constructor of our context we are creating a dependency on DbContextOptions which will decide which source to use to persist the data.

TrainingContext.cs

public class TrainingContext : DbContext
{
    public TrainingContext(DbContextOptions options)
        : base(options)
    {
    }

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

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

Now we will wire up the TrainingContext to the main program and using the DbContextOptionsBuilder we will instruct the entity framework to use the in-memory database. I also created a separate method to populate the student table with 2 records.

Program.cs

class Program
{
    static void Main(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<TrainingContext>();
        optionsBuilder.UseInMemoryDatabase(true);

        using (var context = new TrainingContext(optionsBuilder.Options))
        {
            AddStudentData(context);

            var student = context.Student.FirstOrDefault(x => x.Id == 1);

            Console.WriteLine(student.ToString());

            student.FirstName = "James";
            context.SaveChanges();

            student = context.Student.FirstOrDefault(x => x.Id == 1);

            Console.WriteLine(student.ToString());

            Console.ReadLine();

        }
    }

    private static void AddStudentData(TrainingContext context)
    {
        context.Student.Add(new Student { Id = 1, FirstName = "Joe", LastName = "Blogg" });

        context.Student.Add(new Student { Id = 2, FirstName = "Jane", LastName = "Doe" });

        context.SaveChanges();
    }
}

As you can see we first retrieve the student with id 1 then we changed the first name of the student and asked entity framework to save the changes into the in-memory database.

And below is the output of the before and after state of that record.

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.

Entity Framework: Viewing SQL

In this post I am going to show you a cool trick I learned regarding ObjectQuery class and how it can be useful when working with Entity Framework.

In order to see what SQL statement Entity Framework is executing we generally tend to run the SQL profiler and set custom filters like the database name or id, the current user under which our code is going to execute the linq query etc so that we can isolate our query execution.

I find this approach very ugly as you have to start-stop your profiler trace in order to capture the exact time when the SQL is executed and in multi developer environment when everyone is developing against the same database it becomes quite challenging.

Well it’s not painful if we use a better approach using the ObjectQuery class and an extension method which extends the IQueryable interface. So here is the code for the ObjectQuery Extension class.

ObjectQueryExtension.cs

public static class ObjectQueryExtension
{
    public static string ToTraceString(this IQueryable t)
    {
        var objectQuery = t as ObjectQuery;
        return objectQuery != null ? objectQuery.ToTraceString() : 
                                     String.Empty;
    }
}

So now I’ll generate the EF Model from the publisher database and the entities I am interested in are Author and Title as shown below.

and the following code to display the SQL statement in the console window.

Program.cs

class Program
{
    static void Main(string[] args)
    {
        using (var context = new PublisherContext())
        {
            var authorId = "998-72-3567";
            var query = context.Author
                            .Where(x=>x.au_id == authorId)
                            .SelectMany(ta=> ta.titleauthors,(au,ti)
                            => new {
                                    au.au_id,
                                    au.au_fname,
                                    au.au_lname,
                                    ti.title
                                    });

            var sql = query.ToTraceString();
            Console.WriteLine("Generated SQL is :");
            Console.WriteLine(sql);
            Console.ReadKey();
        }
    }
}

and this is how the output looks like.

As you can see this small extension methods has lots of potential as you can use it along with logging frameworks like log4net and output the SQL to a file.

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.