none
Using code-first in a multi-process environment RRS feed

  • Question

  • I have been reviewing/studying on code first and migrations to manage database versions.

    I think I have the basics of it down, but have a question about more complex usage.  Consider a database that is utilized (read/write) by multiple different processes.  Some tables in the database are "common" and accessed by more than one process, while other tables are specific to a given process.

    Is there any concept/allowance for this type of environment?  How would versioning and upgrading work in a situation like this?

    Sunday, January 28, 2018 5:06 PM

All replies

  • Is there any concept/allowance for this type of environment?

    Of course, EF is used in multi-users Web environments, which is a multi-processes on a Webserver scenario with each user being in their own thread while processing.   

    I think one has to be aware of deadlocks on the database tables in a multi-users environment, and I don't think any non experienced developer using EF, an ORM, even considers it in such a scenario.

    How would versioning and upgrading work in a situation like this?

    I am sure there MSDN or sites and others that have articles that cover it.

    Myself personally, I don't think any developer that doesn't have basic DBA concepts with the database server  has no business using code-first, IMO, because he or she usually winds up getting into trouble.

    Sunday, January 28, 2018 7:29 PM
  • This isn't a web environment, rather a distinct set of windows services, each with their own set of responsibilities.  To that end, a given process will contain some, but not all, of the entity classes being represented in the database.  That is really the basis of my "how would versioning and upgrading work".  Do we have to have a single process somewhere (that DOES have the definitions of each entity class) that is responsible for upgrading?

    I've searched around quite a bit, but most of the articles are all about a single application (or, multiple instances of a single application).

    Right now, we are managing the database and its upgrades directly.  We were just trying to explore if something like Entity Framework could save us any time.

    Sunday, January 28, 2018 10:29 PM
  • Hi Cabadam,

    As far as I know, entity framework is a ORM framework, which execute SQL statement via ado.net. according to your description, different processes operate the same database. I would suggest that you could refer to the following document about SQL Server Transaction Locking and Row Versioning Guide

    https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx

    If you are worried about concurrency problems, please could refer to the following links.

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

    Please check the attribute named TimeStamp, that for concurrency checking.

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

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 29, 2018 1:41 AM
    Moderator
  • This isn't a web environment, rather a distinct set of windows services, each with their own set of responsibilities.  To that end, a given process will contain some, but not all, of the entity classes being represented in the database.  That is really the basis of my "how would versioning and upgrading work".  Do we have to have a single process somewhere (that DOES have the definitions of each entity class) that is responsible for upgrading?

    You can use a Repository for each Windows Service all sharing the common DAL.

    https://msdn.microsoft.com/en-us/library/ff649690.aspx?f=255&MSPPError=-2147217396

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

    Well there should be a  Data Access Layer, a classlib project, that all the windows services have reference to that does the low level data base CRUD using EF or any ORM. And if using the Data Access Object pattern in the DAL, then the DAO is on a per table basis.

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

    Of course, it would be using the DTO pattern, another classlib project called Entities, where all the DTO(s) are kept and all projects have reference to the Entities project and know what the DTO(s) are about.

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

    EF code first is more popular in Web based ASP.NET MVC, ASP.NET WebAPI  and .NET Core based solutions and is for new database development so they say.

    EF database first is for existing databases, and it is much more easier to maintain and deploy than code first, IMO.

    https://roland.kierkels.net/c-asp-net/ef-model-vs-database-vs-code-first-approach/

    Below is a typical usage of Repository,  DAO, and DTO patterns being used with a DAL using EF DB first.

    You can use AutoMapper, EF-2-DTO or do the object mapping in code yourself

    https://entitiestodtos.codeplex.com/

    It's about the patterns and not where they are being used in Web solution or Windows service based solution.

    It's about SoC even for Windows service applications too.

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

    I've searched around quite a bit, but most of the articles are all about a single application (or, multiple instances of a single application).

    It doesn't matter about the multiple processes using an ORM, and each process is using the ORM in its own thread independent of the other processes, and what you should be concerned about is deadlocks and how to circumvent them even if you were not using EF.

    http://www.c-sharpcorner.com/UploadFile/ff2f08/prevent-dead-lock-in-entity-framework/

    using System;
    using System.Collections.Generic;
    using System.Runtime.Serialization;
    
    namespace Entities
    {
       [DataContract] 
        public class DTOStudent
        {
            
            private DTOResponse dtor = new DTOResponse();
    
            [DataMember]
            public Int32 StudentID { get; set; }
    
            [DataMember]
            public string LastName { get; set; }
    
            [DataMember]
            public string FirstName { get; set; }
    
            [DataMember]
            public DateTime? EnrollmentDate { get; set; }
    
            [DataMember]
            public virtual ICollection<DTOEnrollandCourse> EnrollsandCourses { get; set; }
    
            [DataMember]
            public DTOResponse DtoResponse
            {
                get { return dtor; }
                set { dtor = value; }
            } 
        }
    }
    
    -------------------------------------------------
    using System;
    using System.Collections.Generic;
    using Entities;
    
    namespace Repository
    {
        public interface IStudentRepo
        {
            DTOStudent GetStudentById(Int32 id);
            List<DTOStudent> GetStudents();
            void CreateStudent(DTOStudent dto);
            void UpdateStudent(DTOStudent dto);
            void DeleteStudent(Int32 id);
        }
    }
    
    ------------------------------------------------------------
    using System;
    using System.Collections.Generic;
    using Entities;
    using DAL.DAO;
    
    namespace Repository
    {    public class StudentRepo : IStudentRepo
        {
            private IDAOStudent _daoStudent;
    
            public StudentRepo(IDAOStudent daoStudent)
            {
                _daoStudent = daoStudent;
            }
            public DTOStudent GetStudentById(int id)
            {
               return _daoStudent.GetStudentById(id);
            }
            public List<DTOStudent> GetStudents()
            {
                return _daoStudent.GetStudents();
            }
            public void CreateStudent(DTOStudent dto)
            {
                _daoStudent.CreateStudent(dto);
            }
            public void UpdateStudent(DTOStudent dto)
            {
                _daoStudent.UpdateStudent(dto);
            }
            public void DeleteStudent(int id)
            {
                _daoStudent.DeleteStudent(id);
            }
        }
    }
    --------------------------------------------------
    
    using System;
    using System.Collections.Generic;
    using Entities;
    
    namespace DAL.DAO
    {
        public interface IDAOStudent
        {
            DTOStudent GetStudentById(Int32 id);
            List<DTOStudent> GetStudents();
            void CreateStudent(DTOStudent dto);
            void UpdateStudent(DTOStudent dto);
            void DeleteStudent(Int32 id);
        }
    }
    ---------------------------------------------------
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data.Entity;
    using System.Data.Entity.Core.EntityClient;
    using System.Data.Entity.Core.Objects;
    using System.Data.Entity.Infrastructure;
    using System.Data.SqlClient;
    using Entities;
    using DAL.Model;
    
    namespace DAL.DAO
    {
        public class DAOStudent : IDAOStudent
        {
            public DTOStudent GetStudentById(Int32 id)
            {
                var dto = new DTOStudent();
                using (var context = new CUDataEntities())
                {
                    var student = (context.Students.Where(a => a.StudentID == id)).SingleOrDefault();
    
                    if (student != null)
                    {
                        dto.StudentID = student.StudentID;
                        dto.FirstName = student.FirstName;
                        dto.LastName = student.LastName;
                        dto.EnrollmentDate = student.EnrollmentDate;
    
                        var enrolllments =  new DAOEnrollment().GetEntrollmentsByStudentId(id).ToList();
                        var courses = new DAOCourse().GetCoursesByStudentCourseId(student.StudentID).ToList();
    
                        dto.EnrollsandCourses = (from a in enrolllments
                                      join b in courses on a.CourseID equals b.CourseID
                        select new  DTOEnrollandCourse()
                         { Title = b.Title, Credits = b.Credits, Grade = a.Grade }).ToList();
                    }
                }
    
                return dto;
            }
            public void CreateStudent(DTOStudent dto)
            {
                using (var context = new CUDataEntities())
                {
                    var student = new Student
                    {
                        FirstName = dto.FirstName,
                        LastName = dto.LastName,
                        EnrollmentDate = dto.EnrollmentDate
                    };
    
                    context.Students.Add(student);
                    context.SaveChanges();
                }
            }
    
            public void DeleteStudent(int id)
            {
                Student student;
                using (var context = new CUDataEntities())
                {
                    student = (context.Students.Where(a => a.StudentID == id)).SingleOrDefault();
                }
    
                using (var newContext = new CUDataEntities())
                {
                    newContext.Entry(student).State = System.Data.Entity.EntityState.Deleted;
                    newContext.SaveChanges();
                }
            }
    
            public List<DTOStudent> GetStudents()
            {
               
                var dtos = new List<DTOStudent>();
    
                using (var context = new CUDataEntities())
                {
                    //var adapter = (IObjectContextAdapter)context;
                    //var objectContext = adapter.ObjectContext;
                    
                    //var entityConn = objectContext.Connection as EntityConnection;
                    //var dbConn = entityConn.StoreConnection as SqlConnection;
    
                    //dbConn.Open();
    
                    var students = context.Students.ToList();
    
                    foreach(var stud in students)
                    {
                        var dto = new DTOStudent
                        {
                            StudentID = stud.StudentID,
                            FirstName = stud.FirstName,
                            LastName = stud.LastName,
                            EnrollmentDate = stud.EnrollmentDate
                        };
    
                        dtos.Add(dto);
                    }
                }
    
                return dtos;
            }
    
            public void UpdateStudent(DTOStudent dto)
            {
                var student = new Student();
    
                using (var context = new CUDataEntities())
                {
                    student = (context.Students.Where(a => a.StudentID == dto.StudentID)).SingleOrDefault();
                }
    
                if (student != null)
                {
                    student.FirstName = dto.FirstName;
                    student.LastName = dto.LastName;
                    student.EnrollmentDate = dto.EnrollmentDate;
                } 
    
                using (var dbcontext = new CUDataEntities())
                {
                    if (student != null)
                    {
                        dbcontext.Entry(student).State = EntityState.Modified;
                        dbcontext.SaveChanges();
                    }
                }
            }
        }
    }
    
    

    Monday, January 29, 2018 3:14 AM