Inheritance Modelling Patterns with Entity Framework 6 Code First

Introduction

In this post I’m going to demonstrate how to model inheritance using Entity Framework 6 Code First. In particular, I’m going to demonstrate the following alternative database mapping strategies –

  • Table Per Hierarchy (TPH)
  • Table Per Concrete Type (TPC)
  • Table Per Type (TPT)

My model is as follows –

contract

Contract, the base class, has a start and end date, and a unique identifier. CreditContract and PrepaymentContract derive from Contract and hence inherit it’s properties. In addition, CreditContract has credit limit and currency properties, whilst PrepaymentContract has prepayment amount and period properties..

Code First Classes

First step is to create the model classes.

Contract

Contract is the base class for the hierarchy, hence the abstract modifier.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }
}

CreditContract

CreditContract is derived from Contract.

For simplicity I have modelled the CreditLimitCurrency property as a string with data annotations mapping it to a char(3) column. In practice it would be better to model this as a Enum.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public class CreditContract : Contract
    {
        [Required]
        public float CreditLimit { get; set; }

        [Column(TypeName = "char")]
        [StringLength(3)]
        [Required]
        public string CreditLimitCurrency { get; set; }
    }
}

PrepaymentContract

PrepaymentContract also derives from Contract.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public class PrepaymentContract : Contract
    {
        [Required]
        public float PrepaymentAmount { get; set; }

        [Column(TypeName = "char")]
        [StringLength(1)]
        [Required]
        public string PrepaymentPeriod { get; set; }
    }
}

Table Per Hierarchy (TPH)

With the TPH mapping strategy, all classes in the inheritance tree are mapped to a single database table. A database field is used as a discriminator in determining which concrete type each database record should be mapped to.

To instruct Entity Framework to use this strategy, all that is required is to derive a class from the DbContext class, and add a DBSet property for the Contract type, whilst NOT adding DBSet properties for CreditContract nor PrepaymentContract.

I have also added an override of the OnModelCreating method. I’ll discuss this in a moment.

CodeFirstInheritanceDbContext

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Contract>()
                .Map<CreditContract>(m => m.Requires("Discriminator").HasValue("C"))
                .Map<PrepaymentContract>(m => m.Requires("Discriminator").HasValue("P"));

            base.OnModelCreating(modelBuilder);
        }
    }
}

If we then enable migrations and add an initial migration, the following DbMigration class is generated –

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Contracts",
                c => new
                    {
                        ContractID = c.Int(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        CreditLimit = c.Single(),
                        CreditLimitCurrency = c.String(maxLength: 3, fixedLength: true, unicode: false),
                        PrepaymentAmount = c.Single(),
                        PrepaymentPeriod = c.String(maxLength: 1, fixedLength: true, unicode: false),
                        Discriminator = c.String(nullable: false, maxLength: 128),
                    })
                .PrimaryKey(t => t.ContractID);

        }

        public override void Down()
        {
            DropTable("dbo.Contracts");
        }
    }
}

A single table, Contract, is created with fields from all types. Of note is the Discriminator field, which we did not explicitly specify in our hierarchy. EF has generated this field for us and will use it to discriminate between credit and prepayment contract records.

If we update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

The following records are added to the Contract table –

tphresults

By default EF will use the full names of the types as the discriminator values. This may significantly increase the size of each record. EF allows us to override these values using the fluent API. This is what I did in the OnModelCreating method of the CodeFirstInheritanceDbContext class, in order to shorten the discriminator to a single character.

We can then query the model across contract types and for specific contract types as follows –

using (var db = new CodeFirstInheritanceDbContext())
{
    var countOfAllContract = db.Contracts.Count();
    var countOfCreditContracts = db.Contracts.OfType<CreditContract>().Count();
}

TPH is the simplest mapping strategy to implement. And as all data is stored in a single table there are no joins required when querying data. It is therefore the most performant strategy.

However, there are a few issues –

  • If you look back at the CreditContract and PrepaymentContract classes you’ll note that all the properties are decorated with the [Required] attribute. However, to fit records of both types into a single database table EF has had to generate the fields as nullable. Data integrity has potentially been compromised.
  • It is not clear from the database table which fields relate to which type. This may make life difficult when populating the table from other sources.
  • The table breaks 3rd normal form as the values contained in the sub-type fields depend on the discriminator. 3rd normal form states that fields should only depend on the table key.

