Auditable/Archivable with Generic Repository, UnitOfWork and a business logic layer.

Jul 2, 2012 at 3:24 PM
Edited Jul 2, 2012 at 3:38 PM

Hi,

I am pretty new to EF and am currently working with EF 4.3, I have added your EF.Patterns 0.6 via nuget as I am trying to implement an audit and repository function for my tables.

I currently use UnitOfWork with a generic repository pattern, but I also need to implement a business logic layer. As I have said I am quite new to this and so I could do with some guidance on how to structure these patterns together.

I am a little unsure what the various patterns are for such as unitOfWork, Repository/Generic Repository and Business logic. Currently I only have a Generic repository which performs functions such as GetByID, Delete, Create, Update and Get. But I also want to introduce some more specific funtions such as get User permission level for a specific function by passing user and function for example.

Would you include these specific types in in the generic repository class or create seperate ones, and is the repository pattern where you put these data access functions? What goes in to your business logic as this confuses me somewhat?

I need to be able to audit my tables and want to be able to check who has done a particular action on a particular record, what the action was and when it was performed. I also want to be able to look back at past revision of a record whether it has been Created, Updated or even deleted.

Here is some of my code, I was wonder if you could give me some guidance/examples of how to implement the Auditable/Archivable patterns to decorate my repositories and how I should go about further adding to my repositories/ Business logic layer.

