Blog

Implementing Generic Repository Pattern With Entity Framework

Introduction:

The blog guides you in implementing generic repository pattern with Entity Framework in applications developed on .Net Framework using Code First approach. At the end of the blog, you’ll get to know the different approaches of implementing the repository pattern.

 
About the Repository Pattern:

The repository patterns are intended to create an abstraction layer between the data access layer and the business logic layer of an application.  It restricts us to work directly with the data in the application and creates new layers for database operations, business logic, and the application’s UI. It is a data access pattern that prompts a more loosely coupled approach to data access. We create the data access logic in a separate class, or set of classes called a repository.

Implementation:

Before implementing any repository pattern approach, we first need to create an application. In this tutorial, we’ll use an ASP.Net MVC Web application. So, follow the following steps-

  1. Create an ASP.Net MVC application project say SampleAp.
  2. Install latest Entity Framework from Nuget.
  3. Suppose, we have a table in our database Employees with some basic information like- Name, EmployeeCode, Role, and Phone.
  4. Create a folder Models (if it’s not already created) under project SampleApp. Now, create a folder Entities under Models. Add following class Employee under this folder.

    Employee.cs:
    namespace SampleApp.Models
    {
        public class Employee
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string EmployeeCode { get; set; }
            public string Role { get; set; }
            public string Phone { get; set; }
        }
    }
  5. Create another folder EntityMapper under Models. Add following class EmployeeMapper under this folder.

    EmployeeMapper.cs:
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.ModelConfiguration;
    
    namespace SampleApp.Models
    {
        public class EmployeeMapper : EntityTypeConfiguration<Employee>
        {
            public EmployeeMapper()
            {
                ToTable("Employees");
    
                HasKey(x => x.Id)
                    .Property(x => x.Id)
                    .HasColumnName("ID")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    
                Property(x => x.EmployeeCode).HasColumnName("EmployeeCode");
                Property(x => x.Name).HasColumnName("Name");
                Property(x => x.Phone).HasColumnName("Phone");
                Property(x => x.Role).HasColumnName("Role");
            }
        }
    }

     

  6. Create a folder DataLayer under the project. Add following class DataContext under this folder.

    DataContext.cs:
    using SampleApp.Models;
    using System.Data.Entity;
    
    namespace SampleApp.DataLayer
    {
        public class DataContext : DbContext
        {
            public DataContext() : base("DataConnection") { }
    
            public DataContext(string connectionStringName) : base(connectionStringName) { }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Configurations.Add(new EmployeeMapper());
                base.OnModelCreating(modelBuilder);
            }
        }
    }

    Now, we’ve our project ready to implement the following two approaches. We’re going to take a look at them separately.

    Bad Approach: Implement a repository class for each entity type

    In the first approach, we’ll implement a repository class for each entity type. Suppose we have a table named Employees in the database and its corresponding entity class Employee in our application. For the Employee entity type, we'll create a repository interface IEmployeeRepository and a repository class EmployeeRepository.

    IEmployeeRepository.cs: 
    Here, we’ve added some basic methods required for CRUD operation in any application.

    using SampleApp.Models;
    using System.Linq;
    
    namespace SampleApp.DataLayer
    {
        public interface IEmployeeRepository
        {
            IQueryable<Employee> GetAll();
            Employee GetEmployee(int employeeId);
            void Add(Employee entity);
            void Delete(Employee entity);
            void Update(Employee entity);
            void Save();
        }
    }
    


    EmployeeRepository.cs:

    using SampleApp.Models;
    using System;
    using System.Data.Entity;
    using System.Linq;
    
    namespace SampleApp.DataLayer
    {
        public class EmployeeRepository : IEmployeeRepository
        {
            [ThreadStatic]
            private static EmployeeRepository _repository;
    
            private DataContext DataContext { get; set; }
    
            public static EmployeeRepository Instance
            {
                get
                {
                    return _repository ?? (_repository = new EmployeeRepository());
                }
            }
    
            private EmployeeRepository()
            {
                DataContext = new DataContext();
    
                DataContext.Configuration.LazyLoadingEnabled = true;
            }
    
            public IQueryable<Employee> GetAll()
            {
                return DataContext.Set<Employee>().AsQueryable();
            }
    
            public Employee GetEmployee(int employeeId)
            {
                return GetAll().FirstOrDefault(e => e.Id == employeeId);
            }
    
            public void Add(Employee employee)
            {
                DataContext.Set<Employee>().Add(employee);
            }
    
            public void Delete(Employee employee)
            {
                DataContext.Set<Employee>().Remove(employee);
            }
    
            public void Update(Employee employee)
            {
                DataContext.Entry<Employee>(employee).State = EntityState.Modified;
            }
    
            public void Save()
            {
                DataContext.SaveChanges();
            }
    
            public static void Dispose()
            {
                if (_repository == null)
                    return;
    
                _repository.DataContext.Dispose();
                _repository.DataContext = null;
                _repository = null;
            }
        }
    }
    

     

    Everything looks good so far in this approach, so why are we calling it a BAD approach?

    Problems with this approach-

      1. Code Redundancy: Suppose, we need to implement it for another entity say Department. We will need to make an interface cs and DepartmentRepository.cs. What this will do is it’ll add a lot of duplicate code in our application.
        Don’t repeat yourself which is a principle of software development aimed at reducing repetition of information of all kinds, especially useful in multi-tier architectures. 

      2. Partial Updates: Creating a repository class for each entity type could result in partial database updates. For example, suppose we need to update two different entity types as part of the same transaction. If each uses a separate database context instance, one might succeed and the other might fail. One way to minimize redundant code is to use a generic repository, and one way to ensure that all repositories use the same database context (and thus coordinate all updates).


    Best Approach:

    Now, we’re going to look at a better approach. In this approach, we are going to create a GenericRepository class. This class will be used by all the entities. Create a generic repository interface IRepository.cs.

    IRepository.cs:

    using System;
    using System.Linq;
    using System.Linq.Expressions;
    
    namespace Stylofie.DataLayer
    {
        interface IRepository
        {
            /// <summary>
            /// Gets all objects from database
            /// </summary>
            IQueryable<T> All<T>() where T : class;
    
            /// <summary>
            /// Gets objects from database by filter.
            /// </summary>
            /// <param name="predicate">Specified a filter</param>
            IQueryable<T> Filter<T>(Expression<Func<T, bool>> predicate, params string[] includes) where T : class;
    
            /// <summary>
            /// Find object by keys.
            /// </summary>
            /// <param name="keys">Specified the search keys.</param>
            T Find<T>(params object[] keys) where T : class;
    
            /// <summary>
            /// Find object by specified expression.
            /// </summary>
            /// <param name="predicate"></param>
            T Find<T>(Expression<Func<T, bool>> predicate) where T : class;
    
            /// <summary>
            /// 
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="t"></param>
            /// <param name="commit"></param>
            /// <returns></returns>
            T Insert<T>(T t, bool commit) where T : class;
    
            /// <summary>
            /// Delete the object from database.
            /// </summary>
            /// <param name="t">Specified a existing object to delete.</param>        
            int Delete<T>(T t) where T : class;
    
            /// <summary>
            /// Delete objects from database by specified filter expression.
            /// </summary>
            /// <param name="predicate"></param>
            /// <param name="commit"></param>
            int Delete<T>(Expression<Func<T, bool>> predicate, bool commit) where T : class;
    
            /// <summary>
            /// Update object changes and save to database.
            /// </summary>
            /// <param name="t">Specified the object to save.</param>
            /// <param name="commit"></param>
            int Update<T>(T t, bool commit) where T : class;
        }
    }
    

     

    Repository.cs:

    using System.Data.Entity.Infrastructure;
    using System.Data.Objects;
    using System.Text.RegularExpressions;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Entity;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Linq.Dynamic;
    using System.Web.Routing;
    using Stylofie.Library;
    using Stylofie.Models;
    
    namespace Stylofie.DataLayer
    {
        public class Repository : IRepository
        {
            [ThreadStatic]
            private static Repository _repository;
    
            private DataContext DataContext { get; set; }
    
            public static Repository Instance
            {
                get
                {
                    return _repository ?? (_repository = new Repository());
                }
            }
    
            private Repository()
            {
                DataContext = new DataContext();
    
                DataContext.Configuration.LazyLoadingEnabled = true;
            }
    
            private DbSet<T> DbSet<T>() where T : class
            {
                return DataContext.Set<T>();
            }
    
            public string GetTableName<T>() where T : class
            {
                var objectContext = ((IObjectContextAdapter)DataContext).ObjectContext;
                var sql = objectContext.CreateObjectSet<T>().ToTraceString();
                var regex = new Regex(@"FROM\s+(?<table>.+)\s+AS");
                var match = regex.Match(sql);
                return match.Groups["table"].Value;
            }
    
            public IQueryable<T> All<T>() where T : class
            {
                return DbSet<T>().AsQueryable();
            }
    
            public static IQueryable<T> Table<T>() where T : class
            {
                return Instance.DbSet<T>();
            }
    
            public IQueryable<T> Filter<T>(Expression<Func<T, bool>> predicate, params string[] includes) where T : class
            {
                var dbSet = DbSet<T>().Where(predicate).AsQueryable();
    
                if (includes == null || !includes.Any()) return dbSet;
    
                foreach (var include in includes)
                {
                    dbSet = dbSet.Include(include);
                }
                return dbSet;
            }
    
            public void Filter<T>(Expression<Func<T, bool>> predicate, PagedView<T> pagedView) where T : class
            {
                var sortExpression = string.Format("{0} {1}", pagedView.Sort ?? "Id", pagedView.SortDir ?? "ASC");
    
                var dbSet = DbSet<T>().Where(predicate).OrderBy(sortExpression).AsQueryable();
    
                pagedView.TotalRecords = dbSet.Count();
    
                pagedView.RecordIndex = ((pagedView.Page ?? 1) - 1) * Common.PageSize;
    
                pagedView.ResultSet = dbSet.Skip(pagedView.RecordIndex).Take(Common.PageSize);
            }
    
            public T Find<T>(params object[] keys) where T : class
            {
                return DbSet<T>().Find(keys);
            }
    
            public T Find<T>(Expression<Func<T, bool>> predicate) where T : class
            {
                return DbSet<T>().Where(predicate).FirstOrDefault();
            }
    
            public bool IsExists<T>(Expression<Func<T, bool>> predicate) where T : class
            {
                return DbSet<T>().Any(predicate);
            }
    
            public T Insert<T>(T entity, bool commit = true) where T : class
            {
                var newEntry = DbSet<T>().Add(entity);
    
                if (commit)
                {
                    DataContext.SaveChanges();
                }
    
                return newEntry;
            }
    
    
    
            public int Update<T>(T entity) where T : class
            {
                var entry = DataContext.Entry(entity);
                DbSet<T>().Attach(entity);
                entry.State = EntityState.Modified;
                return DataContext.SaveChanges();
            }
    
            public int Update<T>(T entity, bool commit) where T : class
            {
                var entry = DataContext.Entry(entity);
    
                var primaryKey = 0;
    
                var prop = entity.GetType().GetProperty("Id");
    
                if (prop != null)
                {
                    primaryKey = (int)prop.GetValue(entity, null);
                }
    
                if (entry.State != EntityState.Detached) return commit ? DataContext.SaveChanges() : 0;
    
                var currentEntry = DbSet<T>().Find(primaryKey);
    
                if (currentEntry != null)
                {
                    var attachedEntry = DataContext.Entry(currentEntry);
    
                    attachedEntry.CurrentValues.SetValues(entity);
                }
                else
                {
                    DbSet<T>().Attach(entity);
    
                    entry.State = EntityState.Modified;
                }
    
                return commit ? DataContext.SaveChanges() : 0;
            }
    
            public void DetachEntity<T>(T entity) where T : class
            {
                DataContext.Entry(entity).State = EntityState.Detached;
            }
    
            public int Delete<T>(T entity) where T : class
            {
                DbSet<T>().Remove(entity);
                return DataContext.SaveChanges();
            }
    
            public int Delete<T>(Expression<Func<T, bool>> predicate, bool commit = true) where T : class
            {
                var objects = Filter(predicate);
    
                foreach (var obj in objects)
                {
                    DbSet<T>().Remove(obj);
                }
    
                return commit ? DataContext.SaveChanges() : 0;
            }
    
            public int Count<T>(Expression<Func<T, bool>> predicate) where T : class
            {
                return DbSet<T>().Count(predicate);
            }
    
            public IEnumerable<T> ExecuteSqlQuery<T>(string commandText, params SqlParameter[] parameters) where T : struct
            {
                return DataContext.Database.SqlQuery<T>(commandText, parameters);
            }
    
            public int ExecuteSqlCommand(string commandText, params SqlParameter[] parameters)
            {
                return DataContext.Database.ExecuteSqlCommand(commandText, parameters);
            }
    
            public int ExecuteNonQuery(string commandText, CommandType commandType, object parameters)
            {
                var rowCount = 0;
                var cmd = DataContext.Database.Connection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                AddParameters(cmd, parameters);
                var isMyConnection = false;
                if (DataContext.Database.Connection.State != ConnectionState.Open)
                {
                    DataContext.Database.Connection.Open();
                    isMyConnection = true;
                }
                try
                {
                    rowCount = cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (isMyConnection)
                        DataContext.Database.Connection.Close();
                }
    
                return rowCount;
            }
    
            public DataSet ExecuteReader(string commandText, CommandType commandType, object parameters, params string[] resultSets)
            {
                var ds = new DataSet();
                var cmd = DataContext.Database.Connection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
    
                AddParameters(cmd, parameters);
    
    
                var isMyConnection = false;
                if (DataContext.Database.Connection.State != ConnectionState.Open)
                {
                    DataContext.Database.Connection.Open();
                    isMyConnection = true;
                }
                try
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        ds.Load(reader, LoadOption.OverwriteChanges, resultSets);
                    }
                }
                finally
                {
                    if (isMyConnection)
                        DataContext.Database.Connection.Close();
                }
                return ds;
            }
    
            public object ExecuteScalor(string commandText, CommandType commandType, object parameters, params string[] resultSets)
            {
                var cmd = DataContext.Database.Connection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                AddParameters(cmd, parameters);
                var isMyConnection = false;
                if (DataContext.Database.Connection.State != ConnectionState.Open)
                {
                    DataContext.Database.Connection.Open();
                    isMyConnection = true;
                }
                try
                {
                    return cmd.ExecuteScalar();
                }
                finally
                {
                    if (isMyConnection)
                        DataContext.Database.Connection.Close();
                }
                return null;
            }
    
            public List<T> ExecuteReader<T>(string commandText, CommandType commandType, object parameters) where T : class
            {
                var entityList = new List<T>();
                var cmd = DataContext.Database.Connection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
    
                AddParameters(cmd, parameters);
    
                var isMyConnection = false;
    
                if (DataContext.Database.Connection.State != ConnectionState.Open)
                {
                    DataContext.Database.Connection.Open();
    
                    isMyConnection = true;
                }
    
                try
                {
                    var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    
                    var schemaTable = dr.GetSchemaTable();
    
                    while (dr.Read())
                    {
                        var instance = Activator.CreateInstance<T>();
    
                        var isEmptyRow = true;
    
                        foreach (var property in typeof(T).GetProperties())
                        {
                            var sqlColName = property.GetCustomAttributes(typeof(SqlColumn), true).FirstOrDefault() as SqlColumn;
    
                            if (schemaTable != null && (sqlColName == null
                                                        || !schemaTable.Rows.OfType<DataRow>().Any(row => row["ColumnName"].ToString().Equals(sqlColName.Name)))) continue;
    
                            if (sqlColName != null && dr[sqlColName.Name] == DBNull.Value) continue;
    
                            if (sqlColName != null) property.SetValue(instance, dr[sqlColName.Name], null);
    
                            isEmptyRow = false;
                        }
    
                        if (!isEmptyRow)
                            entityList.Add(instance);
                    }
                    dr.Close();
                }
    
                finally
                {
                    if (isMyConnection)
                        DataContext.Database.Connection.Close();
                }
    
                return entityList;
    
            }
    
            public bool IsExists(string commandText, CommandType commandType, object parameters)
            {
                bool flag = false;
                var cmd = DataContext.Database.Connection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
    
                AddParameters(cmd, parameters);
    
                var isMyConnection = false;
    
                if (DataContext.Database.Connection.State != ConnectionState.Open)
                {
                    DataContext.Database.Connection.Open();
    
                    isMyConnection = true;
                }
    
                try
                {
                    var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    
                    var schemaTable = dr.GetSchemaTable();
    
                    if (dr.Read())
                    {
                        flag = true;
                    }
                    dr.Close();
                }
    
                finally
                {
                    if (isMyConnection)
                        DataContext.Database.Connection.Close();
                }
    
                return flag;
    
            }
    
            private static void AddParameters(System.Data.Common.DbCommand cmd, object parameters)
            {
                if (parameters == null) return;
    
                var paramDict = new RouteValueDictionary(parameters);
    
                if (paramDict.Count == 0) return;
    
                foreach (var item in paramDict)
                {
                    var parameter = cmd.CreateParameter();
                    parameter.ParameterName = "@" + item.Key;
                    parameter.Value = item.Value;
                    cmd.Parameters.Add(parameter);
                }
            }
    
            public void Commit()
            {
                DataContext.SaveChanges();
            }
    
            public static void Dispose()
            {
                if (_repository == null)
                    return;
    
                _repository.DataContext.Dispose();
                _repository.DataContext = null;
                _repository = null;
            }
        }
    }
    


    Below, I’ve listed some of the benefits of using a generic repository pattern-

      1. Removal of Code Duplicity: In this approach, we’ve not created separate repositories for each entity. By using this approach, we’ve avoided hundreds (or even thousands in case of large projects) of lines of duplicate code.
      2. Code Reusability: We can utilize this generic repository in any other project without any difficulty because it is independent of the project’s business logic.
      3. Simplified Unit Testing: It simplifies unit testing.

Conclusion:

In this blog, we've gone through both the approaches and analyzed their pros and cons. We know the better of the two approaches, i.e., using the generic repository pattern. We should use this approach on our projects be it small, medium-sized or large complex applications.