Table Per Concrete Type (TPC)

With the TPC mapping strategy, each concrete class is mapped to it’s own database table. So we get 2 tables, CreditContract and PrepaymentContract, with each having all fields in the respective branch of the hierarchy. We do not get a table for the abstract base class.

Before we instruct EF to use this strategy, we need to make a small modification to the Contract class – change the type of the ContractID property from int to Guid. I’ll discuss why in a moment.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public Guid ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }
}

As per the TPH strategy, we derive a class from DbContext, and add a DBSet property for the Contract type, whilst NOT adding DBSet properties for CreditContract nor PrepaymentContract.

We then override the OnModelCreating method and add fluent API code to instruct EF to map the CreditContract entity to a specific table. And to also map the inherited properties to this table. The table name does not have to be the same as the entity name.

We do the same for PrepaymentContract.

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CreditContract>().Map(m =>
            {
                m.MapInheritedProperties();
                m.ToTable("CreditContract");
            });

            modelBuilder.Entity<PrepaymentContract>().Map(m =>
            {
                m.MapInheritedProperties();
                m.ToTable("PrepaymentContract");
            });   
           
            base.OnModelCreating(modelBuilder);
        }
    }
}

If we then enable migrations and add an initial migration, we can see from the migration script that 2 separate tables are created on the database.

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.CreditContract",
                c => new
                    {
                        ContractID = c.Guid(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        CreditLimit = c.Single(nullable: false),
                        CreditLimitCurrency = c.String(nullable: false, maxLength: 3, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID);
            
            CreateTable(
                "dbo.PrepaymentContract",
                c => new
                    {
                        ContractID = c.Guid(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        PrepaymentAmount = c.Single(nullable: false),
                        PrepaymentPeriod = c.String(nullable: false, maxLength: 1, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID);
            
        }
        
        public override void Down()
        {
            DropTable("dbo.PrepaymentContract");
            DropTable("dbo.CreditContract");
        }
    }
}

Update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

The CreditContract entity has been persisted to the CreditContract table –

tpc1

Whilst the PrepaymentContract entity has been persisted to the PrepaymentContract table –

tpc2

We can then query the entities exactly as we did when the TPH strategy was adopted. The consumer of the model does not need to be aware of the mapping strategy.

We changed the ContractID property from int to Guid. The reason is that we are asking the database server to generate unique identifiers for our records. The concrete entity types are mapped to separate tables, and the database server does not and should not know this. Integer identity fields are only unique within the context of a single table, and so if the type was int we would soon experience errors similar to the following –

The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.

However, by changing the type to Guid we are, to all intents and purposes, generating unique fields.

The downside of doing this is that SQL Server GUID fields are 16 bytes in length, compared to 4 bytes for an int.

Compared to TPH, TPC is less performant when querying across types as the queries require a UNION between the tables. However, it will be equally performant when querying a single type.

As TPC stores the data in separate tables, it allows for non-nullable columns, hence ensuring data integrity. And it is clear which properties are required by which type.

Table Per Type (TPT)

With the Table By Type (TPT) strategy, both abstract and concrete types are mapped to their own tables in the database with the inheritance relationships represented by foreign key relationships.

To instruct EF to use this strategy we have 2 options.

The first option is to override the OnModelCreating method and add Fluent API code to instruct EF to map the CreditContract entity to a specific table, and the PrepaymentContract entity to a different specific table.

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CreditContract>().Map(m =>
            {
                m.ToTable("CreditContract");
            });

            modelBuilder.Entity<PrepaymentContract>().Map(m =>
            {
                m.ToTable("PrepaymentContract");
            });   
           
            base.OnModelCreating(modelBuilder);
        }
    }
}

