How to Join tables and return result into view using Asp .net mvc (tutorial)

In this asp .net tutorial we will learn about how to Join multiple tables and return result into view using Asp .net mvc. We will also explore concept of ViewModel to pass multiple models to view in asp .net mvc.We will also see how to get data from multiple models in a view using asp .net.You can also download sample code attached with this example.

Step1 Create Classes:

In this example I have not used any database so I will create Classes manually to store and reterieve data. If you are using entity framework you can use classes generated by entity framework.

    namespace JoinAndViewModel.Models
    {
        public class Student
        {
            public int Id { get; set; }
            public string StudentName { get; set; }
            public string StudentStream { get; set; }
        }

        public class StudentAdditionalInfo
        {
            public int Id { get; set; }
            public int StudentId { get; set; }
            public string FavourateFruit { get; set; }
            public string Hobby { get; set; }
        }
    }
    

Step2 Create a ViewModel Class :

In this step we will create ViewModel class. This will be used to store and reterive data built by joining two tables (Student and StudentAdditionalInfo).

    namespace JoinAndViewModel.Models
    {
        public class StudentViewModel
        {
            public Student studentVm { get; set; }
            public StudentAdditionalInfo studentAdditionalInfoVm { get; set; }
        }
    }
    

Step3 Create a Controller:

In this step we will create controller, Insert dummy data, Join data and pass data to view.

     public class HomeController : Controller
    {
        List<Student> student = new List<Student>();
        List<StudentAdditionalInfo> studentAdditionalInfo = new List<StudentAdditionalInfo>();

        public ActionResult Index()
        {
            insertDummyData();
            var studentViewModel = from s in student
                                   join st in studentAdditionalInfo on s.Id equals st.StudentId into st2
                                   from st in st2.DefaultIfEmpty()
                                   select new StudentViewModel { studentVm = s, studentAdditionalInfoVm = st };
            return View(studentViewModel);
        }

        public void insertDummyData()
        {
            //--- Insert Dummy data into Student
            student.Add(new Student { Id = 1, StudentName = "Max", StudentStream = "Computer Science" });
            student.Add(new Student { Id = 2, StudentName = "Tony", StudentStream = "Life Sciences" });
            student.Add(new Student { Id = 3, StudentName = "Jhon", StudentStream = "Robotics" });
            student.Add(new Student { Id = 4, StudentName = "Jack", StudentStream = "Computer Science" });
            student.Add(new Student { Id = 5, StudentName = "Dominic", StudentStream = "Avaiation" });

            //---- Insert Dummy data into StudentAdditionalInfo
            studentAdditionalInfo.Add(new StudentAdditionalInfo { Id = 100, StudentId = 1, FavourateFruit = "Apple", Hobby = "Driving" });
            studentAdditionalInfo.Add(new StudentAdditionalInfo { Id = 101, StudentId = 2, FavourateFruit = "Mango", Hobby = "Hunting" });
            studentAdditionalInfo.Add(new StudentAdditionalInfo { Id = 102, StudentId = 3, FavourateFruit = "Bannana", Hobby = "Fishing" });
            studentAdditionalInfo.Add(new StudentAdditionalInfo { Id = 103, StudentId = 4, FavourateFruit = "Pine Apple", Hobby = "Sailing" });
            studentAdditionalInfo.Add(new StudentAdditionalInfo { Id = 104, StudentId = 5, FavourateFruit = "Grapes", Hobby = "Street Racing" });

        }

    }
    

Code Explanation:

1. Here we have created insertDummyData() method to insert dummy data
2. In Index() mehtod we are joining data of multiple tables (Student and StudentAdditionalInfo) and returning to view.

Step4 Create view to display data:

In this step we will create view to display data.

        @model IEnumerable<JoinAndViewModel.Models.StudentViewModel>
        <table class="table table-striped">
            <tr>
                <td>Student Name</td>
                <td>Student Stream</td>
                <td>Favourate Fruite</td>
                <td>Hobby</td>
            </tr>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@item.studentVm.StudentName</td>
                    <td>@item.studentVm.StudentStream</td>
                    <td>@item.studentAdditionalInfoVm.FavourateFruit</td>
                    <td>@item.studentAdditionalInfoVm.Hobby</td>
                </tr>
            }
        </table>
    

Join tables and return result into view using Asp .net mvc output: