How to filter for attributes from database

Question

I am using visual studio 2017 and have created a standard ASP.NET/MVC Project with the Entity Framework, finally a local database is included. I have the Class "Courses" and "Categories". One Course can have one categorie, and one categorie can be in many courses. (n:1 relationship)

My Model is for my course.cs:

namespace project.Models
{
    public class Course
    {
        public int ID { get; set; }        
        public int CategoryID { get; set; }
        public string Name { get; set; }
        public string  Description { get; set; }

        public virtual CourseCategory CourseCategory { get; set; }

    }

Model for the courseCategory.cs is:

    namespace project.Models
{
    public class CourseCategory
    {
        public int ID { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Course> Course { get; set; }
    }
}

The view is:

@model IEnumerable<Models.Courses>

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

<h2>Index</h2>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table">
    <thead>
        <tr>
                <th>
                    @Html.DisplayNameFor(model => model.ID)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.CategoryID)
                </th>                    
                <th>
                    @Html.DisplayNameFor(model => model.Name)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Description)
                </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.ID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.CategoryID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Description)
            </td>
            <td>
                <a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |
                <a asp-action="Details" asp-route-id="@item.ID">Details</a> 
                <a asp-action="Delete" asp-route-id="@item.ID">Delete</a>
            </td>
        </tr>
}
    </tbody>
</table>

Derived from these two classes i created the controller and the views automatically. I do "add-migration -xx" and "update-database" and my application is runnable. I open "http://localhost/courses" and i have list of my courses (inluded the given category of the course) and can add, move delete, etc... If i have a look into my database/tables i can see the right primary- and foreign keys. It works....

But now, i want to add a possibility to filter my courses. I need an extra part in this side, where my categories are listed and if i click on one of these categories, i want a new resultlist of all course with the clicked categorie.

How can i realize it and where i have to modify the code? I tried to modify the CourseController.cs

  // GET: Course
    public async Task<IActionResult> Index()
    {
        return View(await _context.Course.ToListAsync());
    }

I guess i need here somethink like a comparison of the primary keys in the tables?

Answer 1

This is what you need

1. A dropdown in your view where user can select a category
2. Changes to your action method where it will accept the selected category and get the courses under that category.

There are multiple ways to do this. This is how i will do it.

I like to not mix the entity classes created by ORM in my UI layer. So i create a view model specific to the view

public class CourseListVm
{
   public int? CategoryId { set;get;}
   public List<SelectListItem> Categories { set;get;}
   public IEnumerable<CourseVm> Courses { set;get;}
   public CourseListVm()
   {
       this.Courses = new List<CourseVm>();
   }
}
public class CourseVm
{
   public int Id { set;get;}
   public string Name { set;get;}
}

Now in your action method, create an object of this, load available categories from your database to the Categories property. We will add a parameter to the action method and check if there is a value for selected catetory (from the ui) and if yes ,we will do the filtering when populating the Courses property

public async Task<IActionResult> Index(int? categoryId)
{
    var vm=new CourseListVm();
    //Populate the catetory list so that we can use that in UI to build the dropdown
    vm.Categories = _context.Categories
                            .Select(a => new SelectListItem() {Value = a.Id.ToString(), 
                                                               Text = a.Name})
                            .ToList();
    List<Course> courseList = new List<Course>();
    if(categoryId==null)
    {
      courseList = await _context.Course.ToListAsync()
    }
    else
    {
      courseList = await _context.Course
                     .Where(a=>a.CategoryId==categoryId.Value)
                     .ToListAsync()
    }
    vm.Courses = GetCourseVms(courseList);
    return View(vm);
}
private IEnumerable<CourseVm> GetCourseVms(IEnumerable<Course> courses)
{
    return courses.Select(a=>new CourseVm { Id=a.Id, Name =a.Name});
}

I am using the GetCourseVms method to map the Course entity to the CourseVm view model object. You can use a library like Auto mapper to do so if you prefer that.

Now in your view, which is strongly typed to the CourseListVm, we will render a dropdown for the categories using the DropDownListFor helper method. If you are doing asp.net core app, you can use the Select tag helper to render the dropdown

@model CourseListVm
@using (Html.BeginForm("Index", "Courses",FormMethod.Get))
{
    @Html.DropDownListFor(a=>a.CategoryId,Model.Categories,"All")
    <button type="submit">Filter</button>    
}
@foreach (var p in Model.Courses)
{
    <p>@p.Id</p>
    <p>@p.Name</p>
}

When user selects a category and click the submit button, the form will be submitted to the Index action with the id of selected category in the url and the action method code will use that to create the filtered set of the courses to render in the view.