Getting the following error when saving child records using Entity Framework in .NET Core

Question

Error message:

Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded

Scenario: I have a service request application which I am converting into .net core.

The application has many tables. The primary parent table is service requests. There are a number of child tables which successfully update when changes are made. One however that doesn't is a file details table which is for uploading attachments.

The standard scaffolding has been used to generate the code for the controller. So it has the required index, delete, create, and edit IActionResult code in the controller. All that code and those operations work perfectly.

In addition to that I have a separate IActionResult for uploading of files. Here is the code:

public IActionResult UpLoadFiles()
{
    return View();
}

[HttpPost]
public async Task<IActionResult> UpLoadFiles(ICollection<IFormFile> files, ServiceRequests serviceRequests, int id)
{
    // Attached File Processing
    string path = null;

    var uploads = Path.Combine(_environment.WebRootPath, "uploads");

    foreach (var file in files)
        try
        {
            if (file == null || file.Length == 0)
                return Content("file not selected");
            else
            {
                using (var fileStream = new FileStream(Path.Combine(uploads, file.FileName), FileMode.Create))
                {
                    await file.CopyToAsync(fileStream);
                }

                var fileName = Path.GetFileName(file.FileName);
                path = uploads + "\\" + fileName;

                serviceRequests.FileDetails.Add(new FileDetails()
                    {
                        FileName = fileName,
                        Extension = Path.GetExtension(fileName),
                        Id = Guid.NewGuid(),
                        ServiceRequestId = id
                    });

                // _context.Update(serviceRequests);
                _context.Add(serviceRequests.FileDetails);
                await _context.SaveChangesAsync();

                ViewBag.Message = " Files Uploaded";

                return RedirectToAction("Index");
            }
        }
        catch
        {
            ViewBag.Message = "Upload Failed";
        }

        return View();
}

The above code is called from the edit page for the service request as an asp-action link. The int id is the service request number passed from the viewpage.

Almost all the code works as I debug it. The file attached gets saved to the uploads folder and the file name, extension,id and servicerequestid get populated properly to supposed create the child record.

However, when I get to await _context.SaveChangesAsync(); I get the error at the beginning of the thread. When I leave out the _context.add or _context.update lines- there's no error but nothing gets committed to the table in SQL server either.

Perhaps, it is just something small- but I can't seem to get around the record being properly committed to the filedetails table. This child record is voluntary- not all requests have to have a file attachment.

I suppose I could have a separate view and controller for filedetails and have the servicerequestid entered there. But it defeats the purpose of just wanting to enter additional child records in edit mode of the parent record/entity

Any thoughts to what I am missing in using entity framework in .net core?

Here is rest of controller code prior to code shared earlier:

using System;
using System.Collections.Generic;
using System.DirectoryServices;
using System.Linq;
using System.Runtime.InteropServices;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using MimeKit;
using ServeMeHRCore21.Models;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using System.IO;

namespace ServeMeHRCore21.Controllers
{
    public class ServiceRequestsController : Controller
    {
        private readonly ServeMeHRCoreContext _context;
        private IHostingEnvironment _environment;

        public ServiceRequestsController(ServeMeHRCoreContext context, IHostingEnvironment environment)
        {
            _context = context;
            _environment = environment;
        }


        // GET: ServiceRequests
        public async Task<IActionResult> Index(string StatusType, string searchString)
        {
            IEnumerable<SelectListItem> statusitems = _context.StatusTypes.Select(c => new SelectListItem
            {
                Selected = c.Id == 1,
                Value = c.StatusTypeDescription,
                Text = c.StatusTypeDescription
            });
            ViewBag.StatusType = statusitems;

            ViewBag.CurrentFilter = searchString;
            if (StatusType == null)
                StatusType = "Open";

            var serveMeHRCoreContext = _context.ServiceRequests.Include(s => s.MemberNavigation).Include(s => s.PriorityNavigation).Include(s => s.RequestTypeNavigation).Include(s => s.RequestTypeStepNavigation).Include(s => s.StatusNavigation).Include(s => s.TeamNavigation).Where(s => s.StatusNavigation.StatusTypeNavigation.StatusTypeDescription == StatusType);

            if (!String.IsNullOrEmpty(searchString))
            {
                serveMeHRCoreContext = serveMeHRCoreContext.Where(s => s.RequestHeading.Contains(searchString) || s.RequestDescription.Contains(searchString));
            }

            return View(await serveMeHRCoreContext.ToListAsync());
        }

        // GET: ServiceRequests/Details/5
        public async Task<IActionResult> Details(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var serviceRequests = await _context.ServiceRequests
                .Include(s => s.MemberNavigation)
                .Include(s => s.PriorityNavigation)
                .Include(s => s.RequestTypeNavigation)
                .Include(s => s.RequestTypeStepNavigation)
                .Include(s => s.StatusNavigation)
                .Include(s => s.TeamNavigation)
                .SingleOrDefaultAsync(m => m.Id == id);
            if (serviceRequests == null)
            {
                return NotFound();
            }

            return View(serviceRequests);
        }

        // GET: ServiceRequests/Create
        public IActionResult Create()
        {
            ViewData["Member"] = new SelectList(_context.Members, "Id", "MemberEmail");
            ViewData["Priority"] = new SelectList(_context.Priorities, "Id", "PriorityDescription");
            ViewData["RequestType"] = new SelectList(_context.RequestTypes, "Id", "RequestTypeDescription");
            ViewData["RequestTypeStep"] = new SelectList(_context.RequestTypeSteps, "Id", "StepDescription");
            ViewData["Status"] = new SelectList(_context.StatusSets, "Id", "StatusDescription");
            ViewData["Team"] = new SelectList(_context.Teams, "Id", "TeamDescription");

            string homePhone;
            string givenName;
            string surname;
            string email;

            var model = new ServiceRequests();
            model.RequestorId = User.Identity.Name;

            ViewBag.FileUp = _context.ApplicConfs.Select(s => s.FileSystemUpload).FirstOrDefault();

            Boolean ADconf = _context.ApplicConfs.Select(s => s.Adactive).FirstOrDefault();

            if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows) & (ADconf))
            {
                GetADinfo(out givenName, out surname, out homePhone, out email);

                model.RequestorFirstName = givenName;
                model.RequestorLastName = surname;
                model.RequestorEmail = email;
                model.RequestorPhone = homePhone;
                model.RequestType = 1;
            }

