How to view multiple tables in the View MVC3 ASP.NET

Question

I am trying to select a multiple table from the database using LINQ in MVC3 Razor. What do I need to change to my codes in order for me to view multiple data from multiple table in the view?

This is my Model

public class DailyAttendance
{
    [Key]
    public string pnp { get; set; }
    public string student_id { get; set; }
    public string mth_yr_id { get; set; }
    public string D01 { get; set; }
    public string D02 { get; set; }
} // and so on

public class TitasDb : DbContext
{
    public DbSet<DailyAttendance> DailyAttendance { get; set; }
    public DbSet<MonthYear> MonthYear { get; set; }
    // and so on

}

This is how I use LINQ in the controller

public class AttendanceController : Controller
{
    TitasDb _attendanceModel = new TitasDb();

    public ActionResult Index()
    {
        var model = from s in _attendanceModel.Student
                    join ssc in _attendanceModel.StudentSchoolClass on s.student_id equals ssc.student_id
                    join sc in _attendanceModel.SchoolClass on ssc.school_class_id equals sc.school_class_id
                    join da in _attendanceModel.DailyAttendance on s.student_id equals da.student_id
                    join my in _attendanceModel.MonthYear on da.mth_yr_id equals my.mth_yr_id
                    where my.month_id == "M05"
                    where sc.teacher_id == "T1000001"
                    select new { DailyAttendance = da, Student = s };


        return View(model);
    }
}

And this is how I tried to view the data

@model IEnumerable<titas.Models.DailyAttendance>

@foreach (var item in Model) {
<tr>
    <td>
        @Html.DisplayFor(modelItem => item.student_id)
    <td>
</tr>
}

And the error I am getting is

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousType5`2[titas.Models.DailyAttendance,titas.Models.Student]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[titas.Models.DailyAttendance]'. 

Answer 1

You're sending anonymous object to your view and declaring that your view gets IEnumerable<titas.Models.DailyAttendance> which isn't true in this situation. Try to make model for it.

public class MyModel
{
   public DailyAttendance dailyAttendance{ get; set; }
   public Student student { get; set; }
}

And then in your controller fill data into this model

    var model = (from s in _attendanceModel.Student
                join ssc in _attendanceModel.StudentSchoolClass on s.student_id equals ssc.student_id
                join sc in _attendanceModel.SchoolClass on ssc.school_class_id equals sc.school_class_id
                join da in _attendanceModel.DailyAttendance on s.student_id equals da.student_id
                join my in _attendanceModel.MonthYear on da.mth_yr_id equals my.mth_yr_id
                where my.month_id == "M05"
                where sc.teacher_id == "T1000001"
                select new MyModel { dailyAttendance = da, student = s }).ToList();

And then pass it to your view and declare your view's model like this

@model IEnumarable<MyModel>

Answer 2

As in every ASP.NET MVC application, define a view model that will contain all the information that you need to work with in your view:

public class MyViewModel
{
    public DailyAttendance DailyAttendance { get; set; }
    public Student Student { get; set; }
}

and then have your controller action return this view model to the view:

public class AttendanceController : Controller
{
    TitasDb _attendanceModel = new TitasDb();

    public ActionResult Index()
    {
        var model = from s in _attendanceModel.Student
                    join ssc in _attendanceModel.StudentSchoolClass on s.student_id equals ssc.student_id
                    join sc in _attendanceModel.SchoolClass on ssc.school_class_id equals sc.school_class_id
                    join da in _attendanceModel.DailyAttendance on s.student_id equals da.student_id
                    join my in _attendanceModel.MonthYear on da.mth_yr_id equals my.mth_yr_id
                    where my.month_id == "M05"
                    where sc.teacher_id == "T1000001"
                    select new MyViewModel
                    { 
                        DailyAttendance = da, 
                        Student = s 
                    };
        return View(model.ToList());
    }
}

and finally have your view strongly typed to the view model:

@model IList<MyViewModel>
<table>
    <thead>
        <tr>
            <th>student id</th>
            <th>attendance id</th>
        </tr>
    </thead>
    <tbody>
        @for (var i = 0; i < Model.Count; i++)
        {
            <tr>
                <td>@Html.DisplayFor(x => x[i].Student.student_id)</td>
                <td>@Html.DisplayFor(x => x[i].DailyAttendance.attendance_id)</td>
            </tr>
        }
    </tbody>
</table>

and to cleanup the view you could use a display template that will automatically be rendered for each element of the model (~/Views/Shared/DisplayTemplates/MyViewModel.cshtml):

@model MyViewModel
<tr>
    <td>@Html.DisplayFor(x => x.Student.student_id)</td>
    <td>@Html.DisplayFor(x => x.DailyAttendance.attendance_id)</td>
</tr>

and now your main view simply becomes:

@model IList<MyViewModel>
<table>
    <thead>
        <tr>
            <th>student id</th>
            <th>attendance id</th>
        </tr>
    </thead>
    <tbody>
        @Html.DisplayForModel()
    </tbody>
</table>

Answer 3

The problem is that you are passing an anonymous type as the model: select new { DailyAttendance = da, Student = s } where you have defined the model in your view as @model IEnumerable<titas.Models.DailyAttendance>.

Try creating a model specific to your view:

public class AttendanceModel
{
    public Student Student { get; set; }
    public DailyAttendance DailyAttendace { get; set; }
}

Or whatever works for you. You may want to create specific view models for the student and daily attendace also.

Then you can change your model:

@model IEnumerable<Attendanceodel>

And the select to:

select new AttendanceModel { DailyAttendance = da, Student = s }

Hope that gets you on the right track.