Entity Framework 5.0 Generic Repository classes for CRUD operation with complex LINQ expression. Microsoft Visual Studio Test Tools Unit Testing

• Download TestDatabase.zip - 313.6 KB
• Download demo - 2.2 MB

Introduction

In most of the web application the common user interaction is always add/ Edit / delete and Search information with different predicates parameters. Generic classes can be reused while large scale development as it requires minimum effort from the developer side instead writing every piece of code from scratch. It is better to abstract a piece of code required before and after the processing of information from the user end.<o:p>

e.g. If I need to search an information from any screen in any page of my application I need to send search parameter, read search parameters and give it to real search query either linq or stored procedure and then take the result and process it to get required information only in a JSON or XML format instead of sending entire information back to the web page.<o:p>

In short either you need to make tea or coffee there is some steps which are common in both. The common part can be made as generic so that individual developer does not have to write it again and again for every page / screen.

Background

I came across a situation where most of my screen were having same functionality like whenever user navigates to any menu it always display  the existing information in grid with all CRUD operations in a single panel for end user. Here I start thinking what part can be abstracted in individual layer instead of only abstracting the data access layer.<o:p>

To utilize maximum benefits of pattern and practices and oops concept I commenced the designing of generic classes and it really minimize all my developers effort even though screen is a as simple as User Master screen or a very complex request form having lot of De-Normalized data in bootstrap or jQuery tabs.<o:p>

First generic class to interact with web page.  Like in MVC example HTML view should always interacts with a common Base controller and process the required request except the specific information which needs to be overridden by the developer’s controller.<o:p>

Second generic class to interact with data access layer.  Again during processing the information from the Data Access layer, Developers would have either a simple entity if it is a master screen or may be a complex entity model from a complex screen. so I decided to go for two types of Generic Repository classes on for simple master screen crud operation and other for complex model entity classes, So developer does not have to extract the required information for the particular entity from the complex model while performing CRUD operation, instead the repository class will take care of it using custom reflection and pass the required entity out of the complex entity to the Database to perform CRUD operation.<o:p>

In this article I am going to demonstrate only the part of Data Access Layer.  Next article would be coming as MVC sample in which we would be having Common generic base controller.<o:p>

User may require a simple Generic repository class to search (with or without parameters), Add, Update or Delete a record by passing a simple entity name (database table name) or maybe he has a complex model class which is combination of multiple entities like (customer class with department Information or Total orders etc. as it comes from screen using form collection)

Quote:

<o:p>Due to upload size constrains I have deleted all the dlls inside bin folder and packages. After downloading the source code you have to download the dlls using NUGate Package manager if it does not exist in your visual studio, or it would automatically download once you rebuild solution for the first time<o:p>

Using the code

Very first we need to create a new project ASP.NET MVC 4 Web Application and select project template as Internet application. Do not forget to include Unit test Project as we are going to test all these generic classes and methods using unit test classes, Name the solution as DemoGenericClasses. Build and run the solution it should be compiled and running smoothly. Add one more Project as Class Library and name it as DataModel Delete default class-1 file and start following the steps<o:p>

Step-1 Add new item as ADO.NET Entity Data Model name it as DemoEntities.edmx file select Generate from database option and create new connection, follow the steps as I have provided in my source code--> snapshots folder. Thanks to Microsoft that Entity Framework 5 Entity classes are no longer inherited from any Entity Object class. Now you are free to do the changes in the entity class as they are simple standalone partial classes.  <o:p>

Step-2 Create four folders in data Model layer to make it more structured way of development. Model Entities, Interfaces, Helper Classes and Repository Classes respectively. Each one will hold certain classes and interface as their name indicates.<o:p>

Step-3 Add Helper Classes Which We Would Be Using Various Places to Follow Specific Principle During Designing of Classes and Logical Blocks.<o:p>

.

Quote:

 Kindly Note That When You Add a Class Microsoft by Default Add Number of Usings Assemblies in Your Class Which Are Not Useful for the Logical Piece of Code You Have Written in This Class. Best Practice is to remove the Unused Using by Right Click on Your Class --> Organize Usings--> Remove Unused Usings

<o:p>

IndexedListItem to maintain the Primary key especially after Add operation if you need to update the existing entity with Identity column value. And also to maintain a fake index to maintain the serial no based on temporary collection which might be used during CRUD operation in a Grid.<o:p>

namespace DataModel.HelperClasses
{
    public class IndexedListItem
    {
        public int MyIndex { get; set; }

        public int PrimaryKey { get; set; }
    }
}

PredicateBuilder class to prepare the predicate using System.Linq.Expressions.Expression which would be useful while filter the entity collection based on user input.<o:p>

clsEntityBaseClass   class which would have some common properties like  when you are not using the default entity instead you would like to play with complex view model<o:p>

using System;

namespace DataModel.HelperClasses
{
    public class clsEntityBaseClass : IndexedListItem
    {
        public string CreatedBy { get; set; }

        public DateTime? CreatedDate { get; set; }

        public string ModifiedBy { get; set; }

        public DateTime? ModifiedDate { get; set; }

        public decimal? SortOrder { get; set; }
    }
}

To add additional attributes and logic we can inherit the partial classes generated with edmx file inside DemoEntities.tt from clsEntityBaseClass and to make T as restricted Type instead of having it only Type class.<o:p>

Simple Customer entity class inside DemoEntities.tt inherited from clsEntityBaseClass <o:p>

namespace DataModel
{
    using System;
    using System.Collections.Generic;
     // inherited the autogenerated class with our own custom class clsEntityBaseclass
    public partial class Customer:clsEntityBaseClass
    {
        public Customer()
        {
            this.Sales = new HashSet<Sale>();
        }
    
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public Nullable<System.DateTime> Timestamp { get; set; }
        public int DepartmentId { get; set; }
        public virtual ICollection<Sale> Sales { get; set; }
        public virtual Department Department { get; set; }
    }
}

Step-3 Create a complex Customer Model class which would be differ from Customer entity class as below

using DataModel.HelperClasses;
using System;
namespace DataModel
{
    public class clsCustomerModel : clsEntityBaseClass, IAggregateRoot
    {
        public int DepartmentId { get; set; }
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public Nullable<System.DateTime> Timestamp { get; set; }

        //Addtional attributes which are not part of customer class or table
        public int TotalOrders { get; set; }
        public string OverallRating { get; set; }

        public clsEntityBaseClass GetTableName
        {
            get { return new Customer(); }
        }

        public string EntitySetName
        {
            get { return "Customers"; }
        }
    }
}

Step-4 Now we are ready with all types of entities. Here we would be having two different generic classes one for simple customer type of entities and other for complex entities like customer Model class. In both we would be having same kind of methods like Add, Update, Delete, FindAll, FindById and SearchByparam with overloaded methods for Add and Delete etc.<o:p>

We would prefer to do the Interface oriented programming so none of our method until and unless it is not exposed to the outside world can be designed without interface.<o:p>

Step-5 Create two interfaces we would be giving a bit different method name in both generic classes so that it would be better to understand during Unit testing.<o:p>

IGenericRepository is an interface which would be having T as parameter of type simple entity class e.g. Customer class we would be implementing this interface in GenericRepository class<o:p>

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

namespace DataModel
{
    public interface IGenericRepository<T> where T : class
    {
        IEnumerable<T> GetAll();
        T GetByID(object id);
        IEnumerable<T> FilterByParam(Expression<Func<T, bool>> predicate);
        void Insert(T obj);    
        void Update(T obj);    
        void Delete(object id);    
        void Save();
    }
}

IGenericComplexRepository is second interface which would be having two generic parameter as T and M where<o:p>

T is any class which is inherited by IndexedListItem and also implemented IAggregateRoot interface which we would be using to get the name of entity class from complex model which needs to be added or updated or deleted. With at least one parameter less constructor <o:p>

M is again any class which is inherited from a base entity class clsEntityBaseClass<o:p>

using DataModel.HelperClasses;
using System.Collections.Generic;

namespace DataModel
{
    public interface IGenericComplexRepository<T, M>
        where T : IndexedListItem, IAggregateRoot, new()
        where M : clsEntityBaseClass
    {
        void Add(List<T> entityList);
        void Update(T entity);
        void Remove(T entity);
        void RemoveById(int Id);
        List<T> FindAll();
        T FindById(int id);

    }
}

So now we are almost ready to implement these generic interfaces with proper generic type of parameters<o:p>

Step-6 Create two Generic repository classes named as GenericComplexRepository and GenericRepository implementing the interfaces created in step-5 respectively.<o:p>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Entity;
using System.Data;
using DataModel.HelperClasses;
using System.Data.Entity.Validation;

namespace DataModel
{
    public abstract class GenericRepository<T> : IGenericRepository<T> where T : IndexedListItem, new()
    {
        internal DemoTestEntities objDemoTestEntities;
        internal DbSet<T> dbSet;

        public GenericRepository()
        {
            this.objDemoTestEntities = new DemoTestEntities();
            this.dbSet = objDemoTestEntities.Set<T>();
        }

        public IEnumerable<T> GetAll()
        {
            IQueryable<T> objT = dbSet;
            return objT.ToList();
        }

        public T GetByID(object id)
        {
            return dbSet.Find(id);
        }

        public IEnumerable<T> FilterByParam(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
        {
            return dbSet.Where(predicate).ToList();
        }

        public void Insert(T obj)
        {
            dbSet.Add(obj);
        }

        public void Update(T obj)
        {
            dbSet.Attach(obj);
            objDemoTestEntities.Entry(obj).State = EntityState.Modified;
        }

        public void Delete(object id)
        {
            T ObjT = dbSet.Find(id);
            if (ObjT != null)
            {
                Delete(ObjT);
            }

        }

        public virtual void Delete(T obj)
        {
            if (objDemoTestEntities.Entry(obj).State == EntityState.Detached)
            {
                dbSet.Attach(obj);
            }
            dbSet.Remove(obj);
        }

        public void Save()
        {
            try
            {
                objDemoTestEntities.SaveChanges();
            }

            catch (DbEntityValidationException e)
            {
                foreach (var eve in e.EntityValidationErrors)
                {
                    Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                        eve.Entry.Entity.GetType().Name, eve.Entry.State);
                    foreach (var ve in eve.ValidationErrors)
                    {
                        Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                            ve.PropertyName, ve.ErrorMessage);
                    }
                }
                throw;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public void Dispose()
        {
            if (objDemoTestEntities != null)
            {
                objDemoTestEntities.Dispose();
                objDemoTestEntities = null;
            }
        }

    }
}

For GenericComplexRepository I am not showing it over here as it has proper commenting and is very big in length as it uses reflection to extract the entity columns and place it in simple entity class which is mapped with DB table to perform CRUD operation on it. Now we are ready with generic classes. Now it’s time for developer to reuse classes as long as possible to perform different operation with different entities.<o:p>

Step-7 Create three different developer's repository classes which would be inherited from generic classes based on his / her requirement. Developer may have his own method as well along with generic Add, Update, delete, FindAll and Searchbyparam methods available from Generic classes. For most of the logical blocks developer does not have to write a single line of code.<o:p>

CustomerRepository class which indicates Generic Repository Class would be used with respect to Customer entity to perform CRUD operation on Customer table in Database<o:p>

namespace DataModel
{
    public class CustomerRepository : GenericRepository<Customer>
    {
    }
}

DepartmentRepository class which indicates Generic Repository Class would be used with respect to Department entity to perform CRUD operation on Department table in Database<o:p>

namespace DataModel

{
    public class DepartmentRepository : GenericRepository<Department>
    {
    }
}

clsCustomerModelRepository class which indicates Generic Complex Repository Class would be used with respect to clsCustomerModel entity to perform CRUD operation  on Customer table in Database<o:p>

namespace DataModel
{
    public class clsCustomerModelRepository : GenericComplexRepository<clsCustomerModel, Customer>
    {
    }
}

Step-8 It’s time to test these methods available in generic classes. Create Unit Test classes for respective developer's repository classes inside DemoGenerics.Tests project. Before adding new Unit Test classes do not forget to add the reference of DataModel Layer in it. clsCustomerModelTest, CustomerRepoTest ,DepartmentRepoTest one of the code snippet is<o:p>

Quote:

Kindly note that here I am pushing the actual result to expected result to get Assert.AreEqual(expected, actual); as true. Ideally we should have hardcoded values in expected object and actual would be the result returned by the target method.<o:p>

using System;
using DataModel;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace DemoGenerics.Tests
{
    [TestClass]
    public class CustomerRepoTest
    {
        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        [TestMethod()]
        [DeploymentItem("DataModel.dll")]
        public void GetAllCustomerTest()
        {
            CustomerRepository target = new CustomerRepository();
            List<Customer> expected = new List<Customer>();
            List<Customer> actual;

            actual = (List<Customer>)target.GetAll();
            expected = actual;
            Assert.AreEqual(expected, actual);
        }

        [TestMethod()]
        [DeploymentItem("DataModel.dll")]
        public void GetbyIdCustomerTest()
        {
            CustomerRepository target = new CustomerRepository();
            Customer expected = new Customer();
            Customer actual;
            object CustomerId = 1;

            actual = (Customer)target.GetByID(CustomerId);
            expected = actual;
            Assert.AreEqual(expected, actual);
        }

        [TestMethod()]
        [DeploymentItem("DataModel.dll")]
        public void FilterByParamCustomerTest()
        {
            //Prepare Search Criteria using Predicate Operators
            ParameterExpression d = Expression.Parameter(typeof(Customer), "cust");
            Expression pdFirstName = Expression.Equal(Expression.Property(d, "FirstName"), Expression.Constant("Murtuza"));
            Expression pdDepartmentId = Expression.Equal(Expression.Property(d, "DepartmentId"), Expression.Constant(2));
            Expression<Func<Customer, bool>> pdfinalPredicate = Expression.Lambda<Func<Customer, bool>>(
                Expression.Or(pdFirstName, pdDepartmentId), d);

            //Prepare Search Criteria using single Lambda Expression
            Expression<Func<Customer, bool>> pdfinalexpression = (u) => (u.FirstName.Contains("M") || u.DepartmentId.Equals(2));

            CustomerRepository target = new CustomerRepository();
            List<Customer> expected = new List<Customer>();
            List<Customer> actual;

            actual = (List<Customer>)target.FilterByParam(pdfinalPredicate);
            expected = actual;
            Assert.AreEqual(expected, actual);
        }

        [TestMethod()]
        [DeploymentItem("DataModel.dll")]
        public void InsertCustomerTest()
        {
            CustomerRepository target = new CustomerRepository();
            Customer expected = new Customer();
            Customer actual = new Customer();
            target.Insert(new Customer { FirstName = "Murtuza", LastName = "Patel", City = "Navi Mumbai", State = "MS", Zip = "410208", DepartmentId = 1 });
            target.Save();
            expected = actual;
            Assert.AreEqual(expected, actual);
        }

        [TestMethod()]
        [DeploymentItem("DataModel.dll")]
        public void UpdateCustomerTest()
        {
            CustomerRepository target = new CustomerRepository();
            Customer expected = new Customer();
            Customer actual = new Customer();

            target.Update(new Customer { CustomerID = 1, FirstName = "Murtuza", LastName = "Patel", City = "Navi Mumbai", State = "MS", Zip = "410208", DepartmentId = 3, Timestamp = Convert.ToDateTime("02-04-2015") });
            target.Save();
            expected = actual;
            Assert.AreEqual(expected, actual);
        }

        [TestMethod()]
        [DeploymentItem("DataModel.dll")]
        public void DeleteCustomerTest()
        {
            CustomerRepository target = new CustomerRepository();
            Customer expected = new Customer();
            Customer actual = new Customer();
            object CustomerId = 20;

            target.Delete(CustomerId);
            target.Save();
            expected = actual;
            Assert.AreEqual(expected, actual);
        }

    }
}

to prepare predicate there are many way of preapring the predicates to pass it in a geenric method to search an entity based on search criteria. I have added two ways and even added one more PredicateBuilderclass to achieve the same.

Points of Interest

Finally I achieved something to get blessing from developers saying I have developed my screen without writing a single line of code at least to perform CRUDE operation over data access layer. Unit Testing from Microsoft is nice tool to test you logical blocks without preparing any kind of HTML stuff. Unit testing is another benefit to make sure that all the methods written in generic classes are passed and will work fine after designing the HTML views for the respective developer’s repository classes<o:p>

History

will continue this article to write same type of generic controller class to interact with different HTML Views and passing the data to business layer