locked
Do not know how to display related data with paging sorting and searching RRS feed

  • Question

  • User-872746141 posted

    How do I display related data in Details view with Sorting logic?

    Using this tutorial for it except I'm trying to do it in the Details view. I want it to have paging sorting and searching in controller.
    https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/read-related-data?view=aspnetcore-3.1

    Searched all over the internet. Cannot find an answer.

    So to be explicit I want to display all Problem records in the Job\Detail view but are unsure of how to do that, especially with sort and paging logic.

    If anyone can help please, then here is code for my Job model:

    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;}
    }

    Result model:

    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;}
    }

    Problem model:

    public class Problem
    {
    public int ID {get;set;}
    
    [Required]
    [StringLength(180, MinimumLength = 2, ErrorMessage = "Problem Title must be bettween 2 to 20 characters.")]
    [DataType(DataType.Text)]
    [Display(Name = "Problem Title")]
    [Column("ProblemTitle")]
    public string ProblemTitle {get;set;}
    
    [Required]
    [StringLength(int.MaxValue, MinimumLength = 5, ErrorMessage = "Problem Title must be at least 5 characters.")]
    [DataType(DataType.Text)]
    [Display(Name = "Problem Description")]
    [Column("ProblemDescription")]
    public string ProblemDescription {get;set;}
    
    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = " Problem Start Date")]
    [Column("ProblemStartDate")]
    public DateTime ProblemStartDate {get;set;}
    
    [DataType(DataType.Upload)]
    [Display(Name = " Upload file")]
    [Column("ProblemFileAttachments")]
    public string ProblemFileAttachments {get;set;}
    
    [Required]
    [Display(Name = "Problem Severity")] 
    [Range(1,5, ErrorMessage
    = "Problem Severity value for {0} must be between {1} and {2}.")] 
    [Column("ProblemSeverity")]
    public int ProblemSeverity {get;set;}
    
    [Display(Name = "Problem Complete")] 
    [Column("ProblemComplete")] 
    public bool ProblemComplete {get;set;}
    
    public ICollection<Result> Result {get;set;}
    
    public ICollection<Chat> Chat {get;set;}
    }

    JobsController:

    public async Task<IActionResult> Details(int? id, int? problemID)
    {
    if (id == null)
    {
    return NotFound();
    }
    
    var viewModel = new JobIndexData();
    viewModel.Jobs = await _context.Jobs 
    .Include(j => j.Results)
    .ThenInclude(j => j.Problem)
    .AsNoTracking()
    .OrderBy(j => problemID)
    .ToListAsync();
    //.FirstOrDefaultAsync(m => m.ID == id);
    
    if (id != null)
    {
    ViewData["JobID"] = id.Value;
    Job job = viewModel.Jobs.Where(
    j => j.ID == id.Value).Single();
    viewModel.Problems = job.Results.Select(s => s.Problem);
    }
    
    if (problemID != null)
    {
    ViewData["ProblemID"] = problemID.Value;
    }
    
    return View(viewModel);
    }

    Job (Details) view:

    @model Pitcher.Models.Job
    
    @{
    ViewData["Title"] = "Details";
    }
    <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>
    <div>
    <a asp-action="Edit" asp-route-id="@Model.ID">Edit</a> |
    <a asp-action="Index">Back to List</a>
    </div>

    Model JobIndexData

        public class JobIndexData
        {
            public IEnumerable<Job> Jobs { get; set; }
            public IEnumerable<Problem> Problems { get; set; }
    
            public IEnumerable<Result> Results { get; set; }
        }


    Saturday, June 13, 2020 11:04 AM

