My blog about software development on the Microsoft® stack.

Implementing a generic data access layer using Entity Framework

This post is about how you can develop a generic data access layer (DAL) with full CRUD (Create, Read, Update and Delete) support using Entity Framework 5 with plain old CLR objects (POCOs) and short-lived contexts in a disconnected and stateless N-tier application.

Entity Framework (EF) is Microsoft’s recommended data access technology when building new .NET applications. It is an object-relational mapping framework (ORM) that enables developers to work with relational data using domain-specific objects without having to write code to access data from a database.

EF provides three options for creating the conceptual model of your domain entities, also known as the entity data model (EDM); database first, model first and code first. With both the model first and code first approaches the presumption is that you don’t have an existing database when you start developing the application and a database schema is created based on the model. As databases within enterprise environments are generally designed and maintained by database administrators (DBAs) rather than developers, this post will use the database first option where the EDM becomes a virtual reflection of a database or a subset of it.

Typically when you are doing database first development using EF you are targeting an already existing database but for testing and demo purposes you may of course generate a new one from scratch. There is a walkthrough on how you can create a local service-based database in Visual Studio 2012 available on MSDN here.

The database used in this example is a very simple one containing only the two tables listed below. There is a one-to-many relationship between the Department and Employee tables meaning an employee belongs to a single department and a department can have several employees.

CREATE TABLE [dbo].[Department] (
    [DepartmentId] INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([DepartmentId] ASC)
);

CREATE TABLE [dbo].[Employee] (
    [EmployeeId]   INT          IDENTITY (1, 1) NOT NULL,
    [DepartmentId] INT          NOT NULL,
    [FirstName]    VARCHAR (20) NOT NULL,
    [LastName]     VARCHAR (20) NOT NULL,
    [Email]        VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([EmployeeId] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId])
    REFERENCES [dbo].[Department] ([DepartmentId])
);

N-tier architecture

A large enterprise application will typically have one or more databases to store data and on top of this a data access layer (DAL) to access the database(s). On top of this there may be some repositories to communicate with the DAL, a business layer containing logic and classes representing the business domain, a service layer to expose the business layer to clients and finally some user interface application such as a WPF desktop application or an ASP.NET web application.

User interface layer
WPF / ASP.NET / Console App / WinRT / …
Service layer
WCF / ASMX / …
Business logic layer
Data access layer
EF / ADO.NET / …
Database
SQL Server / Oracle / MySql / …

Data access layer (DAL)

The DAL is simply a C# class library project where you define the model generated from the existing database along with the generic implementation for reading and modifying the data in the database. It is the only layer in the application that will actually know anything about and have any dependencies on EF. Any user interface code should only communicate with the service or business layer and don’t have any references to the DAL.

1. Start by creating a new class library project (Mm.DataAccessLayer) and add a new ADO.NET Entity Data Model to it. Choose the “Generate from database” option in the Entity Data Model wizard. The wizard lets you connect to the database and select the Department and Employee tables to be included in the model.

wizard2 Entity Data Model

Once the wizard has completed the model is added to your project and you are able to view it in the EF Designer. By default all generated code including the model classes for the Department and Employee entities sits in the same project.

Separating entity classes from EDMX

Again, in an enterprise level application where separation of concerns is of great importance you certainly want to have your domain logic and your data access logic in separate projects. In other words you want to move the generated model (Model.tt) to another project. This can easily be accomplished by following these steps:

2. Add a new class library project (Mm.DomainModel) to the solution in Visual Studio.
3. Open File Explorer (right-click on the solution in Visual Studio and choose the “Open Folder in File Explorer” option) and move the Model.tt file to the new project folder.
4. Back in Visual Studio, include the Model.tt file in the new project by clicking on the “Show All Files” icon at the top of the Solution Explorer and then right-click on the Model.tt file and choose the “Include In Project” option.
5. Delete the Model.tt file from the DAL project.
6. For the template in the new domain model project to be able to find the model you then need to modify it to point to the correct EDMX path. You do this by setting the inputFile variable in the Model.tt template file to point to an explicit path where to find the model:

const string inputFile = @"../Mm.DataAccessLayer/Model.edmx";

Once you save the file the entity classes should be generated in the domain model project. Note that if you make any changes to the model in the DAL project later on you are required to explicitly update your model classes. By right-click on the Model.tt template file and choose “Run Custom Tool” the entity classes will be regenerated to reflect the latest changes to the model.

7. As the context by default expects the entity classes to be in the same namespace, add a using statement for their new namespace to the Model.Context.tt template file in the DAL project:

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using Mm.DomainModel; <!-- Added -->
<#
if (container.FunctionImports.Any())
{
#>
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
<#
}
#>

8. Finally, you need to add a reference from the DAL project to the domain model project in order for it to compile.

DbContext

In an EF-based application a context is responsible for tracking changes that are made to the entities after they have been loaded from the database. You then use the SaveChanges method on the context to persist the changes back to the database.

By default EDMs created in Visual Studio 2012 generates simple POCO entity classes and a context that derives from DbContext and this is the recommended template unless you have a reason to use one of the others listed on MSDN here.

The DbContext class was introduced in EF 4.1 and provides a simpler and more lightweight API compared to the EF 4.0 ObjectContext. However it simply acts like a wrapper around the ObjectContext and if you for some reason need the granular control of the latter you can implement an extension method – extension methods enable you to “add” methods to existing types without creating a new derived type – to be able to convert the DbContext to an ObjectContext through an adapter:

using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;

namespace Mm.DataAccessLayer
{
    public static class DbContextExtensions
    {
        public static ObjectContext ToObjectContext(this DbContext dbContext)
        {
            return (dbContext as IObjectContextAdapter).ObjectContext;
        }
    }
}

Encapsulating data access into repositories

A repository is responsible for encapsulating the data access code. It sits between the DAL and the business layer of the application to query the data source for data and map this data to an entity class, and it also persists changes in the entity classes back to the data source using the context.

