locked
Best way to implement entity framework RRS feed

  • Question

  • User713522837 posted

    I have been struggling to find the best way of implementing entity framework Database first for CRUD operations on a large application.
    There are about hundreds of tables and stored procedures in the database, I would need to build a Data Access Layer implementing entity framework
    which would be robust, maintainable, extensible and testable.

    I have gone through many urls trying to find the best approach.Found few links below implementing the basic functionality
    but I guess they are not testable as the DbContext is called in the controller or console application. Probably
    not suited for large applications

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/database-first-development/
    https://msdn.microsoft.com/en-us/library/jj200620(v=vs.113).aspx
    http://www.c-sharpcorner.com/article/working-with-entity-framework-using-database-first-approach/
    https://www.tutorialspoint.com/entity_framework/entity_database_first_approach.htm
    https://www.codeproject.com/Tips/869553/CRUD-operation-with-Entity-Framework-Database-Fi

    Then I found few approaches using Generic repository and unit of work pattern
    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application
    http://www.janholinka.net/Blog/Article/9
    http://rahulrajatsingh.com/2014/06/understanding-repository-and-unit-of-work-pattern-and-implementing-generic-repository-in-asp-net-mvc-using-entity-framework/

    But then again, there are suggestions by experts that Entity framework implements Repository and Unit of Work by default and should only use if the ORM is going to get changed later.
    If that's true, in my case ORM does not going to get changed.
    Some others suggest that IDbContext should be used as opposed to DbContext for testability.

    From all the research I did, I understood that I should use Repository and Unit of Work pattern with IDbContext.
    There are many approaches again on it and all of them without using IDbContext.
    If my approach is correct, could anyone please suggest a sample code where I can find an easiest way of implementing
    Repository and Unit Of Work Pattern with IDbContext.

    If my approach is outdated and there are best approaches in the market, could anyone please point me in the right direction with an example code

    Thursday, February 15, 2018 4:56 PM