All replies

  • User1686398519 posted

    Hi,  287papa

    • You need to delete "public ICollection<Result> Result {get; set; }" in your model, which will lead to "Self referencing loop" error.
    • According to your needs, I made a detailed example, please refer to it.
      • You can use datables, which can help you achieve sorting and paging.
      • For more information about datables, please refer to this link.

    Controller

            private readonly ModelContext _context;
            public JobsController(ModelContext db)
            {
                _context = db;
            }
            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);
            }
            public IActionResult Details(int? ID)
            {
                if (ID == null)
                {
                    return NotFound();
                }
                var job = _context.Jobs.Find(ID);
                return View(job);
            }

    Index

    @model CoreTest.Models.Job
    @{
        ViewData["Title"] = "Index";
    }   
    <h1>Index</h1>
    <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 + '">Details</a>';
                        }
                    }
                    ]
            });
        </script>
    }

    Details

    @model CoreTest.Models.Job
    @using CoreTest.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>
    }
    

    Startup.cs

                services.AddControllers().AddNewtonsoftJson();
                services.AddControllers().AddNewtonsoftJson(options =>
                {
                    options.SerializerSettings.ContractResolver = new DefaultContractResolver();
                });

    Here is the result.

     
    Best Regards,

    YihuiSun

    Monday, June 15, 2020 10:13 AM
  • User-872746141 posted

    Hi YihuiSun,

    I was sick before. I apologize but I forgot to mention I was using  ASP.NET Core 2.2 so the services in the Startup.cs code file are not compatible. I will have to use Jon Skeet's famous checklist from now on how I answer questions! Is it possible to update your solution to use 2.2 please?

    Thursday, June 18, 2020 11:37 AM
  • User1686398519 posted

    Hi 287papa,

    If you are using ASP.NET Core 2.2, you can write this in the Startup.cs file.

                services.AddMvc().AddJsonOptions(options =>
                {
                    options.SerializerSettings.ContractResolver = new DefaultContractResolver();
                });

    Best Regards,

    YihuiSun

    Friday, June 19, 2020 2:32 AM
  • User323983933 posted

    For sorting, I assume your details page has something like

    foreach(var item in model.somelist)
    {
    @* code to display the details data here *@
    }

    You can add this to the foreach...

    foreach(var item in Model.subitems.OrderBy(d=>d=>d.something))
    
    

    if you're talking about sorting on the fly without a postback, this will not help. Then you're talking JQuery.

    Friday, June 19, 2020 3:21 AM
  • User-872746141 posted

    Ah thanks for that. Just had to put Newtonsoft.Json.Serialization; namespace for the DefaultContractResolver. However your ProblemsTable code for the details page sadly doesn't work.

    Problem doesn't exist for that view.

    That's because Problem table is in a three table relationship connecting to Result then Job. So if you look back at the navigational properties at the very bottom of my Jobs model you'll see there is no navigational property for Problems, only Results and Registrations.

    I did this to avoid a many to many relationship with Problems and Jobs. Result is the composite table. This diagram will hopefully demonstrate to you what that looks like.

    Friday, June 19, 2020 4:45 AM
  • User1686398519 posted

    Hi 287papa,

    In order to test and display the results, I used EF core to connect to the database, so the "Problems" in "r.Problem = _context.Problems.Find(r.ProblemID);" is different from "Registrations" in your code."Problems" is in the ModelContext.

    ModelContext

        public class ModelContext : DbContext
        {
            public ModelContext(DbContextOptions<ModelContext> options) : base(options)
            {
            }
            public DbSet<Job> Jobs { get; set; }
            public DbSet<Problem> Problems { get; set; }
            public DbSet<Result> Results { get; set; }
        }

    Best Regards,

    YihuiSun

    Friday, June 19, 2020 9:57 AM
  • User-872746141 posted

    Hi YihuiSun,

    Yeah I am not using registrations for this. I forgot to ommit that. It connects with Job so you don't need to worry about it. My context is more detailed and looks like this.

    public class TeamContext : DbContext
        {
            public TeamContext(DbContextOptions<TeamContext> options) : base(options)
            {
            }
    
            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<Job>().ToTable("tblJob");
                modelBuilder.Entity<Problem>().ToTable("tblProblem");
                modelBuilder.Entity<Result>().ToTable("tblResult");
    
                // modelBuilder.Entity<Result>()
                    // .HasKey(bc => new { bc.JobID, bc.ProblemID });
                modelBuilder.Entity<Result>()
                    .HasOne(bc => bc.Job)
                    .WithMany(b => b.Results)
                    .HasForeignKey(bc => bc.JobID);
                modelBuilder.Entity<Result>()
                    .HasOne(bc => bc.Problem)
                    .WithMany(c => c.Result)
                    .HasForeignKey(bc => bc.ProblemID);
            }        
        }
    }

    All that code in  OnModelCreating under the commented out code can be ignored. I realize it's not doing anything. Anway thanks my index is working amazingly well now!! I just have to figure out how to display the related table in Details.

    Saturday, June 20, 2020 10:57 AM
  • User-872746141 posted

    Sorry still can't work it out. 

    Tuesday, June 23, 2020 12:19 PM