Click here to Skip to main content
15,067,539 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
C#

I am attempting to delete from EF Core generated SQL Server DB rendered in ASP.NET MVC 5 with C# and DataTables. These are the tables I am using. I am trying to delete a record in the Registration entity unsuccessfully. Database diagram here.

The weird thing is only certain records report the error. I will refer to the UserID of this record and not the Primary Key ID as I am deleting and re inserting some of the records. Here is the problem. The UserID 4 and 2 are being deleted without error.

ID	UserID	JobID	RegistrationDate
5062	1	1	2021-08-10 23:06:32.8896172
5069	4	2	2021-08-10 23:21:33.5408262
5073	2	2	2021-08-11 11:08:48.6653782
5074	3	2	2021-08-11 11:20:26.5476394


While the records with UserID of 1 and 3 are being deleted but with the error. I will show you what data lies in the User table. Sorry it's not well formatted. I've put it into csv format so if you copy it to a text editor or excel it should display much better.

ID,UserFirstName,UserLastName,UserContactEmail,UserPhoneNumber,UserAddress,UserPostCode,UserCountry,UserMobileNumber,UserState
1,Carson,Alexander,jnash486+test1@gmail.com,61071800671518,54 Yankee st Runcorn,4113,Australia,0476162087,QLD
2,Alonso,Meredith,jnash486+am15@gmail.com,61038002222,34 Webster Dandenong Park,3175,Australia,0423162085,VIC
3,Arturo,Anand,jnash486+aa01@gmail.com,61028004278,72 Doug Rd Wakevale,3115,Australia,0423162085,VIC
4,Gytis,Barzdukas,jnash486+gytisB@gmail.com,61027104224,156 Barnett Rd,4060,Austraila,0469162074,VIC


Other than that the record from the registration table is being deleted. I cannot catch the error in C#. It appears to be triggered in the view file where I do not know how to catch it so I cannot stop user from seeing the annoying error message which is this.

NullReferenceException: Object reference not set to an instance of an object.
AspNetCore.Views_Jobs_UserAssignments+<>c__DisplayClass8_0+<<ExecuteAsync>b__16>d.MoveNext() in UserAssignments.cshtml, line 100

Stack Query Cookies Headers Routing
NullReferenceException: Object reference not set to an instance of an object.
AspNetCore.Views_Jobs_UserAssignments+<>c__DisplayClass8_0+<<ExecuteAsync>b__16>d.MoveNext() in UserAssignments.cshtml
+
        var id=@Model.ID
Microsoft.AspNetCore.Mvc.Razor.RazorPage.RenderSectionAsyncCore(string sectionName, bool required)
Microsoft.AspNetCore.Mvc.Razor.RazorPage.RenderSection(string name, bool required)
AspNetCore.Views_Shared__Layout.<ExecuteAsync>b__55_1() in _Layout.cshtml
+
    @RenderSection("Scripts", required: false)
Microsoft.AspNetCore.Razor.Runtime.TagHelpers.TagHelperExecutionContext.SetOutputContentAsync()
AspNetCore.Views_Shared__Layout.ExecuteAsync()
Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context)
Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, bool invokeViewStarts)
Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderLayoutAsync(ViewContext context, ViewBufferTextWriter bodyWriter)
Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context)
Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, string contentType, Nullable<int> statusCode)
Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, string contentType, Nullable<int> statusCode)
Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ActionContext actionContext, IView view, ViewDataDictionary viewData, ITempDataDictionary tempData, string contentType, Nullable<int> statusCode)
Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor.ExecuteAsync(ActionContext context, ViewResult result)
Microsoft.AspNetCore.Mvc.ViewResult.ExecuteResultAsync(ActionContext context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0<TFilter, TFilterAsync>(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext<TFilter, TFilterAsync>(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)


Here is the code of the controller method where I am stuck.

C#
#region User Registrations
        /// <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});
        }

        /// <summary>
        /// Action that displays table showing unassigned users
        /// to currently selected job.
        /// </summary>
        /// <returns>A list of unassigned users for currently selected Job</returns>
        public IActionResult GetUnassignedUsers()
        {
            _context.Jobs.OrderByDescending(j => j.ID).FirstOrDefault();       
            var userlist = _context.Users.Where(u => !u.Registrations.Any());
            return Json(userlist);
        }

        /// <summary>
        /// Action that displays table showing assigned users
        /// </summary>
        /// <returns>A list of users assigned to currently selected Job</returns>
        public IActionResult GetAssignedUsers()
        {
            try
            {
                _context.Jobs.OrderByDescending(j => j.ID).FirstOrDefault();       
                
            }
            
            catch(DbUpdateException /* ex */)
            {
                //Log the error (uncomment ex variable name and write a log).
                ModelState.AddModelError("", "Unable to save changes. " +
                "Try again, and if the problem persists " +
                "See your system administrator.");
            }
            var userlist = _context.Users.Where(u => u.Registrations.Any());
            return Json(userlist);
        }

        //TO DO: Fix method below.

        /// <summary>
        /// NullReferenceException: Object reference not set to an instance of an object.
        /// This method unassign the registration in memory for the currently selected Job.
        /// </summary>
        /// <param name="RegistrationID"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> UnassignUserRegistration(int RegistrationID)
        {
            var user = _context.Users.Include(c => c.Registrations).FirstOrDefault(c => c.ID == RegistrationID);
            foreach (var item in user.Registrations)
            {
                if (user!=null)
                {
                     _context.Entry(item).State = EntityState.Deleted; //delete items from the Join table(registrations).  
                                 //_context.Entry(user).State = EntityState.Deleted; //delete the user //
                    await _context.SaveChangesAsync();             
                }               
            }
            return RedirectToAction(nameof(UserAssignments), new{ID = RegistrationID});
        }

        /// <summary>
        /// Opens up the UserAssignments view page, using the
        /// currently selected JobID in memory.
        /// </summary>
        /// <param name="ID"></param>
        /// <returns>The currently selected Job in memory</returns>
        public IActionResult UserAssignments(int? ID)
        {
            if (ID == null)
            {
                return NotFound();
            }
            var job = _context.Jobs.Find(ID);
            return View(job);
        }
#endregion User Registrations


I will try not to give too much code but here are the related UnserAssignments.cshtml. I have omitted the currently selected Job controller and models for brevity. Please let me know if you need it and I will update and add it to the question! No need to write it from scratch!

Please look inside the @section scripts in the unassignedUsersTable where I am executing the broken method.

ASP.NET
@model Pitcher.Models.Job
@{ 
    var user = new User();
}
@{
    ViewData["Title"] = "UserAssignments";
}
<div>
    <h4>Job</h4>
    <hr />
    <dl class="row">
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.JobTitle)
        </dt>
        <dd class = "col-sm-10"> 
            @Html.DisplayFor(model => model.JobTitle)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.JobDescription)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.JobDescription)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.JobStartDate)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.JobStartDate)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.JobDeadline)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.JobDeadline)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.JobIsComplete)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.JobIsComplete)
        </dd>
    </dl>
</div>
<h3>Unassigned Users</h3>
<table id="unassignedUsersTable" style='display:none;'>
<thead>
        <tr>
            <th>
               @Html.DisplayNameFor(model => user.UserFirstName)
            </th>        
            <th>
               @Html.DisplayNameFor(model => user.UserLastName)
            </th>
            <th>
               @Html.DisplayNameFor(model => user.UserContactEmail)
            </th>
            <th>                
            </th>
        </tr>
    </thead>
    @if(user == null)
    {
         <script type="text/javascript">
            alert("Model empty");
        </script>        
    }
    else
    {
        <tbody></tbody>
    }
</table>

<h3>Assigned Users</h3>
<table id="registeredUsersTable" style="display: none">
<thead>
        <tr>
                <th>
                @Html.DisplayNameFor(model => user.UserFirstName)
                </th>        
                <th>
                @Html.DisplayNameFor(model => user.UserLastName)
                </th>
                <th>
                @Html.DisplayNameFor(model => user.UserContactEmail)
                </th>
            <th>                
            </th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

@section scripts{
    <script src="~/js/jquery.dataTables.min.js"></script>
    <script>    
    $.fn.dataTable.ext.errMode = 'throw';
    //Reveal table if button click successful.  
        document.getElementById('unassignedUsersTable').style.display = 'block'; 
        var id=@Model.ID
        $('#unassignedUsersTable').DataTable({
                "ajax": {
                'type': 'get',
                'data': { ID: id},
                'dataType': "json",
                //Get users from UsersController.                  
                "url": "@Url.Action("GetUnassignedUsers")",
                "dataSrc": function (result) {
                    return result;
                    }
                },            
            "columns": [                
                { "data": "userFirstName"},
                { "data": "userLastName"},
                { "data": "userContactEmail"},
                {
                    "data": null,
                    "render": function (value) {
                        return  '<a href="/Jobs/AssignUserRegistration?UserID=' + value.id + '&JobID=' + id +' "button type="button" class="btn btn-primary btn-block">Assign</a>';
                    }
                }
                ]
        });

        document.getElementById('registeredUsersTable').style.display = 'block';
        var id=@Model.ID 
        $('#registeredUsersTable').DataTable({
            "ajax": {
            'type': 'get',
            '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?RegistrationID=' + value.id + '"button type="button" class="btn btn-primary btn-block">Unassign</a>';
            }
                }
            ]
        });
    </script>
}


Below are is TeamContext.cs. I have omitted the models and the properties are all listed in the Database Diagram above but not with the types. Please let me know if you need the 3 models listed and I will update the question so you don't have to create all the models yourself.

C#
using Pitcher.Models;
using Microsoft.EntityFrameworkCore;
using Pitcher.Models.TeamViewModels;

namespace Pitcher.Data
{
    public class TeamContext : DbContext
    {
        public TeamContext(DbContextOptions<TeamContext> options) : base(options)
        {
        }

        public DbSet<User> Users { get; set; }
        public DbSet<Registration> Registrations {get;set;}
        public DbSet<Job> Jobs {get;set;}     

        public DbSet<Problem> Problems { get; set; }   

        public DbSet<Result> Results {get;set;}
        
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>().ToTable("tblUser");
            modelBuilder.Entity<Registration>().ToTable("tblRegistration");
            modelBuilder.Entity<Job>().ToTable("tblJob");
            modelBuilder.Entity<Problem>().ToTable("tblProblem");
            modelBuilder.Entity<Chat>().ToTable("tblChat");
            modelBuilder.Entity<Result>().ToTable("tblResult");
            modelBuilder.ApplyConfigurationsFromAssembly(typeof(AccessTokenCacheConfiguration).Assembly);
        }        
    }
}


What I have tried:

Stepping through the code has revealed nothing useful. All I notice is all the records are displaying null job information in the watches page of VS Code regardless of which registration record I select. But the JobID is still displaying correctly in the watches.
Posted
Updated 25-Aug-21 19:00pm
v4

The NullReferenceException errors have nothing to do with database records. They are raised when you try to use a variable reference that has not been initialised. So you need to use your debugger to find out which reference is causing it and why.
   
Comments
Jordan Nash 12-Aug-21 7:21am
   
Already did all that as explained. I cannot see anything out of the ordinary other than what I said. Here is a gif I'm sharing if you can see what I'm doing wrong, please. https://s6.gifyu.com/images/Animation1cff8e6cf7f6fdd3d.gif
Richard MacCutchan 12-Aug-21 7:36am
   
It is impossible for anyone here to find the error as it requires the running of your code. The error message points to "UserAssignments.cshtml, line 100", so that is a good place to start looking.
As far as I can see, the line which causes the error is:
Razor
var id=@Model.ID
That would suggest your model is null. Which means _context.Jobs.Find(ID) is returning null, which you never test for.

Incidentally, you have multiple lines of the form:
C#
_context.Jobs.OrderByDescending(j => j.ID).FirstOrDefault();
Since you're not storing or using the returned value in any way, this is issuing a database query for no reason. You should remove these lines.
   
Comments
Jordan Nash 12-Aug-21 6:57am
   
Yes I knew that. I forgot to delete that with _context.Jobs not doing anything. The reason why I put it in was I was studying the error https://docs.microsoft.com/en-us/dotnet/api/system.nullreferenceexception?view=net-5.0 and found out I wasn't instantiating Jobs. I'm not convinced instantiating it will help as the error only occurs with some records. If I delete var id=@model.ID in one place it will not change the output except it will report the error in the other table containing the variable. But if I delete the variable from both tables then it will stop the data appearing in the tables.
Richard Deeming 12-Aug-21 8:51am
   
If the Model is null, you'll get a NullReferenceException when you try to access a property of it.

You either need to check for null in the action and return a different view if there is no matching record, or check for null in the view and output a suitable default value.

Eg:
var id = @(Model == null ? 0 : Model.Id);
Jordan Nash 17-Aug-21 2:31am
   
This problem gets even stranger. So when I use that code you made to check for null in the action, the error will be gone now regardless of which record it is. so I swapped my original line for yours. But if I assign a user now, I will get this error.
```
An unhandled exception occurred while processing the request.
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblRegistration_tblJob_JobID". The conflict occurred in database "Pitcher8", table "dbo.tblJob", column 'ID'.
The statement has been terminated.
```
Richard Deeming 17-Aug-21 3:30am
   
That error means you are trying to insert a record with a JobID that doesn't exist in the tblJob table.

This comes down to the same error: you are passing in an invalid ID to your UserAssignments action.

You need to debug your code to find out why.
Jordan Nash 20-Aug-21 4:34am
   
So I have used breakpoints and found the JobID returned as 0. Other than that I cannot find why I'm getting this error. I have displayed the migration in my question today where I'm getting the error from FK_tblRegistration_tblJob_JobID
Richard Deeming 20-Aug-21 5:59am
   
The problem is with the parameter you're passing in to your UserAssignments action. Which is a problem with how you're linking to that action.

As I keep saying, you need to debug your code to find out why you are passing an invalid parameter to that action.
Jordan Nash 22-Aug-21 3:33am
   
So I read this https://docs.microsoft.com/en-us/visualstudio/debugger/debugger-feature-tour?view=vs-2019#set-a-watch
Then set watches on JobID and ID in the controller.

The strange thing is if I get out of the page and go back to it I notice when I debug that the ``JobID`` is being passed to it fine but then I unassign the user (delete the registration) and assign them again. That's when 0 is assigned to JobID. If any mentor is willing to Teamview or something with me please PM me and I can workout some sort of payment for your time. Else please advise me how I should debug further, thanks.
Ok so the problem with the UnassignUserRegistration method was that I misinterpreted the RegistrationID as the actual ID being passed in.

When I hover over the unassign button that contains that method I could see the URL and that the function was expecting the UserID and the JobID and not the RegistrationID.

The fix involved me putting into the parameters of the method the UserID and the JobID and then using the
C#
.Single
method to return
both UserID and JobID.

Then I redirect to action just the JobID as that's all that is needed.

I then in the view just had to return.

ASP
UserID=' + value.id + '&JobID=' + id +'


And no errors occurred after that.

JobController.cs

[HttpGet]
        public async Task<IActionResult> UnassignUserRegistration(int UserID, int JobID)
        {
            Registration registration = _context.Registrations.Single(c => c.UserID == UserID && c.JobID == JobID);     
            _context.Entry(registration).State = EntityState.Deleted; 
            await _context.SaveChangesAsync();                             
            return RedirectToAction(nameof(UserAssignments), new{ID = JobID});
        }


UserAssignments.cshtml

JavaScript
document.getElementById('registeredUsersTable').style.display = 'block';

$('#registeredUsersTable').DataTable({
    "ajax": {
    "url": "@Url.Action("GetAssignedUsers")",
    "dataSrc": function (result) {
        return result;
        }
    },
    "columns": [
    { "data": "userFirstName"},
    { "data": "userLastName"},
    { "data": "userContactEmail"},
    {
    "data": null,
    "render": function (value) {
        return  'Unassign';
    }
        }
    ]
});
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900