All replies

  • User1120430333 posted

    I have been struggling to find the best way of implementing entity framework Database first for CRUD operations on a large application.
    There are about hundreds of tables and stored procedures in the database, I would need to build a Data Access Layer implementing entity framework
    which would be robust, maintainable, extensible and testable.

    If you are using a DAL. then you should be using the DTO pattern and not sending the EF entities past the DAL, which will require mapping of EF entity to DTO and vise versa. Using the DTO pattern allows for the abstraction away from the underlying ORM/database technology. You can put all the DTO(s) is a classlib project called Entities and set reference to the project's DLL for all projects that need to know about the DTO(s). As far as testing EF, then you should be doing integration testing against the DAL that is using EF. Because you are may be using sprocs then you may  need to use DTO(s) that do not match the EF entities as well.

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    If you have a large amount of tables, then you should have more than one EF model, which can be done in the DAL by pointing the EF wizard to create EF models in folders, namespace separation, within the DAL classlib project.  

    https://msdn.microsoft.com/en-us/library/hh949853(v=vs.113).aspx

    From all the research I did, I understood that I should use Repository and Unit of Work pattern with IDbContext.
    There are many approaches again on it and all of them without using IDbContext.
    If my approach is correct, could anyone please suggest a sample code where I can find an easiest way of implementing
    Repository and Unit Of Work Pattern with IDbContext.

    I don't bother with UOW pattern myself personally. I have used the Repository and DAO patterns together with the DAO working on a table per table/Ef entity per entity basis. I have also just used the DAL with no Repository pattern with the DAL using the DAO pattern

    http://blog.sapiensworks.com/post/2012/11/01/Repository-vs-DAO.aspx

    https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

     No where does it say that the Repository partner is solely used for CRUD operations with a DB, although database CRUD can be part of the Repository functionality in a non generic fashion.  The Repository functionality could be calling a service  as an example or calling another Repository object.  However, if you want to test DAL functionality, then the unit or integration tests through the Repository can be done.

    https://msdn.microsoft.com/en-us/library/ff649690.aspx

    https://archive.codeplex.com/?p=entitiestodtos

    DAL using DB first, DTO and DAO patterns.

    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;
    using System.Transactions;
    using Entities;
    using DAL.Model;
    
    namespace DAL
    {
        public class DaoProject : IDaoProject
        {
            public DtoProject GetProjectById(int id)
            {
                var dto = new DtoProject();
    
                using (var context = new ProjectMgmntEntities())
                {
                    var project = (context.Projects.Where(a => a.ProjectId == id)).SingleOrDefault();
    
                    if (project == null) return dto;
                    dto.ProjectId = project.ProjectId;
                    dto.ClientName = project.ClientName;
                    dto.ProjectName = project.ProjectName;
                    dto.Technology = project.Technology;
                    dto.ProjectType = project.ProjectType;
                    dto.UserId = project.UserID;
                    dto.StartDate = project.StartDate;
                    dto.EndDate = project.EndDate;
                    dto.Cost = project.Cost;
                }
    
                return dto;
            }
    
            public List<DtoProject> GetProjectsByUserId(string userid)
            {
                var dtos = new List<DtoProject>();
    
                using (var context = new ProjectMgmntEntities())
                {
    
                    dtos = (from a in context.Projects.Where(a => a.UserID.Contains(userid))
                        select new DtoProject
                        {
                            ProjectId = a.ProjectId,
                            ClientName = a.ClientName,
                            ProjectName = a.ProjectName,
                            Technology = a.Technology,
                            ProjectType = a.ProjectType,
                            UserId = a.UserID,
                            StartDate = a.StartDate,
                            EndDate = a.EndDate,
                            Cost = a.Cost
                        }).ToList();
                }
    
                return dtos;
            }
    
            public void CreateProject(DtoProject dto)
            {
                using (var context = new ProjectMgmntEntities())
                {
                    var project = new Project
                    {
                        ClientName = dto.ClientName,
                        ProjectName = dto.ProjectName,
                        Technology = dto.Technology,
                        ProjectType = dto.ProjectType,
                        UserID = dto.UserId,
                        StartDate = dto.StartDate,
                        EndDate = dto.EndDate,
                        Cost = dto.Cost
                    };
    
                    context.Projects.Add(project);
                    context.SaveChanges();
                }
            }
    
            public void UpdateProject(DtoProject dto)
            {
                var project = new Project();
    
                using (var context = new ProjectMgmntEntities())
                {
                    project = (context.Projects.Where(a => a.ProjectId == dto.ProjectId)).SingleOrDefault();
                }
    
                if (project != null)
                {
                    project.ClientName = dto.ClientName;
                    project.ProjectName = dto.ProjectName;
                    project.Technology = dto.Technology;
                    project.ProjectType = dto.ProjectType;
                    project.UserID = dto.UserId;
                    project.StartDate = dto.StartDate;
                    project.EndDate = dto.EndDate;
                    project.Cost = dto.Cost;
                }
    
                using (var dbcontext = new ProjectMgmntEntities())
                {
                    if (project == null) return;
                    dbcontext.Entry(project).State = EntityState.Modified;
                    dbcontext.SaveChanges();
                }
            }
    
            public void DeleteProject(int id)
            {
                Project project;
    
                using (var context = new ProjectMgmntEntities())
                {
                    project = (context.Projects.Where(a => a.ProjectId == id)).SingleOrDefault();
                }
    
                if (project == null) return;
    
                using (var newContext = new ProjectMgmntEntities())
                {
                    var tasks = new DaoTask().GetTasksByProjectId(project.ProjectId);
                    using (TransactionScope scope = new TransactionScope())
                    {
                        foreach (var task in tasks)
                        {
                            new DaoTask().DeleteTask(task.TaskId);
                        }
    
                        newContext.Entry(project).State = EntityState.Deleted;
                        newContext.SaveChanges();
    
                        scope.Complete();
                    }
                }
            }
        }
    }
    

    .

    Friday, February 16, 2018 5:46 PM