Joins with Multiple Table and showing data from multiple tables in the View

Question

I have to get data into this table below:

The data in this table is coming mainly from a table “tblPatient” but the last entry in this table i.e. “AppointmentDate” is coming from another table but to get this single entry I have to linq my query with three tables i.e. tblPatient tblPatientBill & tblPatientAppointment. For this I have written this query in my controller

public ActionResult PrintPartialViewToPdf(int id)
        {
            List<tblPatientAppointment> tblappointments = db.tblPatientAppointments.ToList();
            List<tblPatientBill> tblbill = db.tblPatientBills.Where(x=>x.ID == id && x.is_active == true).ToList();
            List<tblPatient> tblpatient = db.tblPatients.ToList();


            var result = (from p in tblbill
                          join o in tblappointments on p.PatientAppointmentID equals o.ID
                          join c in tblpatient on o.patient_id equals c.Patient_id
                          select new
                          {
                              c.Patient_Name,
                              c.Patient_address,
                              c.Contact_no,
                              c.Age,
                              c.Gender,
                              c.Date_of_Birth,
                              o.AppointmentDate,
                          }).ToList();


                var report = new PartialViewAsPdf("~/Views/Shared/PatientBillToPDF.cshtml", result);
                return report;


        }

In my view when I want to display the result in the view here is my code for it. PatientBillToPDF.cshtml

@model IEnumerable<HMS.Models.tblPatient>

@{
    Layout = null;
}
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Bootstrap Example</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>
    <style>
        .bottom {
            vertical-align: middle;
        }
    </style>
</head>
<body>
<div class="container">
        <div class="row clearfix">
            <div class="container-fluid">
                <div class="col-lg-12 col-md-12 col-sm-12">
                    <div class="block-header">
                        <h3>Patient's Personal Detail</h3>
                    </div>
                    <div class="card">
                        <div class="body table-responsive">

                            <table class="table table-bordered table-striped table-hover js-basic-example dataTable" style="width:100%">
                                @foreach (var item in Model)
                                {
                                    <tr>
                                        <th>@Html.DisplayNameFor(model => model.Patient_Name)</th>
                                        <td>@Html.DisplayFor(model => item.Patient_Name)</td>
                                    </tr>
                                    <tr>
                                        <th>@Html.DisplayNameFor(model => model.Patient_address)</th>
                                        <td>@Html.DisplayFor(model => item.Patient_address)</td>
                                    </tr>
                                    <tr>
                                        <th>@Html.DisplayNameFor(model => model.Contact_no)</th>
                                        <td>@Html.DisplayFor(model => item.Contact_no)</td>
                                    </tr>
                                    <tr>
                                        <th>@Html.DisplayNameFor(model => model.Age)</th>
                                        <td>@Html.DisplayFor(model => item.Age)</td>
                                    </tr>
                                    <tr>
                                        <th>@Html.DisplayNameFor(model => model.Gender)</th>
                                        <td>@Html.DisplayFor(model => item.Gender)</td>
                                    </tr>
                                    <tr>
                                        <th>@Html.DisplayNameFor(model => model.Date_of_Birth)</th>
                                        <td>@Html.DisplayFor(model => item.Date_of_Birth)</td>
                                    </tr>
                                    <tr>
                                        <th>@Html.DisplayNameFor(model => model.AppointmentDate)</th>
                                        <td>@Html.DisplayFor(model => item.AppointmentDate)</td>
                                    </tr>
                                }
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

What I want to know: Please check whether my linq query is according to the requirement? And the 2nd thing is in my View I am not able to get Appointment Date as it is including the tblPatient model. How can I display data from two different tables for which the query is returning the result but I am not able to display it in view because they belong to two different models.Thanks

Use ViewModel instead of sending the JOIN result directly to the View. See this post - https://stackoverflow.com/questions/11064316/what-is-viewmodel-in-mvc — Sahil Sharma, Sep 06 '19 at 12:23

Ehsan Sajjad · Accepted Answer · 2019-09-06T10:40:00.660

How can I display data from two different tables for which the query is returning the result

You actually need to create a ViewModel here and strongly type your view with it. Your ViewModel would look like:

public class PatientAppointmentViewModel
{
     public string PatientName { get;set; }
     public string PatientAddress { get;set; }
     public string ContactNo { get;set;}
     ......
     ......
     public DateTime AppointmentDate { get;set;}
}

In the controller we need to populate the ViewModel :

 var result = (from p in tblbill
                      join o in tblappointments on p.PatientAppointmentID equals o.ID
                      join c in tblpatient on o.patient_id equals c.Patient_id
                      select new PatientAppointmentViewModel
                      {
                          PatientName = c.Patient_Name,
                          PatientAddress = c.Patient_address,
                          ......
                          ......
                          AppointmentDate = o.AppointmentDate
                      }).ToList();

And the View would need to bind with the IEnumerable<PatientAppointmentViewModel>:

@model IEnumerable<HMS.Models.PatientAppointmentViewModel>

@{
    Layout = null;
}

Please check whether my linq query is according to the requirement

Your queries might be correct but One important thing that you are loading all data in memory and then doing linq queries on it which will not work in production if the data is too large. For example the following will load all patient appointments from db which is not correct:

List<tblPatientAppointment> tblappointments = db.tblPatientAppointments.ToList();

and same is the case with other two lines after this one.

You should directly use context object in the linq query so that it only loads those rows in memory that match the query criteria like:

var result = (from p in db.tblPatientBills
              join o in db.tblPatientAppointmentson p.PatientAppointmentID equals o.ID
              join c in db.tblPatients on o.patient_id equals c.Patient_id
              where p.ID == id && p.is_active == true
              select new PatientAppointmentViewModel
              {
                  PatientName = c.Patient_Name,
                  PatientAddress = c.Patient_address,
                  ......
                  ......
                  AppointmentDate = o.AppointmentDate
               }).ToList();

makes sense let me try with it. – Sadia Sep 06 '19 at 10:08 — Sadia, Sep 06 '19 at 10:08
Its working perfectly thanks a lot for the guidance (y) – Sadia Sep 06 '19 at 17:31 — Sadia, Sep 06 '19 at 17:31

score 0 · Answer 2 · answered Sep 06 '19 at 08:19

0

Your code seems incorrect when you displaying the Appointment date.

<tr>
<th>@Html.DisplayNameFor(model => model.AppointmentDate)</th>
<td>@Html.DisplayFor(model => item.Date_of_Birth)</td>
</tr>

The third line will display date of birth. Change that to

<td>@Html.DisplayFor(model => model.AppointmentDate)</td>

To know if the query is efficient, you will have to explain what you are trying to do. Are you getting result for a single patient. Also i would not load the entire table in memory before running the query if i was you.

answered Sep 06 '19 at 08:19

GidiBloke

478
4
16

yes i am getting result for a single patient and i have written it as AppointmentDate in the third line it is mistaken here only.let me edit it. – Sadia Sep 06 '19 at 08:21
the actual problem is that how i retrieve appointment date in table as i have added only one model in the view. – Sadia Sep 06 '19 at 08:23

Joins with Multiple Table and showing data from multiple tables in the View

2 Answers2