            return View(model);
        }

        // POST: ServiceRequests/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("Id,RequestHeading,RequestDescription,RequestorId,RequestorFirstName,RequestorLastName,RequestorPhone,RequestorEmail,DateTimeSubmitted,DateTimeStarted,DateTimeCompleted,Priority,RequestType,RequestTypeStep,Member,Status,Team")] ServiceRequests serviceRequests, ICollection<IFormFile> files)
        {
            if (ModelState.IsValid)
            {
                string homePhone;
                string givenName;
                string surname;
                string email;

                Boolean ADconf = _context.ApplicConfs.Select(s => s.Adactive).FirstOrDefault();

                if (ADconf)
                {
                    GetADinfo(out givenName, out surname, out homePhone, out email);
                    ViewBag.RequestorFirstName = givenName;
                    ViewBag.RequestorLastName = surname;
                    ViewBag.RequestorPhone = homePhone;
                    ViewBag.RequestorEmail = email;
                }




                _context.ServiceRequests.Include(i => i.IndividualAssignmentHistories).Include(i => i.TeamAssignmentHistories).Include(i => i.FileDetails);
                _context.ServiceRequests.Add(serviceRequests);
                serviceRequests.DateTimeSubmitted = DateTime.Now;
                serviceRequests.Status = 1;
                serviceRequests.RequestorId = User.Identity.Name;

                //================================================================================
                //Create a history record for team assignment

                serviceRequests.TeamAssignmentHistories.Add(new TeamAssignmentHistories()
                {
                    DateAssigned = DateTime.Now,
                    AssignedBy = User.Identity.Name,
                    ServiceRequest = serviceRequests.Id,
                    Team = serviceRequests.Team
                });
                //================================================================================

                //================================================================================
                //Create history record for individual assignment
                serviceRequests.IndividualAssignmentHistories.Add(new IndividualAssignmentHistories()
                {
                    DateAssigned = DateTime.Now,
                    AssignedBy = User.Identity.Name,
                    //AssignedTo = "A3HR.Lyndon",
                    AssignedTo = serviceRequests.Member.Value,
                    ServiceRequest = serviceRequests.Id
                });
                //================================================================================

                //Create request step history record===============================================
                serviceRequests.StepHistories.Add(new StepHistories()
                {
                    LastUpdated = DateTime.Now,
                    RequestTypeStep = serviceRequests.RequestTypeStep.Value,
                    ServiceRequest = serviceRequests.Id
                });

                //=================================================================================




                _context.Add(serviceRequests);
                await _context.SaveChangesAsync();

                //Check and see if in application configuration email confirmation is set on========
                Boolean emailconf = _context.ApplicConfs.Select(s => s.EmailConfirmation).FirstOrDefault();

                if (emailconf)
                {
                    SendStatusConfirmation(1, serviceRequests.RequestorEmail);
                }


                return RedirectToAction(nameof(Index));
            }
            ViewData["Member"] = new SelectList(_context.Members, "Id", "MemberEmail", serviceRequests.Member);
            ViewData["Priority"] = new SelectList(_context.Priorities, "Id", "PriorityDescription", serviceRequests.Priority);
            ViewData["RequestType"] = new SelectList(_context.RequestTypes, "Id", "RequestTypeDescription", serviceRequests.RequestType);
            ViewData["RequestTypeStep"] = new SelectList(_context.RequestTypeSteps, "Id", "StepDescription", serviceRequests.RequestTypeStep);
            ViewData["Status"] = new SelectList(_context.StatusSets, "Id", "StatusDescription", serviceRequests.Status);
            ViewData["Team"] = new SelectList(_context.Teams, "Id", "TeamDescription", serviceRequests.Team);
            return View(serviceRequests);
        }

        // GET: ServiceRequests/Edit/5
        public async Task<IActionResult> Edit(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var serviceRequests = await _context.ServiceRequests.SingleOrDefaultAsync(m => m.Id == id);
            if (serviceRequests == null)
            {
                return NotFound();
            }
            ViewData["Member"] = new SelectList(_context.Members, "Id", "MemberEmail", serviceRequests.Member);
            ViewData["Priority"] = new SelectList(_context.Priorities, "Id", "PriorityDescription", serviceRequests.Priority);
            ViewData["RequestType"] = new SelectList(_context.RequestTypes, "Id", "RequestTypeDescription", serviceRequests.RequestType);
            ViewData["RequestTypeStep"] = new SelectList(_context.RequestTypeSteps, "Id", "StepDescription", serviceRequests.RequestTypeStep);
            ViewData["Status"] = new SelectList(_context.StatusSets, "Id", "StatusDescription", serviceRequests.Status);
            ViewData["Team"] = new SelectList(_context.Teams, "Id", "TeamDescription", serviceRequests.Team);
            return View(serviceRequests);
        }

        // POST: ServiceRequests/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Edit(int id, [Bind("Id,RequestHeading,RequestDescription,RequestorId,RequestorFirstName,RequestorLastName,RequestorPhone,RequestorEmail,DateTimeSubmitted,DateTimeStarted,DateTimeCompleted,Priority,RequestType,RequestTypeStep,Member,Status,Team")] ServiceRequests serviceRequests)
        {
            if (id != serviceRequests.Id)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                try
                {

                    // If Status changes then populate the datetime started and completed fields and send email confirmation=========

                    if (serviceRequests.Status == 2 && serviceRequests.DateTimeStarted == null)
                    {
                        serviceRequests.DateTimeStarted = DateTime.Now;
                        SendStatusConfirmation(2, serviceRequests.RequestorEmail);
                        _context.Entry(serviceRequests).State = EntityState.Modified;
                    }

                    if (serviceRequests.Status == 3 && serviceRequests.DateTimeCompleted == null)
                    {
                        serviceRequests.DateTimeCompleted = DateTime.Now;
                        SendStatusConfirmation(3, serviceRequests.RequestorEmail);
                        _context.Entry(serviceRequests).State = EntityState.Modified;

                    }


                    ////===================================================================================



                    // If team is modified create new team history record==========================

                    //var lastTeam = _context.TeamAssignmentHistories
                    //    .Include(t => t.ServiceRequestNavigation)
                    //    .Include(t => t.TeamNavigation)
                    //    .Where(t => t.ServiceRequestNavigation.Id == serviceRequests.Id);
                    var lastTeam = from tah in _context.TeamAssignmentHistories
                                   where tah.ServiceRequest == serviceRequests.Id
                                   orderby tah.DateAssigned descending
                                   select tah;
                    int lteam;
                    lteam = lastTeam.FirstOrDefault().Team;

                    if (serviceRequests.Team != lteam)

                    {
                        serviceRequests.TeamAssignmentHistories.Add(new TeamAssignmentHistories()
                        {
                            DateAssigned = DateTime.Now,
                            AssignedBy = User.Identity.Name,
                            ServiceRequest = serviceRequests.Id,
                            Team = serviceRequests.Team
                        });

                    }

                    //=========================================================================================



                    //=========================================================================================

                    // if individual assigned has changed add individual history record========================
                    //var lastMember = _context.IndividualAssignmentHistories
                    //    .Include(t => t.ServiceRequestNavigation)
                    //    .Where(t => t.ServiceRequestNavigation.Id == serviceRequests.Id)
                    //    ;
                    var lastMember = from lm in _context.IndividualAssignmentHistories
                                     where lm.ServiceRequest == serviceRequests.Id
                                     orderby lm.DateAssigned descending
                                     select lm;
                    int lmember;
                    lmember = lastMember.FirstOrDefault().AssignedTo;

                    if (serviceRequests.Member != lmember)
                    {
                        serviceRequests.IndividualAssignmentHistories.Add(new IndividualAssignmentHistories()
                        {
                            DateAssigned = DateTime.Now,
                            AssignedBy = User.Identity.Name,
                            ServiceRequest = serviceRequests.Id,
                            AssignedTo = serviceRequests.Member.Value,
                        });
                    }


                    //===========================================================================================


                    //===== If request step changes then  create request step history record=====================

                    //var lastStep = _context.StepHistories
                    //    .Include(t => t.ServiceRequestNavigation)
                    //    .Where(t => t.ServiceRequestNavigation.Id == serviceRequests.Id)
                    //    ;
                   var lastStep = from ls in _context.StepHistories
                               where ls.ServiceRequest == serviceRequests.Id
                               orderby ls.LastUpdated descending
                               select ls;
                    int lstep;
                    lstep = lastStep.FirstOrDefault().RequestTypeStep;

                    if (serviceRequests.RequestTypeStep != lstep)
                    {
                        serviceRequests.StepHistories.Add(new StepHistories()
                        {
                            LastUpdated = DateTime.Now,
                            RequestTypeStep = serviceRequests.RequestTypeStep.Value,
                            ServiceRequest = serviceRequests.Id
                        });
                    }

                    //===========================================================================================



                    _context.Update(serviceRequests);
                    await _context.SaveChangesAsync();
                }
                catch (DbUpdateConcurrencyException)
                {
                    if (!ServiceRequestsExists(serviceRequests.Id))
                    {
                        return NotFound();
                    }
                    else
                    {
                        throw;
                    }
                }
                return RedirectToAction(nameof(Index));
            }
            ViewData["Member"] = new SelectList(_context.Members, "Id", "MemberEmail", serviceRequests.Member);
            ViewData["Priority"] = new SelectList(_context.Priorities, "Id", "PriorityDescription", serviceRequests.Priority);
            ViewData["RequestType"] = new SelectList(_context.RequestTypes, "Id", "RequestTypeDescription", serviceRequests.RequestType);
            ViewData["RequestTypeStep"] = new SelectList(_context.RequestTypeSteps, "Id", "StepDescription", serviceRequests.RequestTypeStep);
            ViewData["Status"] = new SelectList(_context.StatusSets, "Id", "StatusDescription", serviceRequests.Status);
            ViewData["Team"] = new SelectList(_context.Teams, "Id", "TeamDescription", serviceRequests.Team);
            return View(serviceRequests);
        }

        // GET: ServiceRequests/Delete/5
        public async Task<IActionResult> Delete(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var serviceRequests = await _context.ServiceRequests
                .Include(s => s.MemberNavigation)
                .Include(s => s.PriorityNavigation)
                .Include(s => s.RequestTypeNavigation)
                .Include(s => s.RequestTypeStepNavigation)
                .Include(s => s.StatusNavigation)
                .Include(s => s.TeamNavigation)
                .SingleOrDefaultAsync(m => m.Id == id);
            if (serviceRequests == null)
            {
                return NotFound();
            }

            return View(serviceRequests);
        }

        // POST: ServiceRequests/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmed(int id)
        {
            var serviceRequests = await _context.ServiceRequests.SingleOrDefaultAsync(m => m.Id == id);
            _context.ServiceRequests.Remove(serviceRequests);
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }

        private bool ServiceRequestsExists(int id)
        {
            return _context.ServiceRequests.Any(e => e.Id == id);
        }

        public void GetADinfo(out string givenName, out string surname, out string homePhone, out string email)
        {
            //===========================================================
            //Go and get AD info for the current user or equivalent
            var components = User.Identity.Name.Split('\\');
            var username = components.Last();
            // create LDAP connection object
            DirectoryEntry myLdapConnection = createDirectoryEntry();
            DirectorySearcher search = new DirectorySearcher(myLdapConnection);

            search.Filter = "(cn=" + username + ")";
            SearchResult result = search.FindOne();
            DirectoryEntry dsresult = result.GetDirectoryEntry();
            givenName = dsresult.Properties["givenName"][0].ToString();
            surname = dsresult.Properties["sn"][0].ToString();
            email = dsresult.Properties["mail"][0].ToString();
            homePhone = dsresult.Properties["homePhone"][0].ToString();

            //=============================================================================
        }

        public DirectoryEntry createDirectoryEntry()
        {
            // create and return new LDAP connection with desired settings

            string ADconn = _context.ApplicConfs.Select(s => s.Ldapconn).FirstOrDefault();
            string LDAPConn = _context.ApplicConfs.Select(s => s.Ldappath).FirstOrDefault();

            //string ADconn;
            //ADconn = "SERVER.A3HR.local";
            //string LDAPConn;
            //LDAPConn = "LDAP://SERVER.A3HR.local";
            //DirectoryEntry ldapConnection = new DirectoryEntry("SERVER.A3HR.local");

            //ldapConnection.Path = "LDAP://OU=staffusers,DC=leeds-art,DC=ac,DC=uk";
            //ldapConnection.Path = "LDAP://SERVER.A3HR.local";

            DirectoryEntry ldapConnection = new DirectoryEntry(ADconn);

            ldapConnection.Path = LDAPConn;

            ldapConnection.AuthenticationType = AuthenticationTypes.Secure;

            return ldapConnection;
        }



        public void SendStatusConfirmation(int statusstep, string email)
        {
            var message = new MimeMessage();

            string mailFrom = _context.ApplicConfs.Select(s => s.ManageHremail).FirstOrDefault();
            string fromPass = _context.ApplicConfs.Select(s => s.ManageHremailPass).FirstOrDefault();
            string smtpHost = _context.ApplicConfs.Select(s => s.Smtphost).FirstOrDefault();
            int smtpPort = _context.ApplicConfs.Select(s => s.Smtpport).FirstOrDefault().Value;
            Boolean enabSSL = _context.ApplicConfs.Select(s => s.EnableSsl).FirstOrDefault().Value;


            message.To.Add(new MailboxAddress(email));
            message.From.Add(new MailboxAddress(mailFrom));
            switch (statusstep)
            {
                case 1:
                    message.Subject = "Request received";
                    message.Body = new TextPart("plain") { Text = @"Your request has been received" };
                    break;

                case 2:
                    message.Subject = "Request started";
                    message.Body = new TextPart("plain") { Text = @"Your request has been started" };
                    break;

                case 3:
                    message.Subject = "Request completed";
                    message.Body = new TextPart("plain") { Text = @"Your request has been completed" };
                    break;
            }
            using (var client = new MailKit.Net.Smtp.SmtpClient())
            {
                {
                    // For demo-purposes, accept all SSL certificates (in case the server supports STARTTLS)
                    client.ServerCertificateValidationCallback = (s, c, h, e) => true;



                    client.Connect(smtpHost, smtpPort, false);

                    // Note: since we don't have an OAuth2 token, disable
                    // the XOAUTH2 authentication mechanism.
                    client.AuthenticationMechanisms.Remove("XOAUTH2");

                    // Note: only needed if the SMTP server requires authentication
                    client.Authenticate(mailFrom, fromPass);

                    client.Send(message);
                    client.Disconnect(true);
                }
            }

        }