using System;
using System.ComponentModel.DataAnnotations;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace PayPlate.Models
{
    public class PayrollSelection
    {
        public IEnumerable Payrolls { get; set; }
        public IEnumerable Users { get; set; }
        public IEnumerable Permissions { get; set; } 
    }

    public class PayrollIndexData
    {
        public IEnumerable Payrolls { get; set; }
        public IEnumerable Users { get; set; }
        public IEnumerable Functions { get; set; }
        public IEnumerable Permissions { get; set; } 
    }

    public class Payroll
    {
        public int PayrollID { get; set; }
        [Required(ErrorMessage = "Payroll title is required.")]
        [Display(Name = "Payroll")]
        [MaxLength(20)] 
        public string Title { get; set; }
        [Required(ErrorMessage = "C21 description is required.")]
        [Display(Name = "C21 Name")]
        [MaxLength(20)] 
        public string C21Name { get; set; }
        [Required(ErrorMessage = "Server name is required.")]
        [Display(Name = "Server Name")]
        [MaxLength(20)] 
        public string Server { get; set; }
        [Required(ErrorMessage = "Port is required.")]
        [Display(Name = "Port")]
        [MaxLength(5)] 
        public string Port { get; set; }
        [Required(ErrorMessage = "Connection string is required.")]
        [Display(Name = "Connection String")]
        [MaxLength(200)] 
        public string Connection { get; set; }
        public virtual ICollection Permissions { get; set; }
    }

    public class SelectedPayroll
    {
        public string UserName { get; set; }
        public int UserID { get; set; }
        public int PayrollID { get; set; }
        public string Title { get; set; }
    }

    public class PermissionsPayrollGrp
    {
        public string Payroll { get; set; }
        public int UserCount { get; set; }
    } 

    public class Function
    {
        public int FunctionID { get; set; }
        [Required(ErrorMessage = "Function description is required.")]
        [Display(Name = "Function")]
        [MaxLength(20)] 
        public string Title { get; set; }
        public virtual ICollection Permissions { get; set; }
    }

    public class Permission
    {
        public int PermissionID { get; set; }
        public int PayrollID { get; set; }
        public int FunctionID { get; set; }
        public int UserID { get; set; }
        [Range(typeof(double), "1", "5")]
        public double? Level { get; set; }
        public virtual Payroll Payroll { get; set; }
        public virtual Function Function { get; set; }
        public virtual User User { get; set; }
    }

    public class User
    {
        public int UserID { get; set; }
        public string NetLogin { get; set; }
        [Required(ErrorMessage = "Employee number is required.")]
        [Display(Name = "Employee Number")]
        [MaxLength(6)] 
        public string EmpNumber { get; set; }
        [Required(ErrorMessage = "First name is required.")]
        [Display(Name = "First Name")]
        [MaxLength(50)] 
        public string Forename { get; set; }
        [Required(ErrorMessage = "Last name is required.")]
        [Display(Name = "Last Name")]
        [MaxLength(50)] 
        public string Surname { get; set; }
        public bool Active { get; set; }
        public virtual ICollection Permissions { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace PayPlate.Models
{
    public class JobCode
    {
        public int JobCodeID { get; set; }
        public string Jobcode { get; set; }
        public int DivisionID { get; set; }
        public int AuthChainID { get; set; }
        public int JobID { get; set; }
        public int PensionID { get; set; }
        public int AnalysisGrpID { get; set; }
        public int CostGrpID { get; set; }
        public int ContractID { get; set; }
        public bool AuthJob { get; set; }
        public virtual Division Division { get; set; }
        public virtual AuthChain AuthChain { get; set; }
        public virtual Job Job { get; set; }
        public virtual Pension Pension { get; set; }
        public virtual AnalysisGrp AnalysisGrp { get; set; }
        public virtual CostGrp CostGrp { get; set; }
        public virtual Contract Contract { get; set; }
        public int Author { get; set; }
        public DateTime Created { get; set; }
        public string Action { get; set; }
    }

    public class Division
    {
        public int DivisionID { get; set; }
        public string Code { get; set; }
        public string Title { get; set; }
        public int Author { get; set; }
        public DateTime Created { get; set; }
        public string Action { get; set; }
    }

    public class AuthChain
    {
        public int AuthChainID { get; set; }
        public string RL01 { get; set; }
        public int RL01Emp { get; set; }
        public string RL02 { get; set; }
        public int RL02Emp { get; set; }
        public string RL03 { get; set; }
        public int RL03Emp { get; set; }
        public string RL04 { get; set; }
        public int RL04Emp { get; set; }
        public string RL05 { get; set; }
        public int RL05Emp { get; set; }
        public string RL06 { get; set; }
        public int RL06Emp { get; set; }
        public string RL07 { get; set; }
        public int RL07Emp { get; set; }
        public string RL08 { get; set; }
        public int RL08Emp { get; set; }
        public string RL09 { get; set; }
        public string RL09Emp { get; set; }
        public string RL10 { get; set; }
        public string RL10Emp { get; set; }
        public int Author { get; set; }
        public DateTime Created { get; set; }
        public string Action { get; set; }
    }

    public class Job
    {
        public int JobID { get; set; }
        public string Title { get; set; }
        public int Author { get; set; }
        public DateTime Created { get; set; }
        public string Action { get; set; }
    }

    public class Pension
    {
        public int PensionID { get; set; }
        public string Code { get; set; }
        public string Title { get; set; }
        public int Author { get; set; }
        public DateTime Created { get; set; }
        public string Action { get; set; }
    }

    public class AnalysisGrp
    {
        public int AnalysisGrpID { get; set; }
        public string Value { get; set; }
        public string Name { get; set; }
        public int Author { get; set; }
        public DateTime Created { get; set; }
        public string Action { get; set; }
    }

    public class CostGrp
    {
        public int CostGrpID { get; set; }
        public string Value { get; set; }
        public string Name { get; set; }
        public int Author { get; set; }
        public DateTime Created { get; set; }
        public string Action { get; set; }
    }

    public class Contract
    {
        public int ContractID { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public double Hours { get; set; }
        public double Weeks { get; set; }
        public int Author { get; set; }
        public DateTime Created { get; set; }
        public string Action { get; set; }
    } 
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace PayPlate.Models
{
    public class GLRule
    {
        public int GLRuleID { get; set; }
        public int ContractID { get; set; }
        public int DivisionID { get; set; }
        public int JobID { get; set; }
        public int AnalysisGrpID { get; set; }
        public int CostGrpID { get; set; }
        public virtual Contract Contract { get; set; }
        public virtual Division Division { get; set; }
        public virtual Job Job { get; set; }
        public virtual AnalysisGrp AnalysisGrp { get; set; }
        public virtual CostGrp CostGrp { get; set; }
    }
}

This is my db  context

using System;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using PayPlate.Models;

namespace PayPlate.DAL
{
    public class PayPlateContext : DbContext
    {
        public DbSet Users { get; set; }
        public DbSet Permissions { get; set; }
        public DbSet Payrolls { get; set; }
        public DbSet Functions { get; set; }
        public DbSet JobCodes { get; set; }
        public DbSet Divisions { get; set; }
        public DbSet AuthChains { get; set; }
        public DbSet Jobs { get; set; }
        public DbSet Pensions { get; set; }
        public DbSet AnalysisGrps { get; set; }
        public DbSet CostGrps { get; set; }
        public DbSet Contracts { get; set; }
        public DbSet GLRules { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove();
        }
    }
}

This is my unit of work

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PayPlate.Models;

namespace PayPlate.DAL
{
    public class UnitOfWork : IDisposable 
    { 
        private PayPlateContext context = new PayPlateContext();
        private GenericRepository userRepository;
        private GenericRepository permissionRepository; 
        private GenericRepository payrollRepository; 
        private GenericRepository functionRepository;
        private GenericRepository payrollIndexDataRepository;
        private GenericRepository payrollSelectionRepository;
        private GenericRepository gLRuleRepository;
        private GenericRepository contractRepository;
        private GenericRepository costGrpRepository;
        private GenericRepository analysisGrpRepository;
        private GenericRepository jobRepository;
        private GenericRepository divisionRepository;
        private GenericRepository authChainRepository;
        private GenericRepository jobCodeRepository;
        private GenericRepository pensionRepository;


        public GenericRepository GLRuleRepository
        {
            get
            {

                if (this.gLRuleRepository == null)
                {
                    this.gLRuleRepository = new GenericRepository(context);
                }
                return gLRuleRepository;
            }
        }

        public GenericRepository ContractRepository
        {
            get
            {

                if (this.contractRepository == null)
                {
                    this.contractRepository = new GenericRepository(context);
                }
                return contractRepository;
            }
        }

        public GenericRepository CostGrpRepository
        {
            get
            {

                if (this.costGrpRepository == null)
                {
                    this.costGrpRepository = new GenericRepository(context);
                }
                return costGrpRepository;
            }
        }

        public GenericRepository AnalysisGrpRepository
        {
            get
            {

                if (this.analysisGrpRepository == null)
                {
                    this.analysisGrpRepository = new GenericRepository(context);
                }
                return analysisGrpRepository;
            }
        }

        public GenericRepository JobRepository
        {
            get
            {

                if (this.jobRepository == null)
                {
                    this.jobRepository = new GenericRepository(context);
                }
                return jobRepository;
            }
        }

        public GenericRepository DivisionRepository
        {
            get
            {

                if (this.divisionRepository == null)
                {
                    this.divisionRepository = new GenericRepository(context);
                }
                return divisionRepository;
            }
        }

        public GenericRepository AuthChainRepository
        {
            get
            {

                if (this.authChainRepository == null)
                {
                    this.authChainRepository = new GenericRepository(context);
                }
                return authChainRepository;
            }
        }

        public GenericRepository JobCodeRepository
        {
            get
            {

                if (this.jobCodeRepository == null)
                {
                    this.jobCodeRepository = new GenericRepository(context);
                }
                return jobCodeRepository;
            }
        }

        public GenericRepository PensionRepository
        {
            get
            {

                if (this.pensionRepository == null)
                {
                    this.pensionRepository = new GenericRepository(context);
                }
                return pensionRepository;
            }
        }

        public GenericRepository PayrollSelectionRepository
        {
            get
            {

                if (this.payrollSelectionRepository == null)
                {
                    this.payrollSelectionRepository = new GenericRepository(context);
                }
                return payrollSelectionRepository;
            }
        }

        public GenericRepository PermissionRepository
        {
            get
            {

                if (this.permissionRepository == null)
                {
                    this.permissionRepository = new GenericRepository(context);
                }
                return permissionRepository;
            }
        }

        public GenericRepository UserRepository
        {
            get
            {

                if (this.userRepository == null)
                {
                    this.userRepository = new GenericRepository(context);
                }
                return userRepository;
            }
        } 
 
        public GenericRepository PayrollRepository 
        { 
            get 
            { 
 
                if (this.payrollRepository == null) 
                { 
                    this.payrollRepository = new GenericRepository(context); 
                } 
                return payrollRepository; 
            } 
        } 
 
        public GenericRepository FunctionRepository 
        { 
            get 
            { 
 
                if (this.functionRepository == null) 
                {
                    this.functionRepository = new GenericRepository(context); 
                }
                return functionRepository; 
            } 
        }

        public GenericRepository PayrollIndexDataRepository
        {
            get
            {

                if (this.payrollIndexDataRepository == null)
                {
                    this.payrollIndexDataRepository = new GenericRepository(context);
                }
                return payrollIndexDataRepository;
            }
        }
 
        public void Save() 
        { 
            context.SaveChanges(); 
        } 
 
        private bool disposed = false; 
 
        protected virtual void Dispose(bool disposing) 
        { 
            if (!this.disposed) 
            { 
                if (disposing) 
                { 
                    context.Dispose(); 
                } 
            } 
            this.disposed = true; 
        } 
 
        public void Dispose() 
        { 
            Dispose(true); 
            GC.SuppressFinalize(this); 
        } 
    } 
}

This is my Generic Repository

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Entity;
using PayPlate.Models;
using System.Linq.Expressions;

namespace PayPlate.DAL
{
    public class GenericRepository where TEntity : class
    {
        internal PayPlateContext context;
        internal DbSet dbSet;

        public GenericRepository(PayPlateContext context)
        {
            this.context = context;
            this.dbSet = context.Set();
        }

        public virtual IEnumerable Get(
            Expression> filter = null,
            Func, IOrderedQueryable> orderBy = null,
            string includeProperties = "")
        {
            IQueryable query = dbSet;

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

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

            if (orderBy != null)
            {
                return orderBy(query).ToList();
            }
            else
            {
                return query.ToList();
            }
        }

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

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

        public virtual void Delete(object id)
        {
            TEntity entityToDelete = dbSet.Find(id);
            Delete(entityToDelete);
        }

        public virtual void Delete(TEntity entityToDelete)
        {

            if (context.Entry(entityToDelete).State == EntityState.Detached)
            {
                dbSet.Attach(entityToDelete);
            }
            dbSet.Remove(entityToDelete);
        }

        public virtual void Update(TEntity entityToUpdate)
        {
            dbSet.Attach(entityToUpdate);
            context.Entry(entityToUpdate).State = EntityState.Modified;
        }
    }
}

And this is an example of one of my controllers:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using PayPlate.Models;
using PayPlate.DAL;
using PagedList;

namespace PayPlate.Controllers
{
    public class PermissionController : Controller
    {
        private UnitOfWork unitOfWork = new UnitOfWork(); 

        //
        // GET: /Permission/

        public ViewResult Index(string sortOrder, string currentFilter, string searchString, int? page)
        {
            //var permissions = db.Permissions.Include(p => p.Payroll).Include(p => p.Function).Include(p => p.User);
            //return View(permissions.ToList());

            ViewBag.CurrentSort = sortOrder;
            ViewBag.UserSortParm = String.IsNullOrEmpty(sortOrder) ? "User desc" : "";
            ViewBag.PaySortParm = String.IsNullOrEmpty(sortOrder) ? "Pay desc" : "Pay";
            ViewBag.FuncSortParm = String.IsNullOrEmpty(sortOrder) ? "Func desc" : "Func";
            ViewBag.LevelSortParm = sortOrder == "Level" ? "Level desc" : "Level";

            if (Request.HttpMethod == "GET")
            {
                searchString = currentFilter;
            }
            else
            {
                page = 1;
            }
            ViewBag.CurrentFilter = searchString;

            var rights = from s in unitOfWork.PermissionRepository.Get()
                        select s;
            if (!String.IsNullOrEmpty(searchString))
            {
                rights = rights.Where(s => s.User.NetLogin.ToUpper().Contains(searchString.ToUpper())
                                       || s.Payroll.Title.ToUpper().Contains(searchString.ToUpper())
                                       || s.Function.Title.ToUpper().Contains(searchString.ToUpper()));
            }

            switch (sortOrder)
            {
                case "User desc":
                    rights = rights.OrderByDescending(s => s.User.NetLogin);
                    break;
                case "Pay":
                    rights = rights.OrderBy(s => s.Payroll.Title);
                    break;
                case "Pay desc":
                    rights = rights.OrderByDescending(s => s.Payroll.Title);
                    break;
                case "Func":
                    rights = rights.OrderBy(s => s.Function.Title);
                    break;
                case "Func desc":
                    rights = rights.OrderByDescending(s => s.Function.Title);
                    break;
                case "Level":
                    rights = rights.OrderBy(s => s.Level);
                    break;
                case "Level desc":
                    rights = rights.OrderByDescending(s => s.Level);
                    break;
                default:
                    rights = rights.OrderBy(s => s.User.NetLogin);
                    break;
            }

            int pageSize = 10;
            int pageNumber = (page ?? 1);
            return View(rights.ToPagedList(pageNumber, pageSize)); 
        }

        //
        // GET: /Permission/Details/5

        public ViewResult Details(int id)
        {
            Permission permission = unitOfWork.PermissionRepository.GetByID(id);
            return View(permission);
        }

        //
        // GET: /Permission/Create

        public ActionResult Create()
        {
            ViewBag.PayrollID = new SelectList(unitOfWork.PayrollRepository.Get(), "PayrollID", "Title");
            ViewBag.FunctionID = new SelectList(unitOfWork.FunctionRepository.Get(), "FunctionID", "Title");
            ViewBag.UserID = new SelectList(unitOfWork.UserRepository.Get(), "UserID", "NetLogin");
            return View();
        } 

        //
        // POST: /Permission/Create

        [HttpPost]
        public ActionResult Create(Permission permission)
        {
            if (ModelState.IsValid)
            {
                var permissions = unitOfWork.PermissionRepository.Get(s => s.User.UserID == permission.UserID
                                                                    && s.PayrollID == permission.PayrollID
                                                                    && s.Function.FunctionID == permission.FunctionID);
                if (permissions.Count() == 0)
                {
                    unitOfWork.PermissionRepository.Insert(permission);
                    unitOfWork.Save();
                    return RedirectToAction("Index");
                }
            }

            ViewBag.PayrollID = new SelectList(unitOfWork.PayrollRepository.Get(), "PayrollID", "Title", permission.PayrollID);
            ViewBag.FunctionID = new SelectList(unitOfWork.FunctionRepository.Get(), "FunctionID", "Title", permission.FunctionID);
            ViewBag.UserID = new SelectList(unitOfWork.UserRepository.Get(), "UserID", "NetLogin", permission.UserID);
            return View(permission);
        }
        
        //
        // GET: /Permission/Edit/5
 
        public ActionResult Edit(int id)
        {
            Permission permission = unitOfWork.PermissionRepository.GetByID(id);
            ViewBag.PayrollID = new SelectList(unitOfWork.PayrollRepository.Get(), "PayrollID", "Title", permission.PayrollID);
            ViewBag.FunctionID = new SelectList(unitOfWork.FunctionRepository.Get(), "FunctionID", "Title", permission.FunctionID);
            ViewBag.UserID = new SelectList(unitOfWork.UserRepository.Get(), "UserID", "NetLogin", permission.UserID);
            return View(permission);
        }

        //
        // POST: /Permission/Edit/5

        [HttpPost]
        public ActionResult Edit(Permission permission)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    var permissions = unitOfWork.PermissionRepository.Get(s => s.User.UserID == permission.UserID
                                                                        && s.Payroll.PayrollID == permission.PayrollID
                                                                        && s.Function.FunctionID == permission.FunctionID
                                                                        && s.PermissionID != permission.PermissionID);

                    if(permissions.Count() == 0)
                    {
                        
                        unitOfWork.PermissionRepository.Update(permission);
                        unitOfWork.Save();
                        return RedirectToAction("Index");
                    }
                }
            }
            catch (DataException)
            {
                //Log the error (add a variable name after DataException) 
                ModelState.AddModelError("", "Unable to save changes to permission. Try again, and if the problem persists see your system administrator.");
            } 

            ViewBag.PayrollID = new SelectList(unitOfWork.PayrollRepository.Get(), "PayrollID", "Title", permission.PayrollID);
            ViewBag.FunctionID = new SelectList(unitOfWork.FunctionRepository.Get(), "FunctionID", "Title", permission.FunctionID);
            ViewBag.UserID = new SelectList(unitOfWork.UserRepository.Get(), "UserID", "NetLogin", permission.UserID);
            return View(permission);
        }

        //
        // GET: /Permission/Delete/5
 
        public ActionResult Delete(int id)
        {
            Permission permission = unitOfWork.PermissionRepository.GetByID(id);
            return View(permission);
        }

        //
        // POST: /Permission/Delete/5

        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {
            Permission permission = unitOfWork.PermissionRepository.GetByID(id);
            unitOfWork.PermissionRepository.Delete(permission);
            unitOfWork.Save();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            unitOfWork.Dispose();
            base.Dispose(disposing);
        }
    }
}

Thanks in advance for any help you can offer,

Andy