CODEWITHSUNDEEP
asp.net-mvcentity-frameworklinqjoin
Sadia
Sadia

Reputation: 181

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

I have to get data into this table below: enter image description here

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

Upvotes: 0

Views: 2133

Answers (2)

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

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();

Upvotes: 1

GidiBloke
GidiBloke

Reputation: 488

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.

Upvotes: 0

Related Questions