How to Pull Name Based on Saved ID in ASP.NET Core MVC & SQL Server?

Question

I'm building a website. The subject of the website is as follows: Students and their schools. The school will be selected when adding students. In addition, each student and school will have an ID. I want to display the school name next to the student by the school ID. But I could not access the school name according to the ID.

My code:

Index.cshtml (List of students):

@using StudentApp.Models.Entity
@model List<StudentTable>
@{
    ViewData["Title"] = "Manage Student";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<br />
<h1>Manage Student</h1>
<br />
<table class="table table-bordered">
    <tr>
        <th>
            Student ID
        </th>
        <th>
            Student Name
        </th>
        <th>
            Student Class
        </th>
        <th>
            Delete
        </th>
        <th>
            Edit
        </th>
    </tr>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @item.Id
                </td>
                <td>
                    @item.Name
                </td>
                <td>
                    @item.ClassId @*The school ID is displayed here. *@
                </td>
                <td>
                    <a href="/StudentTable/Delete/@item.Id" class="btn btn-danger">Delete</a>
                </td>
                <td>
                    <a href="/StudentTable/GetClass/@item.Id" class="btn btn-success">Edit</a>
                </td>
            </tr>
        }
    </tbody>
</table>

<a href="/StudentTable/AddStudent"  
class="btn btn-primary">Add Student</a>

StudentTableController.cs:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using StudentApp.Models.Entity;

namespace StudentApp.Controllers
{
    public class StudentTableController : Controller
    {
        StudentDatabaseContext db = new StudentDatabaseContext();

        public IActionResult Index()
        {
            var studentList = db.StudentTables.ToList();
            return View(studentList);
        }
    }
}

Tables:

I appreciate your help in advance.

Answer 1

Do you want to show ClassName instead of SchoolName? I don't find any School in the tables you provided. IF you want to show both class and student information, You can use ViewModel.

ViewModel

 public class ViewModelTest
    {
        public Student students { get; set; }
        public Class classes { get; set; }
    }

Controller

 public IActionResult Index()
        {
            var student = _db.students.ToList();
            List<ViewModelTest> models = new List<ViewModelTest>();
            
            foreach (var item in student)
            {
                ViewModelTest model = new ViewModelTest();
                model.students = item;
                var cla = _db.classes.FirstOrDefault(x => x.Id == item.ClassId);
                model.classes = cla;
                models.Add(model);
            }
            return View(models);
        }

View

@model  List<StudentApp.Models.ViewModelTest>

    <br />
<h1>Manage Student</h1>
<br />
<table class="table table-bordered">
    <tr>
        <th>
            Student ID
        </th>
        <th>
            Student Name
        </th>
        <th>
            Student Class
        </th>
        <th>
            Delete
        </th>
        <th>
            Edit
        </th>
    </tr>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @item.students.Id
                </td>
                <td>
                    @item.students.Name
                </td>
                <td>
                    @item.students.ClassId (@item.classes.ClassName)@*The school ID is displayed here. *@
                </td>
                <td>
                    <a href="/StudentTable/Delete/@item.students.Id" class="btn btn-danger">Delete</a>
                </td>
                <td>
                    <a href="/StudentTable/GetClass/@item.students.Id" class="btn btn-success">Edit</a>
                </td>
            </tr>
        }
    </tbody>
</table>

<a href="/StudentTable/AddStudent"  
class="btn btn-primary">Add Student</a>

Demo

If you want to add a School table, You can just create a viewmodel to contains the models you need.