The second option is to annotate the concrete types with the [Table()] attribute.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }

    [Table("CreditContract")]
    public class CreditContract : Contract
    {
        [Required]
        public float CreditLimit { get; set; }

        [Column(TypeName = "char")]
        [StringLength(3)]
        [Required]
        public string CreditLimitCurrency { get; set; }
    }

    [Table("PrepaymentContract")]
    public class PrepaymentContract : Contract
    {
        [Required]
        public float PrepaymentAmount { get; set; }

        [Column(TypeName = "char")]
        [StringLength(1)]
        [Required]
        public string PrepaymentPeriod { get; set; }
    }
}

Enabling migrations and adding an initial migration gives us the following –

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Contracts",
                c => new
                    {
                        ContractID = c.Int(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                    })
                .PrimaryKey(t => t.ContractID);

            CreateTable(
                "dbo.CreditContract",
                c => new
                    {
                        ContractID = c.Int(nullable: false),
                        CreditLimit = c.Single(nullable: false),
                        CreditLimitCurrency = c.String(nullable: false, maxLength: 3, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID)
                .ForeignKey("dbo.Contracts", t => t.ContractID)
                .Index(t => t.ContractID);

            CreateTable(
                "dbo.PrepaymentContract",
                c => new
                    {
                        ContractID = c.Int(nullable: false),
                        PrepaymentAmount = c.Single(nullable: false),
                        PrepaymentPeriod = c.String(nullable: false, maxLength: 1, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID)
                .ForeignKey("dbo.Contracts", t => t.ContractID)
                .Index(t => t.ContractID);

        }

        public override void Down()
        {
            DropForeignKey("dbo.PrepaymentContract", "ContractID", "dbo.Contracts");
            DropForeignKey("dbo.CreditContract", "ContractID", "dbo.Contracts");
            DropIndex("dbo.PrepaymentContract", new[] { "ContractID" });
            DropIndex("dbo.CreditContract", new[] { "ContractID" });
            DropTable("dbo.PrepaymentContract");
            DropTable("dbo.CreditContract");
            DropTable("dbo.Contracts");
        }
    }
}

3 tables are created, one per type, as shown in the following database schema –

dbdiagram

Update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

And this is what we get in our Contract table –

tpt1

And in the CreditContract table –

tpt2

And finally the PrepaymentContract table –

tpt3

TPT is the least performant of the strategies, but enables the highest degree of database integrity and normalization.

 

 

 

Server-side paging with Entity Framework

Source code for article – https://github.com/RossWhitehead/ServerSidePaging

(Note. I deleted the original source code as part of a clean-up of my repos. It was very cathartic but I got a little carried away. I’ve recreated the code in the repo above. But a few of the objects are named differently. Sorry!)

In my last post – Unit of Work, Repository, Entity Framework, and Persistence Ignorance – I presented a generic entity framework implementation of the repository pattern. In this post I’m going to present some changes to this implementation to enable server-side paging.

I have a large data-set that I wish to display in a table or grid. To minimize the size of the response, and to improve user experience, I want to present the data one page at a time, and allow the user to paginate back and forth through the data-set.

Below you can see that I am presenting the 1st page of 5 products from my BikeStore in a grid. If I click on the next button I will see the next page. (The eagle-eyed amongst you will notice that I only have 6 products in total. Ignore this and pretend I have a large data-set.)

Paging

I am using the Telerik Kendo UI Grid, and I can retrieve a list of products, and bind it to the grid using the Get() method of the aforementioned repository. So code similar to that shown below –

using (var unitOfWork = new BikeStoreUnitOfWork())
{
    IEnumerable products = unitOfWork.ProductRepository.Get();
    return View(products);
}

The Grid is clever enough to determine the number of pages and display accordingly. There are a number of other grid helpers on the market which also do this work for us.

However, the paging is being done on the client, and each time the user clicks on a pager button the full product list is requested from the server – via an ajax call in this case – even though only a single page will be displayed. This will obviously be very inefficient and very slow with large data-sets, as all products will be retrieved from the database every time the user navigates to a new page.

How can we improve the performance?

The Get() method returns IEnumerable. We could modify the Get() method to instead return IQueryable. In so doing we are deferring execution, and we can then allow the consumer to restrict the query using the Skip and Take methods. This does in fact simplify our entity framework implementation of repository. Also, it works well with some grid helpers – such as Grid.Mvc – which emits the Skip and Take automatically.

However, the problem with this is that we are assuming that the data provider supports IQueryable. Entity Framework does, but what if we are required to switch to provider that does not. Also, mocking an IQueryable interface is not a simple task and so our unit testing becomes more difficult.

I have chosen not to change the repository to return IQueryable. But instead allow the consumer to request a page of data, and restrict the IEnumerable set accordingly.

IRepository

using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace Framework.Data
{
    public interface IRepository<TEntity> where TEntity : class
    {
        IEnumerable<TEntity> Get();

        IEnumerable<TEntity> Get(
           Expression<Func<TEntity, bool>> filter = null);

        IEnumerable<TEntity> Get(
           Expression<Func<TEntity, bool>> filter = null,
           string[] includePaths = null);

        IEnumerable<TEntity> Get(
           Expression<Func<TEntity, bool>> filter = null,
           string[] includePaths = null,
           int? page = 0,
           int? pageSize = null,
           params SortExpression<TEntity>[] sortExpressions);

        void Add(TEntity entity);

        void Alter(TEntity entity);

        void Remove(TEntity entity);
    }
}

As before, my IRepository is returning IEnumerable. However, I have overloaded the Get method. The final overload, which enables server-side paging, accepts the following parameters –

  • Expression<Func<TEntity, bool>> filter = null
    • A Linq expression defining a filter
  • string[] includePaths = null
    • An array of strings, with each string denoting an include path for a related entity
  • int? page = 0
    • The required page of data.
  • int? pageSize = null
    • The page size.
  • params SortExpression<TEntity>[] sortExpressions
    • A variable number of SortExpression instances, with each instance denoting a Linq Expression to sort and a sort direction. The SortExpression class is shown below.

SortExpression<TEntity>

using System;
using System.ComponentModel;
using System.Linq.Expressions;

namespace Framework.Data
{
    public class SortExpression<TEntity> where TEntity : class
    {
        public SortExpression(Expression<Func<TEntity, object>> sortBy, ListSortDirection sortDirection)
        {
            SortBy = sortBy;
            SortDirection = sortDirection;
        }

        public Expression<Func<TEntity, object>> SortBy { get; set; }
        public ListSortDirection SortDirection { get; set; }
    }
}

Repository

The generic entity framework implementation of the repository is as follows –

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;

namespace Framework.Data
{
    public class EFRepository<TEntity> : IRepository<TEntity> where TEntity : class
    {
        private DbContext db;
        private DbSet<TEntity> dbSet;

        public EFRepository(DbContext context)
        {
            this.db = context;
            this.dbSet = context.Set<TEntity>();
        }

        public IEnumerable<TEntity> Get()
        {
            return Get(null, null, null, null, null);
        }

        public IEnumerable<TEntity> Get(
            Expression<Func<TEntity, bool>> filter = null)
        {
            return Get(filter, null, null, null, null);
        }

        public IEnumerable<TEntity> Get(
            Expression<Func<TEntity, bool>> filter = null,
            string[] includePaths = null)
        {
            return Get(filter, includePaths, null, null, null);
        }

        public IEnumerable<TEntity> Get(
           Expression<Func<TEntity, bool>> filter = null,
           string[] includePaths = null,
           int? page = null,
           int? pageSize = null,
           params SortExpression<TEntity>[] sortExpressions)
        {
            IQueryable<TEntity> query = dbSet;

            if (filter != null)
            {
                query = query.Where(filter);
            }

            if (includePaths != null)
            {
                for (var i = 0; i < includePaths.Count(); i++)
                {
                    query = query.Include(includePaths[i]);
                }
            }

            if (sortExpressions != null)
            {
                IOrderedQueryable<TEntity> orderedQuery = null;
                for (var i = 0; i < sortExpressions.Count(); i++)
                {
                    if (i == 0)
                    {
                        if (sortExpressions[i].SortDirection == ListSortDirection.Ascending)
                        {
                            orderedQuery = query.OrderBy(sortExpressions[i].SortBy);
                        }
                        else
                        {
                            orderedQuery = query.OrderByDescending(sortExpressions[i].SortBy);
                        }
                    }
                    else
                    {
                        if (sortExpressions[i].SortDirection == ListSortDirection.Ascending)
                        {
                            orderedQuery = orderedQuery.ThenBy(sortExpressions[i].SortBy);
                        }
                        else
                        {
                            orderedQuery = orderedQuery.ThenByDescending(sortExpressions[i].SortBy);
                        }

                    }
                }

                if (page != null)
                {
                    query = orderedQuery.Skip(((int)page - 1) * (int)pageSize);
                }
            }

            if (pageSize != null)
            {
                query = query.Take((int)pageSize);
            }

            return query.ToList();
        }

        public virtual void Add(TEntity entity)
        {
            this.dbSet.Add(entity);
        }

        public virtual void Remove(TEntity entityToDelete)
        {
            if (this.db.Entry(entityToDelete).State == EntityState.Detached)
            {
                this.dbSet.Attach(entityToDelete);
            }
            this.dbSet.Remove(entityToDelete);
        }

        public virtual void Alter(TEntity entityToUpdate)
        {
            this.dbSet.Attach(entityToUpdate);
            this.db.Entry(entityToUpdate).State = EntityState.Modified;
        }

    }
}

Please note that the page parameter will be ignored if there is not at least one SortExpression. This is because the Skip will raise an exception is the query is not ordered.

IUnitOfWork, IBikeStoreUnitOfWork, BikeStoreUnitOfWork, and BikeStoreContext do not require any modifications. For completeness I have listed these below.

IUnitOfWork

using System;

namespace Framework.Data
{
    public interface IUnitOfWork
    {
        void Save();
    }
}

IBikeStoreUnitOfWork

using BikeStore.Model;
using Framework.Data;

namespace BikeStore.Data
{
    public interface IBikeStoreUnitOfWork : IUnitOfWork
    {
        IRepository ProductRepository { get; }
        IRepository BasketRepository { get; }
        IRepository CustomerRepository { get; }
    }
}

BikeStoreUnitOfWork

using BikeStore.Model;
using Framework.Data;
using System;
using System.Data.Entity.Core.Objects;

namespace BikeStore.Data
{
    public class BikeStoreUnitOfWork : IBikeStoreUnitOfWork, IDisposible
    {
        private BikeStoreContext _db = new BikeStoreContext();

        private EFRepository _productRepository;
        private EFRepository _basketRepository;
        private EFRepository _customerRepository;

        public IRepository ProductRepository
        {
            get
            {
                if (_productRepository == null)
                    _productRepository = new EFRepository(_db);
                return _productRepository;
            }
        }

        public IRepository BasketRepository
        {
            get
            {
                if (_basketRepository == null)
                    _basketRepository = new EFRepository(_db);
                return _basketRepository;
            }
        }

        public IRepository CustomerRepository
        {
            get
            {
                if (_customerRepository == null)
                    _customerRepository = new EFRepository(_db);
                return _customerRepository;
            }
        }

        public void Save()
        {
            _db.SaveChanges();
        }

        private bool _disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this._disposed)
            {
                if (disposing)
                {
                    _db.Dispose();
                }
            }
            this._disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}

BikeStoreContext

using BikeStore.Model;
using System.Data.Entity;

namespace BikeStore.Data
{
    public class BikeStoreDbContext : DbContext
    {
        public BikeStoreDbContext() : base("DefaultConnection")
        { }

        public virtual DbSet<Product> Products { get; set; }
        public virtual DbSet<Basket> Baskets{ get; set; }
        public virtual DbSet<Customer> Customers{ get; set; }
    }
}

Example of Server-side Paging

And this is an example of how to use the repository to request a page of entities –

using (var unitOfWork = new BikeStoreUnitOfWork())
{
    IEnumerable products = unitOfWork.ProductRepository.Get (
        p => p.Price > 2000,
        null, // e.g. new string[] { "ProductCategory", "ProductSubCategory.ProductCategory" },
        2,
        10,
        new SortExpression(p => p.Name, ListSortDirection.Ascending),
        new SortExpression(p => p.Description, ListSortDirection.Descending));
}

I am filtering the product list to only retrieve products priced over £2000. I’m requesting the inclusion of the ProductSubCategory and ProductSubCategory.ProductCategory entities. I’m specifying that the entities be ordered by Name ASC and then Description DESC, and I’m requesting the 2nd page of 10 entities.

And here’s the resulting query –

SELECT
    [Extent1].[ProductId] AS [ProductId],
    [Extent1].[Name] AS [Name],
    [Extent1].[Description] AS [Description],
    [Extent1].[Price] AS [Price],
    [Extent1].[ProductSubCategoryId] AS [ProductSubCategoryId],
    [Extent2].[ProductSubCategoryId] AS [ProductSubCategoryId1],
    [Extent2].[Name] AS [Name1],
    [Extent2].[ProductCategoryId] AS [ProductCategoryId],
    [Extent3].[ProductCategoryId] AS [ProductCategoryId1],
    [Extent3].[Name] AS [Name2]
    FROM   [dbo].[Product] AS [Extent1]
    INNER JOIN [dbo].[ProductSubCategory] AS [Extent2] ON [Extent1].[ProductSubCategoryId] = [Extent2].[ProductSubCategoryId]
    INNER JOIN [dbo].[ProductCategory] AS [Extent3] ON [Extent2].[ProductCategoryId] = [Extent3].[ProductCategoryId]
    WHERE [Extent1].[Price] > cast(2000 as real)
    ORDER BY [Extent1].[Name] ASC, [Extent1].[Description] DESC
    OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Unit of Work, Repository, Entity Framework, and Persistence Ignorance

Let me lay my cards on the table. I’m not an expert on design patterns. But I’ve read some books, some blog posts, and followed many a debate online. I’ve developed a good few applications and have generally gained a good appreciation for the principles, what we are trying to achieve, and what works in practice.

I’m going to present my go-to implementation when starting a new solution. I will discuss the basics of the patterns and principles. Just enough to answer the Why questions.

I am not presenting anything new or unique. These are tried and tested patterns which are industry best practice. If the code is similar, or even the same, as code published by other sources then I apologize for not referencing those sources. I have used this implementation over a number of years and so would not be able to tell you where the ideas came from, or which snippets of code have been copied or created from scratch.

My choice of weapons at the moment are ASP.NET MVC, C#, Entity Framework (Code First), Unity, and Moq. However, the data persistence layer and service layer that I will present is mostly technology agnostic, so it should be easy enough to implement the classes using other technologies.

I will be presenting this information in a series of posts –

1) Unit of Work, Repository, Entity Framework, and Persistence Ignorance

2) Service Layer, MVC Controllers, and Dependency Injection

3) Unit Testing using Moq

Unit Of Work

According to Fowler Unit of Work –

“Maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems.

When you’re pulling data in and out of a database, it’s important to keep track of what you’ve changed; otherwise, that data won’t be written back into the database. Similarly you have to insert new objects you create and remove any objects you delete.”

The Entity Framework’s DbContext class is essentially an implementation of Unit of Work. It manages the connection to the database. It populates entity objects from the database as and when needed. It tracks changes to the entity objects. And it persists these changes to the database, managing transactions and concurrency when doing so.

So most of the work is done for us.

The recommended way of working with DbContext is to derive a class from DbContext and expose the required entities as DBSet properties. So for my BikeStore application, I could create the following class –

using BikeStore.Model;
using System.Data.Entity;

namespace BikeStore.Data
{
    public class BikeStoreContext : DbContext
    {
        public virtual DbSet<Basket> Baskets { get; set; }
        public virtual DbSet<Product> Products { get; set; }
        public virtual DbSet<Customer> Customers{ get; set; }
    }
}

I could then create an instance of this class, and then query, add, modify, and delete entities using this instance.

using(var context = new BikeStoreContext())
{
    Product product = context.Products.Where(p => p.ProductId == 1).First();
    product.Name = "CAAD 10";
    context.SaveChanges();
}

If I’m developing using ASP.NET MVC I may choose to include this code in a controller action method, or maybe refactor it a little and include it in a helper class or even in a business logic or service layer class.

The problem with the above is that the DbContext and DbSet classes are specific to the System.Data.Entity library. We would need to reference this library in the controller, and as such the controller would now be aware of and dependent on Entity Framework. If we had to switch to another data persistence technology, such as nHibernate, then we would need to re-write and re-test all controller methods that are now dependent on Entity Framework. Admittedly, I have never yet had to do this. But never say never.

A bigger concern is that by creating a dependency between the controllers and Entity Framework, we are making our lives very difficult when it comes to unit testing. We will find it very difficult to isolate the controllers from the database.

So how do we decouple the controllers from the persistence layer, and hence make the controllers persistence ignorant?

IUnitOfWork

The first thing to do is create an IUnitOfWork interface. It’s a very simple interface, with a single method – Save().

using System;

namespace Framework.Data
{
    public interface IUnitOfWork
    {
        void Save();
    }
}

Later on we will create a concrete Entity Framework implementation of this interface, wrapping up the DbContext. DbContext would essentially be hidden from the controllers, and as such the dependency will have been removed.

IRepository

But we still need to remove the dependency on DBSet whilst allowing the querying, adding, modifying, and deleting of entities.

We will use the repository pattern to achieve this. According to Fowler, the Repository pattern –

“Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.”

Rather than DBSet being the interface for accessing the entities, we will use IEnumerable.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace Framework.Data
{
    public interface IRepository<TEntity> where TEntity : class
    {
        IEnumerable<TEntity> Get(
            Expression<func<TEntity, bool>> filter = null,
            Func<iqueryable<TEntity>, IOrderedQueryable> orderBy = null,
            string includeProperties = "");

        IEnumerable<TEntity> GetAll(
            Func<iqueryable<TEntity>, IOrderedQueryable> orderBy = null,
            string includeProperties = "");

        IEnumerable<TEntity> GetTopN(
            int? N,
            Expression<Func<TEntity, bool>> filter = null,
            Func<iqueryable, IOrderedQueryable> orderBy = null,
            string includeProperties = "");

        void Add(TEntity entity);

        void Alter(TEntity entity);

        void Remove(TEntity entity);
    }
}

The first method in the interface, Get, enables the retrieving of a collection of entities. It has 3 optional parameters –

  • filter – which accepts a lambda expression defining which set of entities to retrieve
  • orderBy – which defines how the set should be ordered
  • includeProperties – which accepts a comma-delimited string of related property names to retrieve with each entity.

As its name suggests, the second method, GetAll, enables the retrieving of all entities. Concrete implementations of this method may simple call the Get method. It’s not necessary to include this method in you implementation. It’s there only for convenience.

GetTopN accepts the same parameters as Get, and additionally has a parameter which defines the number of entities to return.

Add, Alter, and Remove all accept an entity, and as their names suggest enable the modification of entities.

EFRepository

We have defined a generic IRespository interface, and could now use this for creating a concrete Entity Framework implementation of Repository for each entity in our system. But we can simplify things by creating a generic Entity Framework implementation. So a single generic class rather than one class per entity.

The generic Entity Framework implementation shown below implements a constructor that take an instance if DbContext, and it used this instance to access the required DbSet.

We will create a  an instance of EFRepository for each entity in our model, and pass the same DbContext into each. We can then save changes to different entity types in a single transaction.

using System.Linq.Expressions;

namespace Framework.Data
{
    public class EFRepository<TEntity> : IRepository where TEntity : class
    {
        private DbContext _db;
        private DbSet<TEntity> _dbSet;

        public EFRepository(DbContext context)
        {
            this._db = context;
            this._dbSet = context.Set<TEntity>();
        }

        public virtual IEnumerable<TEntity> Get(
            Expression<Func<TEntity, bool>> filter = null,
            Func<iqueryable, IOrderedQueryable> orderBy = null,
            string includeProperties = "")
        {
            IQueryable<TEntity> query = _dbSet;

            if (filter != null)
                query = query.Where(filter);

            if (includeProperties != null)
                foreach (var includeProperty in includeProperties.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                    query = query.Include(includeProperty);

            if (orderBy != null)
                orderBy(query);

            return query;
        }

        public virtual IEnumerable GetAll(
            Func<iqueryable, IOrderedQueryable> orderBy = null,
            string includeProperties = "")
        {
            return this.Get(null, orderBy, includeProperties);
        }

        public virtual IEnumerable GetTopN(
            int? N,
            Func<iqueryable, IOrderedQueryable> orderBy = null,
            string includeProperties = "")
        {
            if (N == null)
                return this.Get(null, orderBy, includeProperties);
            else
                return this.Get(null, orderBy, includeProperties).Take((int)N);
        }

        public virtual void Add(TEntity entity)
        {
            _dbSet.Add(entity);
        }

        public virtual void Remove(TEntity entityToDelete)
        {
            if (_db.Entry(entityToDelete).State == EntityState.Detached)
                _dbSet.Attach(entityToDelete);
            _dbSet.Remove(entityToDelete);
        }

        public virtual void Alter(TEntity entityToUpdate)
        {
            _dbSet.Attach(entityToUpdate);
            _db.Entry(entityToUpdate).State = EntityState.Modified;
        }
    }
}

IBikeStoreUnitOfWork

If you look at the namespaces for the classes shown so far, you’ll see that I have included all the classes in the Framework.Data namespace. This is because there is nothing that is application specific.

I started this article by presenting the BikeStoreContext class, and pointing out that by consuming it in our controllers we are tying the controllers to Entity Framework.

I will now replicate the BikeStoreContext class but in a way that hides the implementation of the persistence technology.

Firstly, let’s create an IBikeStoreUnitOfWork interface, which exposes an IRepository class for each entity in our BikeStore model (I have only shown a few of the entities to keep the examples short) –

using BikeStore.Model;
using Framework.Data;

namespace BikeStore.Data
{
    public interface IBikeStoreUnitOfWork : IUnitOfWork
    {
        IRepository ProductRepository { get; }
        IRepository BasketRepository { get; }
        IRepository CustomerRepository { get; }
    }
}

BikeStoreUnitOfWork

We now want to create our concrete Entity Framework implementation of this interface.

using BikeStore.Model;
using Framework.Data;
using System;
using System.Data.Entity.Core.Objects;

namespace BikeStore.Data
{
    public class BikeStoreUnitOfWork : IBikeStoreUnitOfWork, IDisposible
    {
        private BikeStoreContext _db = new BikeStoreContext();

