c#asp.net-core model-view-controller entity-framework-core dropdown

Changing Drop down list ASP.NET CORE

I am using ASP.NET CORE with SQL server.

I have 2 tables: Review and WorkSchedule. The WorkSchedule table contains ManagerID and StaffID. The Review table contains ReviewerID and RevieweeID. The ManagerID,StaffID,ReviewerIDandRevieweeID are foreign keys to StaffID in the Staff table.

For example, if I create a new review in the CreateReview page, I will manually set the ReviewerID and RevieweeID. I want to make the WorkScheduleID dropdown to automatically change so that WorkScheduleID will appear where ManagerID = ReviewerID and StaffID = RevieweeID

Model:

namespace Starbucks.Models
{
    public class ReviewModel
    {
        [Key]
        [Display(Name = "Review ID")]
        public Guid ReviewID { get; set; }


        [Display(Name = "Reviewer")]
        public Guid ReviewerID { get; set; }
        [ForeignKey("ReviewerID")]
        public StaffModel Staff { get; set; }


        [Display(Name = "Reviewee")]
        public Nullable<Guid> RevieweeID { get; set; }
        [ForeignKey("RevieweeID")]
        public StaffModel Staffs { get; set; }


        [Display(Name = "WorkSchedule ID")]
        public Nullable<Guid> WorkScheduleID { get; set; }
        [ForeignKey("WorkScheduleID")]
        public WorkScheduleModel WorkSchedules { get; set; }
    }


    public class WorkScheduleModel
    {

        [Key]
        [Display(Name = "Work Schedule ID")]
        public Guid WorkScheduleID { get; set; }


        [Display(Name = "Staff ID")]
        public Nullable<Guid> StaffID { get; set; }
        [ForeignKey("StaffID")] 
        public StaffModel Staffs { get; set; }



        [Display(Name = "Manager ID")]
        public Guid ManagerID { get; set; }
        [ForeignKey("ManagerID")]
        public StaffModel Staff { get; set; }
    }
}

Controller:

[HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> CreateReview([Bind("ReviewID,ReviewCategoryID,ReviewRating,ReviewComment,ReviewDateTime,ReviewerID,RevieweeID,WorkScheduleID")] ReviewModel reviewModel)
        {
            if (ModelState.IsValid)
            {      
                reviewModel.ReviewID = Guid.NewGuid();
                reviewModel.ReviewDateTime = DateTime.Now;
                _context.Add(reviewModel);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(ProfilePage));
            }
            ViewData["RevieweeID"] = new SelectList(_context.Staff, "StaffID", "StaffName", reviewModel.RevieweeID);
            ViewData["ReviewerID"] = new SelectList(_context.Staff, "StaffID", "StaffName", reviewModel.ReviewerID);
            ViewData["WorkScheduleID"] = new SelectList(_context.WorkSchedule, "WorkScheduleID", "WorkScheduleID");
            return View(reviewModel);
        }

View Page:

<body>
    <div class="row">
        <div class="col-md-8">
            <form asp-action="CreateReview">
                <div asp-validation-summary="ModelOnly" class="text-danger"></div>                
                <div class="form-group">
                    <label asp-for="ReviewerID" class="control-label"></label>
                    <select asp-for="ReviewerID" class="form-control" asp-items="ViewBag.ReviewerID"></select>
                </div>
                <div class="form-group">
                    <label asp-for="RevieweeID" class="control-label"></label>
                    <select asp-for="RevieweeID" class="form-control" asp-items="ViewBag.RevieweeID"></select>
                </div>
                <div class="form-group">
                    <label asp-for="WorkScheduleID" class="control-label"></label>
                    <select asp-for="WorkScheduleID" class="form-control" asp-items="ViewBag.WorkScheduleID"></select>
                </div>
                <div class="form-row">
                    <div class="form-group col-md-6">
                        <input type="submit" value="Save" class="btn btn-primary btn-block" />
                    </div>
                </div>
            </form>
        </div>
    </div>
</body>

Solution

You can create a method to filter the data, and use ajax to call it, then populate the select list with the returned data.

Controller:

public IActionResult Index()
{

    ViewData["RevieweeID"] = new SelectList(_context.Staff, "StaffID", "StaffName");
    ViewData["ReviewerID"] = new SelectList(_context.Staff, "StaffID", "StaffName");
    ViewData["WorkScheduleID"] = new SelectList(_context.WorkSchedule, "WorkScheduleID", "WorkScheduleID");
    return View();
}

[HttpGet]
public IActionResult FilterWorkScheduleID(Guid reviewerid, Guid revieweeid)
{
    var WorkScheduleIDs = _context.WorkSchedule.Where(x => x.StaffID == revieweeid && x.ManagerID == reviewerid).ToList();
    return Json(new { items = WorkScheduleIDs });
}

View:

@model ReviewModel
@{
    ViewData["Title"] = "Index";
}

<h1>Index</h1>

<div class="row">
    <div class="col-md-8">
        <form asp-action="CreateReview">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="ReviewerID" class="control-label"></label>
                <select asp-for="ReviewerID" class="form-control" asp-items="ViewBag.ReviewerID"></select>
            </div>
            <div class="form-group">
                <label asp-for="RevieweeID" class="control-label"></label>
                <select asp-for="RevieweeID" class="form-control" asp-items="ViewBag.RevieweeID"></select>
            </div>
            <div class="form-group">
                <label asp-for="WorkScheduleID" class="control-label"></label>
                <select asp-for="WorkScheduleID" class="form-control" asp-items="ViewBag.WorkScheduleID"></select>
            </div>
            <div class="form-row">
                <div class="form-group col-md-6">
                    <input type="submit" value="Save" class="btn btn-primary btn-block" />
                </div>
            </div>
        </form>
    </div>
</div>

@section scripts{ 
    <script>
        $("select").on("change", function () {
            var reviewerID = $("#ReviewerID").find(":selected").val();
            var revieweeID = $("#RevieweeID").find(":selected").val();
            $.ajax({
                type: 'get',
                url: '/Staff/FilterWorkScheduleID',
                data: {
                    reviewerid: reviewerID,
                    revieweeid:revieweeID
                },
                success: function (result) {
                    $("#WorkScheduleID").empty();
                    $.each(result.items, function (i, obj) {
                        var option = "<option value=" + obj.workScheduleID + ">" + obj.workScheduleID + "</option>";
                        $(option).appendTo("#WorkScheduleID"); 
                    })
                
                }
            })
        })
    </script>
}

Result: