Cannot display related data with datatables in ASP.NET core

Question

0.00/5 (No votes)

See more:

, +

Do not know how to display related data in my "Details" view with the DataTables plugin where JobID has related Problems. Using Data Tables to render the data across 3 tables in this relationship structure:

Job 1-m Result m-1 Problem.

Steps I took to reproduce the problem:

I download datatables here and install under wwwroot/js/:
Download[^]

Here is the code.

C#

public class JobsController : Controller
    {
        private readonly TeamContext _context;

        public JobsController(TeamContext context)
        {
            _context = context;
        }

        // GET: Jobs
        // COPY AND PASTE THIS METHOD CUSTOMIZATION INTO OTHER CONTROLLERS. Enables sorting. 
        public IActionResult Index()
        {
            return View();
        }

        public IActionResult GetAllJobs()
        {
            return Json(_context.Jobs.ToList());
        }

        public IActionResult GetAllProblemByJobId(int? ID)
        {
            if (ID == null)
            {
                return NotFound();
            }
            var resultlist = _context.Results.Where(r => r.JobID == ID).ToList();
            var problemlist = new List<Problem>();
            resultlist.ForEach(r =>
            {
                r.Problem = _context.Problems.Find(r.ProblemID);
                if (r.Problem != null)
                { problemlist.Add(r.Problem); }
            });
            return Json(problemlist);
        }

        // GET: Jobs/Details/5
        // COPY AND PASTE THIS METHOD CUSTOMIZATION INTO OTHER CONTROLLERS.  
        public IActionResult Details(int? ID)
        {
            if (ID == null)
            {
                return NotFound();
            }
            var job = _context.Jobs.Find(ID);
            return View(job);
        }
}

C#

public class Job
{

    public int ID { get; set; }

    [Required]
    [StringLength(20, MinimumLength = 3, ErrorMessage = "Job Title must be bettween 3 to 20 characters.")]
    [DataType(DataType.Text)]
    [Display(Name = "Job Title")]
    [Column("JobTitle")]
    public string JobTitle { get; set; }

    [StringLength(200, MinimumLength = 3, ErrorMessage = "Job Description must be bettween 200 to 3 characters.")]
    [DataType(DataType.Text)]
    [Display(Name = "Description")]
    [Column("JobDescription")]
    public string JobDescription { get; set; }

    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = " Start Date")]
    [Column("JobStartDate")]
    public DateTime JobStartDate {get;set;}

    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Deadline Date")]
    [Column("JobDeadlineDate")]
    public DateTime JobDeadline {get;set;}

    [Display(Name = "Job Is Complete?")]
    [Column("JobIsComplete")]
    public bool JobIsComplete{get;set;}

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

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

C#

public class Result
{
    [Key]
    public int ID {get;set;}
    public int JobID {get;set;}

    public int ProblemID {get;set;}
    public Job Job {get;set;}
    public Problem Problem {get;set;}
}

Jobs\Index

C#

@model Pitcher.Models.Job
@{
    ViewData["Title"] = "Jobs";
}   
<h1>Jobs</h1>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table id="jobTable" class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.ID)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.JobTitle)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.JobDescription)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.JobStartDate)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.JobDeadline)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.JobIsComplete)
            </th>
            <th>
            </th>
        </tr>
    </thead>
    <tbody></tbody>
</table>
@section scripts{ 
    <script src="~/js/jquery.dataTables.min.js"></script>
    <script>
        var oTable = $('#jobTable').DataTable({
                "ajax": {
                type: 'get',
                'dataType': "json",
                "url": "@Url.Action("GetAllJobs")",
                "dataSrc": function (result) {
                    return result;
                    }
                },
            "columns": [
                { "data": "ID"},
                { "data": "JobTitle"},
                { "data": "JobDescription"},
                { "data": "JobStartDate"},
                { "data": "JobDeadline"},
                {
                    "data": "JobIsComplete",
                    "render": function (data) {
                        if (data) {
                            return "IsComplete";
                        } else {
                            return "Not Complete";
                        }
                    }
                },
                {
                    "data": null,
                    "render": function (value) {
                        return 
'<a href="/Jobs/Details/' + value.ID + '"button type="button" class="btn btn-primary btn-block">Details</a> <br> '
                            
                            + 
'<a href="/Jobs/Edit/' + value.ID + '"button type="button" class="btn btn-info btn-block">Edit </a> <br> ' 
                            
                            + 
'<a href="/Jobs/Delete/' + value.ID + '"button type="button" class="btn btn-primary btn-block">Delete</a>';
                    }
                }
                ]
        });
    </script>
}

Jobs\Details

C#

@model Pitcher.Models.Job
@using Pitcher.Models
@{ 
    var problem = new Problem();
}
@{
    ViewData["Title"] = "Details";
}

<h1>Details</h1>
<table id="CurrentJobTable" class="table">
    <thead>
        <tr>
            <th>@Html.DisplayNameFor(model => model.ID)</th>
            <th>@Html.DisplayNameFor(model => model.JobTitle)</th>
            <th>@Html.DisplayNameFor(model => model.JobDescription)</th>
            <th>@Html.DisplayNameFor(model => model.JobStartDate)</th>
            <th>@Html.DisplayNameFor(model => model.JobDeadline)</th>
            <th>@Html.DisplayNameFor(model => model.JobIsComplete)</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>@Html.DisplayTextFor(model => model.ID)</td>
            <td>@Html.DisplayTextFor(model => model.JobTitle)</td>
            <td>@Html.DisplayTextFor(model => model.JobDescription)</td>
            <td>@Html.DisplayTextFor(model => model.JobStartDate)</td>
            <td>@Html.DisplayTextFor(model => model.JobDeadline)</td>
            <td>@Html.DisplayTextFor(model => model.JobIsComplete)</td>
        </tr>
    </tbody>
</table>
<table id="ProblemsTable" class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(problemmodel => problem.ID)
            </th>
            <th>
                @Html.DisplayNameFor(problemmodel => problem.ProblemTitle)
            </th>
            <th>
                @Html.DisplayNameFor(problemmodel => problem.ProblemDescription)
            </th>
            <th>
                @Html.DisplayNameFor(problemmodel => problem.ProblemStartDate)
            </th>
            <th>
                @Html.DisplayNameFor(problemmodel => problem.ProblemFileAttachments)
            </th>
            <th>
                @Html.DisplayNameFor(problemmodel => problem.ProblemSeverity)
            </th>
            <th>
                @Html.DisplayNameFor(problemmodel => problem.ProblemComplete)
            </th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

@section scripts{
    <script src="~/js/jquery.dataTables.min.js"></script>
    <script>
        var id=@Model.ID;
        $('#ProblemsTable').DataTable({
                "ajax": {
                'type': 'get',
                'data': { ID: id},
                'dataType': "json",
                "url": "@Url.Action("GetAllProblemByJobId")",
                "dataSrc": function (result) {
                    return result;
                    }
                },
            "columns": [
                { "data": "ID"},
                { "data": "ProblemTitle"},
                { "data": "ProblemDescription"},
                { "data": "ProblemStartDate"},
                { "data": "ProblemFileAttachments"},
                { "data": "ProblemSeverity" },
                { "data": "ProblemComplete" }
                ]
        });
    </script>
}

Expected behavior

It renders the problem.

Actual behavior

It doesn't render the problem.

Environment data
`.NET Core SDK (reflecting any global.json):
Version: 2.2
Runtime Environment:
OS Name: Windows
OS Version: 10.0.18363
OS Platform: Windows
RID: win10-x64

What I have tried:

Searched all over the internet and cannot find an answer to my problem sadly. So to be explicit I want to display all Problem records in the Detail View but are unsure of how to do that correctly.

I used this tutorial but my logic is different because in the tutorial they are doing it in the Index while I am doing it in the Detail view.

Posted 25-Jun-20 2:08am

Jordan Nash

Updated 29-Jun-20 0:05am

#realJSOP

v2

Add a Solution

Comments

Richard Deeming 25-Jun-20 10:06am

There's nothing obviously wrong with your code that I can see. It's probably an issue with your data, which we don't have access to.

Check your browser's developer console for errors. Make sure the XHR request for the GetAllProblemByJobId action is being made, and contains the expected parameters. Check whether it's returning any errors. Debug your server-side code to see if there's anything wrong with the query.

NB: You can simplify your query and improve the performance by using the navigation property:

public IActionResult GetAllProblemByJobId(int? ID)
{
    if (ID == null) return NotFound();
    
    var problemlist = _context.Results.Where(r => r.JobID == ID).Select(r => r.Problem).ToList();
    return Json(problemlist);
}

Jordan Nash 29-Jun-20 0:35am

Thank you. Your code has actually solved my problem and all the listed problems appear now.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2020-06-29T00:05:00

As discussed in the comments, there's nothing obviously wrong with your code that I can see. It's probably an issue with your data, which we don't have access to.

Check your browser's developer console for errors. Make sure the XHR request for the GetAllProblemByJobId action is being made, and contains the expected parameters. Check whether it's returning any errors. Debug your server-side code to see if there's anything wrong with the query.

NB: You can simplify your query and improve the performance by using the navigation property:

C#

public IActionResult GetAllProblemByJobId(int? ID)
{
    if (ID == null) return NotFound();
    
    var problemlist = _context.Results.Where(r => r.JobID == ID).Select(r => r.Problem).ToList();
    return Json(problemlist);
}