        private EFRepository<Product> _productRepository;
        private EFRepository<Basket> _basketRepository;
        private EFRepository<Customer> _customerRepository;

        public IRepository ProductRepository
        {
            get
            {
                if (_productRepository == null)
                    _productRepository = new EFRepository<Product>(_db);
                return _productRepository;
            }
        }

        public IRepository BasketRepository
        {
            get
            {
                if (_basketRepository == null)
                    _basketRepository = new EFRepository<Basket>(_db);
                return _basketRepository;
            }
        }

        public IRepository CustomerRepository
        {
            get
            {
                if (_customerRepository == null)
                    _customerRepository = new EFRepository<Customer>(_db);
                return _customerRepository;
            }
        }

        public void Save()
        {
            _db.SaveChanges();
        }

        private bool _disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this._disposed)
            {
                if (disposing)
                {
                    _db.Dispose();
                }
            }
            this._disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}

BikeStoreUnitOfWork contains a single instance of BikeStoreContext, and passes it into the repositories when they are utilized. By sharing this instance across repositories we can include changes to multiple entity types within a single transaction.

BikeStoreUnitOfWork implements the IDisposible interface, and ensures that the context is correctly disposed of.

We can now replace the code shown at the start of the article with the following –

using (var unitOfWork = new BikeStoreUnitOfWork())
{
    Product product = unitOfWork.ProductRepository.Get(p => p.ProductId == 1).First();
    product.Name = "CAAD 10";
    unitOfWork.Save();
}

This code can be included in a controller without the need for the controller to reference the System.Data.Entity assembly. The controller is persistence ignorant. It is no longer aware that Entity Framework is being used in the persistence layer. And we could change the implementation of BikeStoreUnit of work to use nHibernate or any other technology, without the need to change any code or references in the controller.

In the next post in this series I will present the Service Layer pattern, and demonstrate how we can use this to separate business logic from request processing/presentation logic. And how we can use dependency injection to inject concrete implementations of unit of work into the service classes, and concrete implementations of the service classes into the controllers. Hence fully decoupling the controllers from the service layer, and the service layer from the persistence layer. In the third and final post I will talk about how this helps us to isolate the controllers and service layers during unit testing.