A repository typically implements an interface that provides a simple set of methods for the developer using the repository to code against. Using an interface the consumer doesn’t need to know anything about what happens behind the scenes, i.e. whether the DAL uses EF, another ORM or manually creating connections and commands to execute queries against a data source. Besides the abstraction it brings it’s also great if you are using dependency injection in your application.

By using a generic repository for querying and persisting changes for your entity classes you can maximize code reuse. Below is a sample generic interface which provides methods to query for all entities, specific entities matching a given where predicate and a single entity as well as methods for inserting, updating and removing an arbitrary number of entities.

9. Add the below interface named IGenericDataRepository to the Mm.DataAccessLayer project.

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

namespace Mm.DataAccessLayer
{
    public interface IGenericDataRepository<T> where T : class
    {
        IList<T> GetAll(params Expression<Func<T, object>>[] navigationProperties);
        IList<T> GetList(Func<T, bool> where, params Expression<Func<T, object>>[] navigationProperties);
        T GetSingle(Func<T, bool> where, params Expression<Func<T, object>>[] navigationProperties);
        void Add(params T[] items);
        void Update(params T[] items);
        void Remove(params T[] items);
    }
}

IList vs IQueryable

Note that the return type of the two Get* methods is IList<T> rather than IQueryable<T>. This means that the methods will be returning the actual already executed results from the queries rather than executable queries themselves. Creating queries and return these back to the calling code would make the caller responsible for executing the LINQ-to-Entities queries and consequently use EF logic. Besides, when using EF in an N-tier application the repository typically creates a new context and dispose it on every request meaning the calling code won’t have access to it and therefore the ability to cause the query to be executed. Thus you should always keep your LINQ queries inside of the repository when using EF in a disconnected scenario such as in an N-tier application.

Loading related entities

EF offers two categories for loading entities that are related to your target entity, e.g. getting employees associated with a department in this case. Eager loading uses the Include method on the DbSet to load child entities and will issue a single query that fetches the data for all the included entities in a single call. Each of the methods for reading data from the database in the concrete sample implementation of the IGenericDataRepository<T> interface below supports eager loading by accepting a variable number of navigation properties to be included in the query as arguments.

10. Add a new class named GenericDataRepository to the MM.DataAccessLayer project and implement the IGenericDataRepository<T> interface .

using Mm.DomainModel;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;

namespace Mm.DataAccessLayer
{
    public class GenericDataRepository<T> : IGenericDataRepository<T> where T : class
    {
        public virtual IList<T> GetAll(params Expression<Func<T, object>>[] navigationProperties)
        {
            List<T> list;
            using (var context = new Entities())
            {
                IQueryable<T> dbQuery = context.Set<T>();

                //Apply eager loading
                foreach (Expression<Func<T, object>> navigationProperty in navigationProperties)
                    dbQuery = dbQuery.Include<T, object>(navigationProperty);
                
                list = dbQuery
                    .AsNoTracking()
                    .ToList<T>();
            }
            return list;
        }

        public virtual IList<T> GetList(Func<T, bool> where, 
             params Expression<Func<T,object>>[] navigationProperties)
        {
            List<T> list;
            using (var context = new Entities())
            {
                IQueryable<T> dbQuery = context.Set<T>();
                
                //Apply eager loading
                foreach (Expression<Func<T, object>> navigationProperty in navigationProperties)
                    dbQuery = dbQuery.Include<T, object>(navigationProperty);

                list = dbQuery
                    .AsNoTracking()
                    .Where(where)
                    .ToList<T>();
            }
            return list;
        }

        public virtual T GetSingle(Func<T, bool> where,
             params Expression<Func<T, object>>[] navigationProperties)
        {
            T item = null;
            using (var context = new Entities())
            {
                IQueryable<T> dbQuery = context.Set<T>();
                
                //Apply eager loading
                foreach (Expression<Func<T, object>> navigationProperty in navigationProperties)
                    dbQuery = dbQuery.Include<T, object>(navigationProperty);

                item = dbQuery
                    .AsNoTracking() //Don't track any changes for the selected item
                    .FirstOrDefault(where); //Apply where clause
            }
            return item;
        }
        
        /* rest of code omitted */
    }
}

For example, here’s how you would call the GetAll method to get all departments with its employees included:

IGenericDataRepository<Department> repository = new GenericDataRepository<Department>();
IList<Department> departments = repository.GetAll(d => d.Employees);

With lazy loading related entities are loaded from the data source by EF issuing a separate query first when the get accessor of a navigation property is accessed programmatically.

Dynamic proxies

For EF to enable features such as lazy loading and automatic change tracking for POCO entities, it can create a wrapper class around the POCO entity at runtime. There is a certain set of rules that your entity classes need to follow to get this proxy behavior. To get the instant change tracking behavior every property must be marked as virtual. For the lazy loading to work, those related properties that you want to be lazily loaded must be marked as virtual and those who point to a set of related child objects have to be of type ICollection. There is a complete list of the requirements for POCO proxies to be created available on MSDN here if you want more information.

Disconnected entities

However, in an N-tier application entity objects are usually disconnected meaning they are not being tracked by a context as the data is fetched using one context, returned to the client where there is no context to track changes and then sent back to the server and persisted back to the database using another instance of the context. Looking at the code above, a new instance of the context will be created and disposed for each method call and the AsNoTracking extension method – also added in EF 4.1 – is used to tell the context not to track any changes which may result in better performance when querying for a large number of entities. When using short-lived contexts like this, you should disable lazy loading. If you don’t an exception saying the context has been disposed will be thrown whenever a non-initialized navigation property is accessed from anywhere outside the context’s scope.

11. Lazy loading and dynamic proxy creation is turned off for all entities in a context by setting two flags on the Configuration property on the DbContext as shown below. Both these properties are set to true by default.

