locked
DB context in .NET core RRS feed

  • General discussion

  • We have one legacy application in ASP .net 2.0 and Sql server. We had used ADO .Net For Database access.
    Now were planning to re-write the application. We have chosen ASP.net Core with Entity Framework Core as data access technology for Web API.

    The existing database have more than 800 tables. We do want to continue using  the same database. 
    We have Generated entity for DbContext and entity types for our database by using Scaffold-DbContext.

    Shall we go for single DB context for all the tables in the database or should we have different DBContexts for different modules(Same table might be used more than one module)?

    Or there is any better approach for our scenario? Please guide me.

    Tuesday, May 14, 2019 2:13 AM

All replies

  • The existing database have more than 800 tables. We do want to continue using  the same database. 

    We have Generated entity for DbContext and entity types for our database by using Scaffold-DbContext.

    IMHO, that's a performance hit no matter how you look at it every time the WebAPI is accessed by a WebAPI client program dealing with 800 model objects that must be initialized in dealing with model instancing each time a client program wants to access the WebAPI and use the database. 

    We have chosen ASP.net Core with Entity Framework Core as data access technology for Web API.

    Maybe you need to look into microservices.

    https://docs.microsoft.com/en-us/dotnet/standard/microservices-architecture/multi-container-microservice-net-applications/data-driven-crud-microservice

    https://docs.microsoft.com/en-us/dotnet/standard/microservices-architecture/microservice-ddd-cqrs-patterns/microservice-application-layer-web-api-design

    https://docs.microsoft.com/en-us/dotnet/standard/microservices-architecture/microservice-ddd-cqrs-patterns/microservice-application-layer-implementation-web-api

    Shall we go for single DB context for all the tables in the database or should we have different DBContexts for different modules(Same table might be used more than one module)?

    IMHO, you need to segregate the context into several Repositories using the Repository pattern  or Data Access Objects using Data Access Object pattern. 

    Tuesday, May 14, 2019 9:50 AM
  • Thanks for reply.

    Can you please share sample of Repository pattern  and  Data Access Object pattern. 

    Wednesday, May 15, 2019 9:59 AM
  • Thanks for reply.

    Can you please share sample of Repository pattern  and  Data Access Object pattern. 

    I have used the Repository pattern in its usage in Domain Driven Design, but I am not a fan of the generic Repository. But since I mentioned it, and you requested it, I'll give you links on it.

    https://code-maze.com/net-core-web-development-part4/

    https://martinfowler.com/eaaCatalog/repository.html

    https://programmingwithmosh.com/net/common-mistakes-with-the-repository-pattern/

    https://www.thereformedprogrammer.net/is-the-repository-pattern-useful-with-entity-framework-core/

    However, if you do use the Repository pattern, then try to implement the Repository by domain principles.

    https://programmingwithmosh.com/net/common-mistakes-with-the-repository-pattern/

    https://blog.sapiensworks.com/post/2012/03/05/The-Generic-Repository-Is-An-Anti-Pattern.aspx

    http://www.bradoncode.com/blog/2013/08/repository-vs-domain-model-vs-data.html

    The pattern I use behind the WebAPI is the DAO and DTO patterns.

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

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    https://en.wikipedia.org/wiki/Data_access_object

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

    The generic repository and in general a repository pattern sitting behind the WebAPI is anemic of any business logic, which is the true purpose of even using the Repository pattern in DDD, and the Repository object just turns into a glorified DAO.

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

    The DAO objects can communicate with each other and the Web API and the WebAPI client know about the DTO(s), becuase both the client and WebAPI have reference to a classlib project called Entitles where the DTO(s) are kept. You also see the Delete() in DaoProject use the Delete() in DaoTask which is doing a cascade delete.

    There can be more that one EF Model with DBcontexts in project folders in the DAL that implements namespace seperation as well as more than one WebAPI service can be used. The usage of the DTO(s) decouples the services and also DTO(s) allows the client-side code to decouple from even knowing that EF is even involved becuase all it sees is the DTO(s).  

    Example.....

    using System.Collections.Generic;
    using DAL;
    using Entities;
    using Microsoft.AspNetCore.Mvc;
    
    namespace ProgMgmntCore2Api.Controllers
    {
        [Produces("application/json")]
        [Route("api/[controller]")]
        [ApiController]
    
        public class ProjectController : ControllerBase, IProjectController
        {
            private readonly IDaoProject _daoProject;
    
            public ProjectController(IDaoProject daoProject)
            {
                _daoProject = daoProject;
            }
    
            [HttpGet]
            [Route("GetProjById")]
            public DtoProject GetProjectById(int id)
            {
                return  _daoProject.GetProjectById(id);
            }
            
            [HttpGet]
            [Route("GetProjsByUserId")]
            public List<DtoProject> GetProjectsByUserId(string userid)
            {
                return _daoProject.GetProjectsByUserId(userid);
            }
    
            [HttpPost]
            [Route("CreateProject")]
            public void Post_CreateProject(DtoProject dto)
            {
                _daoProject.CreateProject(dto);
            }
    
            [HttpPost]
            [Route("DeleteProject")]
            public void Post_DeleteProject(DtoId dto)
            {
                _daoProject.DeleteProject(dto.Id);
            }
    
            [HttpPost]
            [Route("UpdateProject")]
            public void Post_UpdateProject(DtoProject dto)
            {
                _daoProject.UpdateProject(dto);
            }
        }
    }
    

    using System;
    using System.Collections.Generic;
    using System.Text;
    using Entities;
    
    namespace DAL
    {
        public interface IDaoProject
        {
            DtoProject GetProjectById(int id);
            List<DtoProject> GetProjectsByUserId(string userid);
            void CreateProject(DtoProject dto);
            void UpdateProject(DtoProject dto);
            void DeleteProject(int id);
        }
    }
    
    ==========================================================
    
    using System.Collections.Generic;
    using System.Linq;
    using System.Transactions;
    using DAL.Models.DB;
    using Entities;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Options;
    
    namespace DAL
    {
        public class DaoProject :IDaoProject
        {
            private readonly IOptions<ConnectionStrings> _options;
            
            public DaoProject(IOptions<ConnectionStrings> options)
            {
                _options = options;
            }
    
            public DtoProject GetProjectById(int id)
            {
                var dto = new DtoProject();
    
                using (var context = new ProjectManagementContext(_options))
                {
                    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 ProjectManagementContext(_options))
                {
                    
                    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 ProjectManagementContext(_options))
                {
                    var project = new Projects
                    {
                        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 Projects();
                
                using (var context = new ProjectManagementContext(_options))
                {
                   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 ProjectManagementContext(_options))
                {
                    if (project == null) return;
                    dbcontext.Entry(project).State = EntityState.Modified;
                    dbcontext.SaveChanges();
                }
            }
    
            public void DeleteProject(int id)
            {
                Projects project;
    
                using (var context = new ProjectManagementContext(_options))
                {
                   project = (context.Projects.Where(a => a.ProjectId == id)).SingleOrDefault();
                }
    
                if (project == null) return;
    
                using (var newContext = new ProjectManagementContext(_options))
                {
                   
                    var tasks = new DaoTask(_options).GetTasksByProjectId(project.ProjectId);
                    using (TransactionScope scope = new TransactionScope())
                    {
                        foreach (var task in tasks)
                        {
                            new DaoTask(_options).DeleteTask(task.TaskId);
                        }
    
                        newContext.Entry(project).State = EntityState.Deleted;
                        newContext.SaveChanges();
    
                        scope.Complete();
                    }
                }
            }
        }
    }
    


     
    Wednesday, May 15, 2019 5:56 PM