Deleting a record related from another

Question

User-872746141 posted

Hi I have a problem with un-assigning a user from a Job. i.e I don't know how to delete a registration record related to the job that the user is assigned to. To make it more simple I have 3 tables with this relationship.

tblUser 1---------M tblRegistration M -----------1 tblJob

See this image of the form? Basically the end user clicks the big orange button "Unassign" and the record disappears from the table and appears in the table with the Unassigned Users heading.  

https://imgur.com/jgVnTpQ

My problem is in the UnassignUserRegistration method where I am trying to delete my user registration which In my Job Controller. I will provide the code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using Pitcher.Data;
using Pitcher.Models;
using Pitcher.Models.TeamViewModels;


namespace Pitcher.Controllers
{
    public class JobsController : Controller
    {
        private readonly TeamContext _context;


        public JobsController(TeamContext context)
        {
            _context = context;
        }
       
        /// <summary>
        /// Creates a Registration to assign user to a currently displayed job.
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="JobID"></param>
        /// <returns>The newly assigned user registration</returns>
        [HttpGet]
        public async Task<IActionResult> AssignUserRegistration(int UserID, int JobID)
        {
            Registration registration = new Registration{UserID = UserID, JobID = JobID, RegistrationDate = DateTime.Now};
            _context.Add(registration);
            await _context.SaveChangesAsync();
            //ID is JobID. Now refresh the page by redirecting to it again.
            return RedirectToAction(nameof(UserAssignments), new{ID = JobID});
        }


        public IActionResult GetUnassignedUsers()
        {
            _context.Jobs.OrderByDescending(j => j.ID).FirstOrDefault();       
            var userlist = _context.Users.Where(u => !u.Registrations.Any());
            return Json(userlist);
        }


        public IActionResult GetAssignedUsers()
        {
            _context.Jobs.OrderByDescending(j => j.ID).FirstOrDefault();       
            var userlist = _context.Users.Where(u => u.Registrations.Any());
            return Json(userlist);
        }


        public async Task<IActionResult> UnassignUserRegistration(int UserID, int JobID)
        {            
            if(JobID == null)
            {
                return NotFound();
            }
            var registration = await _context.Registrations.OrderByDescending(j => j.ID)                
                .AsNoTracking()
                .Include(r => r.Job)
                .Include(r => r.User)
                //Retrives the selected entity.
                .FirstOrDefaultAsync(m => m.ID == JobID);
            if (registration == null)
            {
                ViewData["ErrorMessage"] =
                    "Delete failed. Try again, and if the problem persists " +
                    "see your system administrator.";
            }
            return RedirectToAction(nameof(UserAssignments), new{ID = JobID});
        }


        [HttpPost, ActionName("UnassignUserRegistration")]
        [ValidateAntiForgeryToken]
        //This is basically like the Delete_Confirmed method that 
        //EF Core generates with the view which I don't supply as 
        //as it is a given that it should work if UnassignUserRegistration does. 
        public async Task<IActionResult> UnassignUserRegistration_Confirmed(int id)
        {
            var registration = await _context.Registrations.FindAsync(id);


            if(registration == null)
            {
                return RedirectToAction(nameof(Index));
            }
            try
            {
                _context.Registrations.Remove(registration);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            catch(DbUpdateException /* ex */)
            {
                //Log the error (uncomment ex variable name and write a log.)
                return RedirectToAction(nameof(Delete), new {id = id, saveChangesError = true});
            }
        }

In the view file the method is being triggered in the second return of the the registeredUserTable. Here is all the code if you
need to see it. If no one is able or is able to help or feels I have not put in enough effort to research this my self(which I have)
then please if you may, direct me to the appropriate resource. Here is the function I am rendering the table data to in the front end.

        document.getElementById('registeredUsersTable').style.display = 'block';
        var id=@Model.ID 
        $('#registeredUsersTable').DataTable({
            "ajax": {
            'type': 'get',
            //PROBLEM:Cannot get the related UserID of the Job.
            'data': { ID: id},
            'dataType': "json",                  
            "url": "@Url.Action("GetAssignedUsers")",
            "dataSrc": function (result) {
                return result;
                }
            },            
            "columns": [                
            { "data": "userFirstName"},
            { "data": "userLastName"},
            { "data": "userContactEmail"},
            {
            "data": null,
            "render": function (value) {
                return  '<a href="/Jobs/UnassignUserRegistration?UserID=' + value.id + '&JobID=' + id +' "button type="button" class="btn btn-primary btn-block">Unassign</a>';
            }
                }
            ]
        });

Answer 1

User287926715 posted

Hi 287papa,

Use EF 6 to delete related entities can refer to this article：

Cascade Delete in Entity Framework 6

var registration = await _context.Registrations.FindAsync(id);

You can modify

var registration = await _context.Registrations.FindAsync(id);

to

var registration = await _context.Registrations   
                .Include(r => r.Job)
                .Include(r => r.User)
                .FirstOrDefaultAsync(m => m.ID == id);

Best Regards,

ChaoDeng

Answer 2

User-872746141 posted

Hi Chao,

Thanks for your response. I Assume  you are suggesting I implement that change in this method? 

UnassignUserRegistration_Confirmed

I need to make you aware that I will not be able to see the results of that change you suggest I implement as that method is getting triggered further down the stack. My actual problem lies in the UnassignUserRegistration method where the "ErrorMessage" will be triggered in the 2nd if statement block. Let me know if you have further questions. Thanks.

Answer 3

User287926715 posted

Hi 287papa,

I Assume  you are suggesting I implement that change in this method? 

Yes it is.

My actual problem lies in the UnassignUserRegistration method where the "ErrorMessage" will be triggered in the 2nd if statement block. Let me know if you have further questions.

After modifying the code in the method "UnassignUserRegistration_Confirmed(int id)" according to my prompts, is an error caught?
If it is convenient, can you provide the error message you received?

Best Regards,

ChaoDeng

Answer 4

User-872746141 posted

Hi Chao,

I can't implement that change as that method is not yet being triggered because I have a problem in that method chain triggered before that which is UnassignUserRegistration. There is a warning saying:

The result of the expression is always 'false' since a value of type 'int' is never equal to 'null' of type 'int?'

 My intention was to return the RegistrationID here which will be deleted though obviously that's not what I'm doing here because I don't know how to. why I'm retuning that error is because I was confused.

I was trying to base my deletion method on the AssignUserRegistration method using the two parameters and do the reverse of that and also base it on the Delete actions generated by EF Core by default. Yes the null check is pointless and the compiler warned me. You could say I didn't know what to check for. At this point I am staring at the problem. I realize now I don't have to check for the UserID or JobId as EF Core will do a cascade delete so really I just have to work on the RegistrationID and workout how to delete it. 

Answer 5

User287926715 posted

Hi 287papa,

The result of the expression is always 'false' since a value of type 'int' is never equal to 'null' of type 'int?'

You cannot use "JobID == null" to determine the int type. Because it is an int type, the int type can never be equal to null, you can use Nullable Types, which can be modified like this:

public async Task<IActionResult> UnassignUserRegistration(int? UserID, int? JobID)

You can also judge this: when the JobID is not empty, then go to the query.

if(JobID!= null){
       //query
}

If it is convenient for you, you can provide me with some code of your Model.

Best Regards,

ChaoDeng

Answer 6

User-872746141 posted

Hi Chao,

I apologise for taking so many months to respond! I had numerous personal and work related setbacks including blue screen of death and having to reinstall Windows and re configuring .NET 5 and SQL Server. At least now the code you provided now gives me the ability to check if there is a user in the front end "registeredUsersTable" in "Jobs\UserAssignments.cshtml".  

Here is the Job Model for the controller I'm using with data attributes omitted for brevity:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace Pitcher.Models
{
    public class Job
    {        
        
        public int ID { get; set; }

        public string JobTitle { get; set; }

        public string JobDescription { get; set; }

        public DateTime JobStartDate {get;set;}

        public DateTime JobDeadline {get;set;}

        public bool JobIsComplete{get;set;}

        public ICollection<Registration> Registrations {get;set;}

        public ICollection<Result> Results {get;set;}
    }   
}

Here is the model for User:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Pitcher.Models;
namespace Pitcher.Models
{
    public class User
    {      
        public int ID { get; set; }

        public string UserFirstName { get; set; }

        public string UserLastName { get; set; }

        public string UserContactEmail{get;set;}

        public string UserPhoneNumber{get;set;}

        public string UserAddress{get;set;}

        public string UserPostCode { get; set; }

        public string UserCountry {get;set;}

        public string UserMobileNumber {get;set;}

        public string UserState {get;set;}                   

        public string UserFullname => string.Format("{0} {1}", UserFirstName, UserLastName);

        public ICollection<Registration> Registrations {get;set;}
    }
}

Here is the model for Registration:

using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace Pitcher.Models
{
    public class Registration
    {
        public int ID {get;set;}
        public int UserID { get; set; }
        public int JobID { get; set; }      
        public DateTime RegistrationDate {get;set;}        
        public User User {get;set;}
        public Job Job {get;set;}
    }
}

These models correspond to the 3 tables tblJob, tblRegistration and tblUser as seen in the diagram generated by the current database.

Again sorry for taking so long. 

Regards,
Jordan