namespace Mm.DataAccessLayer
{
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using Mm.DomainModel;
    
    public partial class Entities : DbContext
    {
        public Entities()
            : base("name=Entities")
        {
            Configuration.LazyLoadingEnabled = false;
            Configuration.ProxyCreationEnabled = false;
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public DbSet<Department> Departments { get; set; }
        public DbSet<Employee> Employees { get; set; }
    }
}

Root vs Graphs

When it comes to persisting changes to the database you need to decide whether your CUD methods should accept an entire graph of entities or only a single root entity to be passed in. A graph of entities is a number of entities that reference each other. For example, when you want to insert a new Department entity to the database by passing it to the repository’s Add method it might have related Employee objects. In this case the Employee objects belong to the graph and the Department object is the root entity.

EntityState

On the server side, things will get easier if you decide to not support graphs. In this case you could expose an Add method and an Update method for each entity type and these methods would only operate on a standalone instance rather than a graph of entities. EF makes it simple to implement these methods. It is all about setting the state of the passed in entity object. An entity can be in one of five states as defined by the System.Data.EntityState enumeration:

Added: the entity is being tracked by the context but hasn’t been added to the database yet.

Unchanged: the entity is being tracked by the context, it exists in the database but its property values have not been changed since it was fetched from the database.

Modified: the entity is being tracked by the context, it exists in the database and some or all of its property values have been modified since it was fetched from the database

Deleted: the entity is being tracked by the context, it exists in the database but will be deleted on the next call to the SaveChanges method.

Detached: the entity is not being tracked by the context at all.

When the context’s SaveChanges method is called it decides what to do based on the entity’s current state. Unchanged and detached entities are ignored while added entities are inserted into the database and then become Unchanged when the method returns, modified entities are updated in the database and then become Unchanged and deleted entities are deleted from the database and then detached from the context.

DbSet.Entry

You can explicitly change the state of an entity by using the DbSet.Entry method. There is no need to attach the entity to the context before using this method as it will automatically do the attachment if needed. Below is the implementation of the generic repository’s Add method. It explicitly sets the state of the entity to be inserted into the database to Added before calling SaveChanges to execute and commit the insert statement.

public virtual void Add(params T[] items)
{
    using (var context = new Entities())
    {
        foreach (T item in items)
        {
            context.Entry(item).State = System.Data.EntityState.Added;
        }
        context.SaveChanges();
    }
}

The implementation for the Update and Remove methods are very similar to the Add method as shown below. Note that all exception handling has been omitted for brevity in the sample code.

public virtual void Update(params T[] items)
{
    using (var context = new Entities())
    {
        foreach (T item in items)
        {
            context.Entry(item).State = System.Data.EntityState.Modified;
        }
        context.SaveChanges();
    }
}

public virtual void Remove(params T[] items)
{
    using (var context = new Entities())
    {
        foreach (T item in items)
        {
            context.Entry(item).State = System.Data.EntityState.Deleted;
        }
        context.SaveChanges();
    }
}

Also note that all methods have been marked as virtual. This allows you to override any method in the generic repository by adding a derived class in cases where you need some specific logic to apply only to a certain type of entity. To be able to extend the generic implementation with methods that are specific only to a certain type of entity, whether it’s an initial requirement or a possible future one, it’s considered a good practice to define a repository per entity type from the beginning. You can simply inherit these repositories from the generic one as shown below and add methods to extend the common functionality based on your needs.

12. Add interfaces and classes to represent specific repositories for the Department and Employee entities to the DAL project.

using Mm.DomainModel;

namespace Mm.DataAccessLayer
{
    public interface IDepartmentRepository : IGenericDataRepository<Department>
    {
    }

    public interface IEmployeeRepository : IGenericDataRepository<Employee>
    {
    }

    public class DepartmentRepository : GenericDataRepository<Department>, IDepartmentRepository
    {
    }

    public class EmployeeRepository : GenericDataRepository<Employee>, IEmployeeRepository
    {
    }
}

Business layer

As mentioned before, the repository is located somewhere between the DAL and the business layer in a typical N-tier architecture. The business layer will use it to communicate with the database through the EDM in the DAL. Any client application will be happily unaware of any details regarding how data is fetched or persisted on the server side. It’s the responsibility of the business layer to provide methods for the client to use to communicate with the server.

13. Add a new project (Mm.BusinessLayer) to the solution with references to the DAL project (Mm.DataAccessLayer) and the project with the domain classes (Mm.DomainModel). Then add a new interface and a class implementing this interface to it to expose methods for creating, reading, updating and deleting entities to any client application.

Below is a sample implementation. In a real world application the methods in the business layer would probably contain code to validate the entities before processing them and it would also be catching and logging exceptions and maybe do some caching of frequently used data as well.

using Mm.DomainModel;
using System.Collections.Generic;
using Mm.DataAccessLayer;

namespace Mm.BusinessLayer
{
    public interface IBusinessLayer
    {
        IList<Department> GetAllDepartments();
        Department GetDepartmentByName(string departmentName);
        void AddDepartment(params Department[] departments);
        void UpdateDepartment(params Department[] departments);
        void RemoveDepartment(params Department[] departments);

        IList<Employee> GetEmployeesByDepartmentName(string departmentName);
        void AddEmployee(Employee employee);
        void UpdateEmploee(Employee employee);
        void RemoveEmployee(Employee employee);
    }

    public class BuinessLayer : IBusinessLayer
    {
        private readonly IDepartmentRepository _deptRepository;
        private readonly IEmployeeRepository _employeeRepository;

        public BuinessLayer()
        {
            _deptRepository = new DepartmentRepository();
            _employeeRepository = new EmployeeRepository();
        }

        public BuinessLayer(IDepartmentRepository deptRepository,
            IEmployeeRepository employeeRepository)
        {
            _deptRepository = deptRepository;
            _employeeRepository = employeeRepository;
        }

        public IList<Department> GetAllDepartments()
        {
            return _deptRepository.GetAll();
        }

        public Department GetDepartmentByName(string departmentName)
        {
            return _deptRepository.GetSingle(
                d => d.Name.Equals(departmentName), 
                d => d.Employees); //include related employees
        }

        public void AddDepartment(params Department[] departments)
        {
            /* Validation and error handling omitted */
            _deptRepository.Add(departments);
        }

        public void UpdateDepartment(params Department[] departments)
        {
            /* Validation and error handling omitted */
            _deptRepository.Update(departments);
        }

        public void RemoveDepartment(params Department[] departments)
        {
            /* Validation and error handling omitted */
            _deptRepository.Remove(departments);
        }

        public IList<Employee> GetEmployeesByDepartmentName(string departmentName)
        {
            return _employeeRepository.GetList(e => e.Department.Name.Equals(departmentName));
        }

        public void AddEmployee(Employee employee)
        {
            /* Validation and error handling omitted */
            _employeeRepository.Add(employee);
        }

        public void UpdateEmploee(Employee employee)
        {
            /* Validation and error handling omitted */
            _employeeRepository.Update(employee);
        }

        public void RemoveEmployee(Employee employee)
        {
            /* Validation and error handling omitted */
            _employeeRepository.Remove(employee);
        }
    }
}

Client

A client application consuming the sever side code will only need references to the business layer and the entity classes defined in the Mm.DomainModel project. Below is a simple C# console application to test the functionality provided by the business layer. It’s important to note that there are no references or dependencies to EF in this application. In fact you could replace the EF-based DAL with another one using raw T-SQL commands to communicate with the database without affecting the client side code. The only thing in the console application that hints that EF may be involved is the connection string that was generated in the DAL project when the EDM was created and has to be added to the application’s configuration file (App.config). Connection strings used by EF contain information about the required model, the mapping files between the model and the database and how to connect to the database using the underlying data provider.

14. To be able to test the functionality of the business layer and the DAL, create a new console application and add references to the Mm.BusinessLayer project and the Mm.DomainModel project.

using Mm.BusinessLayer;
using Mm.DomainModel;
using System;
using System.Collections.Generic;

namespace Mm.ConsoleClientApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            IBusinessLayer businessLayer = new BuinessLayer();
            
            /* Create some departments and insert them to the database through the business layer */
            Department it = new Department() { Name = "IT" };
            Department sales = new Department() { Name = "Sales" };
            Department marketing = new Department() { Name = "Marketing" };
            businessLayer.AddDepartment(it, sales, marketing);

            /* Get a list of departments from the database through the business layer */
            Console.WriteLine("Existing departments:");
            IList<Department> departments = businessLayer.GetAllDepartments();
            foreach (Department department in departments)
                Console.WriteLine(string.Format("{0} - {1}", department.DepartmentId, department.Name));

            
            /* Add a new employee and assign it to a department */
            Employee employee = new Employee()
            {
                FirstName = "Magnus",
                LastName = "Montin",
                DepartmentId = it.DepartmentId
            };
            businessLayer.AddEmployee(employee);
            
            /* Get a single department by name */
            it = businessLayer.GetDepartmentByName("IT");
            if (it != null)
            {
                Console.WriteLine(string.Format("Employees at the {0} department:", it.Name));
                foreach (Employee e in it.Employees)
                    Console.WriteLine(string.Format("{0}, {1}", e.LastName, e.FirstName));
            };

            /* Update an existing department */
            it.Name = "IT Department";
            businessLayer.UpdateDepartment(it);

            /* Remove employee */
            it.Employees.Clear();
            businessLayer.RemoveEmployee(employee);
            
            /* Remove departments*/
            businessLayer.RemoveDepartment(it, sales, marketing);

            Console.ReadLine();
        }
    }
}

Output

Persisting disconnected graphs

While avoiding the complexity of accepting graphs of objects to be persisted at once makes life easier for server side developers, it potentially makes the client component more complex. As you may have noticed by looking at the code for the business layer above, you are also likely to end up with a large number of operations exposed from the server. If you do want your business layer to be able to handle graphs of objects to passed in and be persisted correctly, you need a way of determining what changes were made to the passed in entity objects in order for you to set their states correctly.

For example, consider a scenario when you get a Department object representing a graph with related Employee objects. If all entities in the graph are new, i.e. are not yet in the database, you can simply call the DbSet.Add method to set the state of all entities in the graph to Added and call the SaveChanges to persist the changes. If the root entity, the Department in this case, is new and all related Employee objects are unchanged and already existing in the database you can use the DbSet.Entry method to change the state of the root only. If the root entity is modified and some related items have also been changed, you would first use the DbSet.Entry method to set the state of the root entity to Modified. This will attach the entire graph to the context and set the state of the related objects to Unchanged. You will then need to identify the related entities that have been changed and set the state of these to Modified too. Finally, you may have a graph with entities of varying states including added ones. The best thing here is to use the DbSet.Add method to set the states of the related entities that were truly added to Added and then use the DbSet.Entry method to set the correct state of the other ones.

So how do you know the state of an entity when it comes from a disconnected source and how do you make your business layer able to persist a graph with a variety of objects with a variety of states? The key here is to have the entity objects track their own state by explicitly setting the state on the client side before passing them to the business layer. This can be accomplished by letting all entity classes implement an interface with a state property. Below is a sample interface and an enum defining the possible states.

namespace Mm.DomainModel
{
    public interface IEntity
    {
        EntityState EntityState { get; set; }
    }

    public enum EntityState
    {
        Unchanged,
        Added,
        Modified,
        Deleted
    }
}
/* Entity classes implementing IEntity */
public partial class Department : IEntity
{
    public Department()
    {
        this.Employees = new HashSet<Employee>();
    }
    
    public int DepartmentId { get; set; }
    public string Name { get; set; }
    
    public virtual ICollection<Employee> Employees { get; set; }

    public EntityState EntityState { get; set; }
}

public partial class Employee : IEntity
{
    public int EmployeeId { get; set; }
    public int DepartmentId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    
    public virtual Department Department { get; set; }

    public EntityState EntityState { get; set; }
}

With this solution, the business layer will know the state of each entity in a passed in graph assuming the states have been set correctly in the client application. The repository will need a helper method to convert the custom EntityState value to a System.Data.EntityState enumeration value. The below static method can be added to the GenericDataRepository<T> class in the DAL to takes care of this.

protected static System.Data.EntityState GetEntityState(Mm.DomainModel.EntityState entityState)
{
    switch (entityState)
    {
        case DomainModel.EntityState.Unchanged:
            return System.Data.EntityState.Unchanged;
        case DomainModel.EntityState.Added:
            return System.Data.EntityState.Added;
        case DomainModel.EntityState.Modified:
            return System.Data.EntityState.Modified;
        case DomainModel.EntityState.Deleted:
            return System.Data.EntityState.Deleted;
        default:
            return System.Data.EntityState.Detached;
    }
}

Next, you need to specify a constraint on the IGenericDataRepository<T> interface and the GenericDataRepository<T> class to ensure that the type parameter T implements the IEntity interface and then make some modifications to the CUD methods in the repository as per below. Note that the Update method will actually be able to do all the work now as it basically only sets the System.Data.EntityState of an entity based on the value of the custom enum property.

public interface IGenericDataRepository<T> where T : class, IEntity { ... }
public virtual void Add(params T[] items)
{
    Update(items);
}

public virtual void Update(params T[] items)
{
    using (var context = new Entities())
    {
        DbSet<T> dbSet = context.Set<T>();
        foreach (T item in items)
        {
            dbSet.Add(item);
            foreach (DbEntityEntry<IEntity> entry in context.ChangeTracker.Entries<IEntity>())
            {
                IEntity entity = entry.Entity;
                entry.State = GetEntityState(entity.EntityState);
            }
        }
        context.SaveChanges();
    }
}

public virtual void Remove(params T[] items)
{
    Update(items);
}

Also note they key to all this working is that the client application must set the correct state of an entity as the repository will be totally dependent on this. Finally, below is some client side code that shows how to set the state of entities and passing a graph of objects to the business layer.

using Mm.BusinessLayer;
using Mm.DomainModel;
using System;
using System.Collections.Generic;

namespace Mm.ConsoleClientApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            IBusinessLayer businessLayer = new BuinessLayer();
            
            /* Create a department graph with two related employee objects */
            Department it = new Department() { Name = "IT" };
            it.Employees = new List<Employee> 
            { 
                new Employee { FirstName="Donald", LastName="Duck", EntityState=EntityState.Added },
                new Employee { FirstName="Mickey", LastName="Mouse", EntityState=EntityState.Added }
            };
            it.EntityState = EntityState.Added;
            businessLayer.AddDepartment(it);

            /*Add another employee to the IT department */
            Employee employee = new Employee()
            {
                FirstName = "Robin",
                LastName = "Hood",
                DepartmentId = it.DepartmentId,
                EntityState = EntityState.Added
            };
            /* and change the name of the department */
            it.Name = "Information Technology Department";
            it.EntityState = EntityState.Modified;
            foreach (Employee emp in it.Employees)
                emp.EntityState = EntityState.Unchanged;
            it.Employees.Add(employee);
            businessLayer.UpdateDepartment(it);

            /* Verify changes by quering for the updated department */
            it = businessLayer.GetDepartmentByName("Information Technology Department");
            if (it != null)
            {
                Console.WriteLine(string.Format("Employees at the {0} department:", it.Name));
                foreach (Employee e in it.Employees)
                    Console.WriteLine(string.Format("{0}, {1}", e.LastName, e.FirstName));
            };

            /* Delete all entities */
            it.EntityState = EntityState.Deleted;
            foreach (Employee e in it.Employees)
                e.EntityState = EntityState.Deleted;
            businessLayer.RemoveDepartment(it);

            Console.ReadLine();
        }
    }
}

Output


157 Comments on “Implementing a generic data access layer using Entity Framework”

  1. Hi Magnus, can I ask how you handle updates to your model as doing so recreates the entity classes and you lose your additions such as the IEntity base and EntityState property.
    I’m using a merge tool to backup the models folder and merge the differences back in after I rebuild my model but was wondering if there was a better way.
    Otherwise, this was just what I was looking for and am loving using this approach for my projects.

    Thanks

  2. I had the exact same question as Brian Hambley. You lose IEntity in your model every time it is regenerated.

  3. Sven says:

    Hi Magnus,

    Thanks for your article. I’ve learned a lot!
    But one question:
    I can see from the SQL Profiler, that the GetList function query in Mm.DataAccessLayer, does not present the where clause as a part of the SQL-query. The where clause is probably provided on the IQuerable – dbQuery – (?) causing that the result from the SQL-query is way too large and that queries takes too much time, specially on large databases. Do you have a suggestion how to change this behavior?

    Thank you.

  4. Hi Sven,

    Please see my previous answer to Vitaliy from October 9, 2013.

  5. Brian and Matthew: You could just create another partial class that contains all additional code that was not generated from the database, e.g.:

    public partial class Department : IEntity
    {
    public EntityState EntityState { get; set; }
    }

  6. Thanks Magnus, I don’t know why I didn’t think of this. Many Thanks

  7. Anonymous says:

    How would you handle the case in which you have a modified entity Department that contains a new Employee? I find that simply using the Entry method to set Department to modified throws an exception due to the Employee object which has a PK of zero.

  8. Hi,

    You need to set the EntityState of the new Employee object to Added as shown in the last client side code sample.

  9. Thanks for your reply. I should have been more clear. What I am implementing is a root-only repository. In that case then, am I to understand that the client should have saved the new Employee before updating the modified Department? I have a scenario where the modified Department is udpated while the new Employee is in its nav property collection but before that Employee object has been persisted to the DB.

  10. Having implemented and worked with the above implementation for a few days now, one of the things you cannot do with this repository is use nested includes, such as GetList(x => x.Departments.Select(d => d.Employees)); This will throw an exception.

  11. J says:

    I just upgraded to EF6.0.2 – I’ve gotten everything to compile, but my app just gives up in the middle of the GetSingle call:

    public virtual T GetSingle(Func where, params Expression<Func>[] navigationProperties)
    {
    T item = null;

    using (var context = new Entities())
    {
    IQueryable dbQuery = context.Set();

    foreach (Expression<Func> navigationProperty in navigationProperties)
    dies here —> dbQuery = dbQuery.Include(navigationProperty);

    item = dbQuery.AsNoTracking().FirstOrDefault(where);
    }

    return item;
    }

    No error/exception. It is attempting to lookup a User while force loading the Firm navprop. The Include call fails and I’m dropped back in the call stack leaving my user at a login screen.

    Reading up on migrating to EF6, I don’t see anything obvious that needs to change. Has anyone here used this with EF 6.X?

    Thanks.

    J

  12. mphillips says:

    Hi Magnus,

    Could you please add an example of how to execute stored procedure using the dbContext? similar to

    IEnumerable travelHistory =
    this.Context.Database.SqlQuery(“EXEC spTS_History_Travel_Get {0}, {1}”, startDate, endDate).ToList();

    Thanks,
    mphillips

  13. Hi mphillips,

    Please see my answer to Jerrico from 2013/08/10 regarding how to execute a stored procedure and map the results to a custom type when you are using a DbContext.

  14. Nic says:

    Hi Magnus,

    Thanks for this nice article.
    I have just one question, in your Updtae fonction you add the entity to the dbSet (dbSet.Add(item);). So when you update your departement after adding an Employee, an new departement will be duplicated. Am I right?

  15. Bruno Almeida says:

    Hi Magnus,

    Could you provide this sample solution?

  16. Nic says:

    Hi,
    Some of my entities weren’t inherit from IEntity, so I add some problems with object’s state, and the ones I wanted to be updated were added. Once the inheritance was right, there was no more problems, sorry.

  17. mphillips says:

    Hi, I’m not able to get the Generic repo to work with a query like this:

    IList list = EmployeeRepository.GetList(
    d => d.FullName.StartsWith(“Ros”) &&
    d.Active.Equals(true)
    );

    if I replace StartsWith with Equals and add the full name it works fine. How can I get the startswith to work?

    Thanks

  18. Robert Langdon says:

    Hi, this was one of the best blog posts I’ve ever seen. It was very useful to me and with the help of you and your article I learn lots of things. Thank you so much.
    p.s. I got many errors applying these steps but none of them was because of your codes. Building many class libraries made it difficult because by default, the models connection string does not allow to work on many projects. But one little change in connection string in both client’s and DAL’s App.config file handled it( =>for those who has the same trouble.)

  19. Simply wish to say your article is as astounding. The clarity to
    your put up is just cool and that i can suppose you’re an expert on this subject.
    Well together with your permission let me to take hold of your
    feed to keep up to date with drawing close post.
    Thanks 1,000,000 and please carry on the rewarding work.

  20. Ibrar says:

    Hi Magnus, I was hoping you could help answer a question I have posted on stack overflow, all about entity state tracking and EF6 and Code First?

    You can find it here: http://stackoverflow.com/questions/22737186/is-there-a-way-of-preventing-ef6-from-reverting-a-property-on-an-entity-post-sav

  21. Julien says:

    Hi Magnus,
    Thank you for this awesome post. It is really great. Well explained, with examples, alternatives, step by step, etc. I wish there was more useful content like that on the net.
    Im’ going to test those layers. See ya !

  22. Believe2014 says:

    You can also download my project on Codeplex. I have a working example that you can copy.
    https://entityinterfacegenerator.codeplex.com/

    It helps generate the interfaces you need for creating mocks and stubs in unit tests.
    You can also use it as Data Contract and share it with other projects.

  23. Julien says:

    Hi Magnus,

    There is an issue of context. Let’s say I’ve loaded an Employee emp1 and it is bounded to Department 2.
    If I do: emp1.DepartmentId = 3; then xxx.Update(emp1);
    Then when saving context there wil be an error because the navigation property emp1.Department is still with the old value and old entity. I have to set the navigation property to null so I can save the change:
    emp1.DepartmentId = 3;
    emp1.Department = null; // so entity does not throw an exception
    xxx.Update(emp1);

    Do you have any clean solution ?

  24. fmalik55 says:

    your business layer is passing lambda expressions to the data layer. so the business layer knows that the underline data layer is somewhat an EF or similar ORM. what if the data layer was actually a set of classes that had sql queries or stored proc names embedded in the GetSingle, ListAll etc methods. in that case, passing the lambda expressions won’t make sense and if we change the implmentations of the data layer, we will have to re-write the business layer and the client layer. great article otherwise.

  25. Hi fmalik 55,

    No, the business layer doesn’t expose any method that accepts an Expression<Func>[] as argument.

    However, the IGenericDataRepository interface (and consequently the IDepartmentRepository and IEmployeeRepository interfaces) which is implemented in the data access layer does. If you were to replace the implementation of the IGenericDataRepository interface with another class that uses raw T-SQL commands to communicate with the database, this class would then be responsible for executing the appropriate T-SQL statements to be able to return the entity classes filled with the requested navigation properties.

    However, replacing the GenericDataRepository class with another class in the DAL would not change the API of the business layer in any way and you wouldn’t need to change a single line of code in the client.

    Also, a Expression<Func> doesn’t have any dependency upon Entity Framework.

  26. Walter says:

    Hi Magnus,

    I loved the tutorial, one of the best I’ve read. It really helped me to understand separation of concerns using EF. One problem I am having…I have created a winforms project that has two DataGridView’s in a master-detail scenario. Problem is I can’t get IList categories to pass “Products to the other DataGridView. Here is the Code:

    private void Form1_Load(object sender, EventArgs e)
    {

    IBusinessLayer businessLayer = new BusinessLayer();

    IList categories = businessLayer.GetAllCategories();

    categoryBindingSource.DataSource = categories;

    productBindingSource.DataSource = categoryBindingSource;
    productBindingSource.DataMember = “Products”;

    categoryDataGridView.DataSource = categoryBindingSource;
    productDataGridView.DataSource = productBindingSource;

    }

    I am still very new. Is this a limitation with IList or because GetAll() doesn’t return “Products” with the List list? Thanks!

  27. Walter says:

    Magnus,

    Never mind, I’m a newb. The GetAll() function called inside of GetAllCategories was the issue.

    return _categoryRepository.GetAll(c => c.Products);
    instead of
    return _categoryRepository.GetAll();

    fixed my issue. Thanks for the Tutorial!

  28. Andy Lippitt says:

    Nice work!

    Not too sure about the types of the where params though. That they are Func instead of Expression<Func> seems to make them be executed in memory against the entire dataset, rather than transformed into SQL.

  29. Kevin says:

    7. As the context by default expects the entity classes to be in the same namespace, add a using statement for their new namespace to the Model.Context.tt template file in the DAL project:

    using System; using System.Data.Entity; using System.Data.Entity.Infrastructure; using Mm.DomainModel; using System.Data.Objects; using System.Data.Objects.DataClasses; using System.Linq;

    Wrong!!! You instruct the reader to add the DAL object as a reference in the Domain object then you instruct the reader to implement a using statement in the Model.Context.tt template file for an object that is not a reference in the DAL object.

    That will never compile.

    Once adding the DAL object as a reference in the Domain object you cannot add the Domain object as a reference in the DAL object, this causes a circular reference which is not permitted by the language construct and will never compile.

    Also, in order to have a .tt file with EF6.1 you have to add a POCO Code Generation Item.

  30. Hi Kevin,

    If you bother to read the entire article it should be pretty clear that the entity classes are moved to their own project (Mm.DomainModel) and namespace and you then need to add a reference to this new project from the DAL project where the template (Model.Context.tt) file is defined. The Mm.DomainModel project does of course not has a reference back to the DAL project. Where did you get this from?

    And of course I have compiled the sample code myself and it works just fine. It certainly not contains any circular references.

  31. Hi Andy,

    You are correct. Please see my comment to Vitaly from October 9th 2013 for more information about this.

  32. Kevin says:

    I got past the issue by adding the DomainModel.as a reference in the DataModel. I am using ADO.NET POCO Entity Objects qith WCF support. Requirement specs. I like the pattern, it is just a bear trying to implement it. I am working with a database where everything is either a Guid, Int32 identity or Varbinary(MAX). I wrote the encryption model and since we are a federally regulated company, I am not allowed to use stored procedures, inline sql or views. I can use linq to entity only and all encryption / decryption can only occur at the client. Whole lot of fun.

  33. Kevin says:

    Hey Mangus,

    Little bit stuck here. I have EF 6.1.0 installed in the DAL. When I try to call the GetAll() method from the BusinessLayer I get this:

    {“Could not load file or assembly ‘EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)”:”EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″}

    Any suggestions would be greatly appreciated.

  34. Kevin says:

    Here is a trick for everyone,

    In your Model.tt file, perform these edits so you won’t lose your changes when you have to regen.

    public string EntityClassOpening(EntityType entity)
    {
    return string.Format(
    CultureInfo.InvariantCulture,
    “{0} {1}partial class {2}{3} : IEntity”, <== Add the IEntity interface here
    Accessibility.ForType(entity),
    _code.SpaceAfter(_code.AbstractOption(entity)),
    _code.Escape(entity),
    _code.StringBefore(" : ", _typeMapper.GetTypeName(entity.BaseType)));
    }

    Add this below the public string NavigationProperty(NavigationProperty navigationProperty) method

    public string EntityProperty()
    {
    return string.Format(CultureInfo.InvariantCulture, "{0}", "public EntityState EntityState { get; set; }");
    }

    Add this after the NavigationProperty foreach loop in

    <== Add the Property here
    }
    <#
    EndNamespace(code);
    }

  35. Kevin says:

    With some modification and a little advanced encryption, all is working just fine.
    Nice pattern. As a side note, I created a set of templates to generate this pattern dynamically for any schema.

    Thx

  36. Helio says:

    Hi Magnus,

    I’m attempting to build a demo based on the concepts you’ve outlined in the article.

    You mentioned:
    “A client application consuming the server side code will only need references to the business layer and the entity classes defined in the Mm.DomainModel project.
    It’s important to note that there are no references or dependencies to EF in this application.
    The only thing in the console application that hints that EF may be involved is the connection string that was generated in the DAL project when the EDM was created and has to be added to the application’s configuration file (App.config). Connection strings used by EF contain information about the required model, the mapping files between the model and the database and how to connect to the database using the underlying data provider.

    The question that I have is the following: If there are no references or dependencies to EF how do you code the information about the EDMX model in the connection string in the client App.Config file? If there are no references to the EF how do you handle the metadata EF files (csdl, ssdl and msl) which are embedded in the DAL assembly? I understand you’ve copied the .tt files to the model assemby but what do you do with the EDMX metadata?

    The error that I have at the moment is “Unable to load specified metadata” which is related to the connection string in the App.Config file in my client app.

  37. fmalik55 says:

    I keep getting: An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception.

    foreach (DbEntityEntry entry in context.ChangeTracker.Entries()) <– This loop has all entry's status set to Added, even though I am setting the status of entries as Modified. If I set a status to Modified then when I check the ChangeTracker, the entity that I am updating is in the loop twice: Once with state Modified and other with state Added. All of its linked properties (entities) have status Added too. Any clue?

  38. fmalik55 says:

    To elloborate, the context.ChangeTracker.Entries() shows all of the entities that are changed and those that are unchanged and their state is set to Added for some reason…

  39. Per says:

    In the disconnected solution with a local entity state, the Update method used for adding, updating and deleting entieties in the database is using the DBContext.Add method to attach entities to the context subsequently setting the entity framework entity state in the changetracker. This imposes a problem, since the Add method tries to work out the added entitys child foreign key relations when an entity is added, which is not the desired behavior in update and delete operations and in some cases this will cause the SaveChanges call to fail with a 0 rows affected error.However, if you use the DBContext.Attach method as opossed to the DBContext.Add method on the entities to be updated, it all works fine :-)

  40. fmalik55 says:

    @Per, Db.Attach doesn’t update the record…

  41. Pedro Cabrita says:

    hello,
    tks for the great article. I’m trying to use this approach and so far is working great until I try to remove an array of entities:

    On the GenericDataRepository in the Remove method, I get an exception on the following line:

    (…)
    foreach (T item in items)
    {
    context.Entry(item).State = System.Data.EntityState.Deleted; <<<<<<<<<<<< ERROR
    }
    context.SaveChanges();
    (…)

    The error is:

    "An object with the same key already exists in the ObjectStateManager.
    The ObjectStateManager cannot track multiple objects with the same key."

    I search but I'm unable to find a solution that work with this pattern…

    Someone have a hint how to solve this problem ?
    thank you ..

  42. Cody says:

    Hi Magnus

    I was wondering with this, how difficult it would be to write tests and mock out the database. Since, there is no overloaded constructor taking a DBConext object, would this be hard to mock out? Should you just create some sort of wrapper class for the generic repository? Thanks

  43. mcalex says:

    In the DbContextExtensions class, I had to change “using System.Data.Objects;” to “using System.Data.Entity.Core.Objects;” to avoid Error: Cannot implicitly convert type ‘System.Data.Entity.Core.Objects.ObjectContext’ to System.Data.Objects.ObjectContext’

  44. J says:

    I’m running into an issue with large binary columns. I am storing files in the DB. When I request a single file and it hits GetSingle in the repo, it wants to load all of the files and then filter on the client side. Is there a way to have EF incorporate the where clause in the call so I only pull back the one file that I want?

    My GetSingle definition is identical to the one you have in your post. The app just hangs here:

    item = dbQuery.AsNoTracking().FirstOrDefault(where);

    And eventually times out.

    Thanks.

    J

  45. Julian50 says:

    “J” I get the same problem.

    The getSingle is not executing all the query in server side…

    According to this link:
    http://stackoverflow.com/questions/13390126/how-can-i-use-a-predicatet-in-an-ef-where-clause

    The error is about the where clause type
    replace
    THIS :
    public virtual T GetSingle(Func where,
    params Expression<Func>[] navigationProperties)

    BY
    public virtual T GetSingle(Expression<Func> where,
    params Expression<Func>[] navigationProperties)

    “Func where” => “Expression<Func> where”

    missing just the word expression… ;)

    This Post still great !! thanks Magnus!!

  46. Hi J and Julian50,

    Please see my previous answer to Vitaliy from October 9 2013 regarding this.

  47. J says:

    Thanks, Julian, Vitaliy and Magnus… I thought I had read something about it before, but I just missed it when I scanned the history.

    J

  48. Julian50 says:

    Hi Magnus,

    oups sorry… To much history comments ;)
    But Maybe you should udpate your post, because I don’t see any advantage to filter in the memory instead server side. More you use the database power, more efficient you are right ? for those raisons:
    – Database more efficient to do query (it is his job ;))
    – Less data to send from database.

    Are you agree with this ?

  49. Julian50 says:

    Hi Magnus,

    One more comments to share what I added to your code.

    The probleme is when you use your code in a transactionscope. If an exception occure, with the entity notracking you don’t have automatique rollback( don’t know why). So for example if you have one exception in the first update, all following update will raise an exception aswell, because the first didn’t roll back.

    so according to this : http://stackoverflow.com/questions/5466677/undo-changes-in-entity-framework-entities

    I used the rollback function:

    public void RollBack()
    {
    var context = DataContextFactory.GetDataContext();
    var changedEntries = context.ChangeTracker.Entries().Where(x => x.State != EntityState.Unchanged).ToList();

    foreach (var entry in changedEntries.Where(x=>x.State == EntityState.Modified))
    {
    entry.CurrentValues.SetValues(entry.OriginalValues);
    entry.State = EntityState.Unchanged;
    }

    foreach (var entry in changedEntries.Where(x=>x.State == EntityState.Added))
    {
    entry.State = EntityState.Detached;
    }

    foreach (var entry in changedEntries.Where(x=>x.State == EntityState.Deleted))
    {
    entry.State = EntityState.Unchanged;
    }

    }

    and modify Add, Update and Delete functions like this:

    private T Add(T iEntity) where T : class
    {
    try
    {
    RepositoryDbContext.Entry(iEntity).State = EntityState.Added;
    RepositoryDbContext.SaveChanges();
    return iEntity;
    }
    catch (Exception ex)
    {
    RollBack();
    throw ex;
    }
    }

    I’m a beginner, so magnus are you agree with this ??
    thx

  50. First I would like to thank you for the awesome post, it really helped a lot, more than you can imagine.

    I was thinking of implementing a join function in the GenericDataRepository, so that I can join one or more tables with my T type Entity, something like this:

    public void Join(K otherClass, Func whereClause) where K : class
    {
    IQueryable dbQuery = Entity.Set();
    IQueryable dbQuery2 = Entity.Set();

    var x = from rows in dbQuery
    join cols in dbQuery2 on //some bla bla
    }

    Is this is even doable?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s