Answered by:
New to Entity Framework - trying to build a proof of concept database web site, need custom accessors

Question
-
User-1573194908 posted
Hope this is the right forum for this question,
I am trying to build a sample web app that inserts and updates data in a fairly simple mySQL database.
The database has patients, doctors, and visits. Patients and doctors have a many-to-many relationship. Visits have a one-to-many relationship with a patient-doctor combination.
There are four tables:
- Doctor table, with id and name.
- Patient table, with id and name.
- DoctorPatient table, with id, doctorId, and patientId, and foreign constraints.
- Visit table, with id, date, doctorPatientId, and foreign constraint.
To make the Web App I have followed this tutorial:
The generated views for the first tables work exactly as I want them to. The doctorPatients view is smart and shows me the names of the doctor and patient in the expected fields. However, the visits view shows a "doctorPatientId" field with number values. What I want to see there is a custom string with the doctor and patient names.
What's the best practice way to achieve this? Everything I've tried either doesn't work, or works in a way that I don't like.
Tuesday, March 31, 2020 3:43 PM
Answers
-
User-719153870 posted
Hi jlisonbidmc,
However, the visits view shows a "doctorPatientId" field with number values. What I want to see there is a custom string with the doctor and patient names.I would suggest to create a new class to store visist id, docter name and patient name.
Please refer to below code:
In Models folder, create this Names class:
public class Names { public int vid { get; set; } public string name1 { get; set; } public string name2 { get; set; } }
Then, modify your automatically generated VisitController Index action:
public ActionResult Index() { var visits = db.Visits.Include(v => v.DoctorPatient); var a = (from v in db.Visits join dp in db.DoctorPatients on v.doctorPatientId equals dp.id join p in db.Patients on dp.patentId equals p.id join d in db.Doctors on dp.doctorId equals d.id select new Names { name1=d.name,name2=p.name,vid=v.id }).ToList(); return View(a); }
At last, modify the Visit Index view:
@model IEnumerable<MVCDemo01.Models.Names> @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> </head> <body> <p> @Html.ActionLink("Create New", "Create") </p> <table class="table"> <tr> <th> @Html.Display("DPName") </th> <th></th> </tr> @foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.name1)_@Html.DisplayFor(modelItem => item.name2) </td> <td> @Html.ActionLink("Edit", "Edit", new { id = item.vid }) | @Html.ActionLink("Details", "Details", new { id = item.vid }) | @Html.ActionLink("Delete", "Delete", new { id = item.vid }) </td> </tr> } </table> </body> </html>
Result:
Best Regard,
Yang Shen
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, April 2, 2020 3:26 AM
All replies
-
User-330204900 posted
Web forms and Dynamic Data would be a good start 5 years ago, you want to look at the Entity Framework forum and MVC or better still Blazor
Wednesday, April 1, 2020 4:07 PM -
User-719153870 posted
Hi jlisonbidmc,
However, the visits view shows a "doctorPatientId" field with number values. What I want to see there is a custom string with the doctor and patient names.I would suggest to create a new class to store visist id, docter name and patient name.
Please refer to below code:
In Models folder, create this Names class:
public class Names { public int vid { get; set; } public string name1 { get; set; } public string name2 { get; set; } }
Then, modify your automatically generated VisitController Index action:
public ActionResult Index() { var visits = db.Visits.Include(v => v.DoctorPatient); var a = (from v in db.Visits join dp in db.DoctorPatients on v.doctorPatientId equals dp.id join p in db.Patients on dp.patentId equals p.id join d in db.Doctors on dp.doctorId equals d.id select new Names { name1=d.name,name2=p.name,vid=v.id }).ToList(); return View(a); }
At last, modify the Visit Index view:
@model IEnumerable<MVCDemo01.Models.Names> @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> </head> <body> <p> @Html.ActionLink("Create New", "Create") </p> <table class="table"> <tr> <th> @Html.Display("DPName") </th> <th></th> </tr> @foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.name1)_@Html.DisplayFor(modelItem => item.name2) </td> <td> @Html.ActionLink("Edit", "Edit", new { id = item.vid }) | @Html.ActionLink("Details", "Details", new { id = item.vid }) | @Html.ActionLink("Delete", "Delete", new { id = item.vid }) </td> </tr> } </table> </body> </html>
Result:
Best Regard,
Yang Shen
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, April 2, 2020 3:26 AM -
User-1573194908 posted
Thanks! This works great, makes sense, and looks nice. Two remarks, in case anyone with my same problem stumbles onto this thread.
I called the new class "VisitViewModel." I think this is the MVC convention. At least, that's how I've always done it.
In the Index view, I'm not sure what this is supposed to do:
@Html.Display("DPName")
But it didn't show anything. So I just used a literal HTML string there.
Another question - I also want the Edit view to show both names in the drop-down menu. Found a solution, but I'd like to verify that this the preferred way of doing it, or else get a better solution.
I made a class "DoctorPatientViewModel" containing Id, DoctorName, PatientName, and a read-only Description property that concatenates DoctorName and PatientName.
My new Edit function:
public ActionResult Edit(int? id) { if (id == null) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } visit visit = db.visits.Find(id); if (visit == null) { return HttpNotFound(); } var a = (from dp in db.doctorPatients join d in db.doctors on dp.doctorId equals d.Id join p in db.patients on dp.patientId equals p.Id select new DoctorPatientViewModel {Id = dp.Id, DoctorName = d.doctorName, PatientName = p.patientName }).ToList(); ViewBag.doctorPatientId = new SelectList(a, "Id", "Description", visit.doctorPatientId); return View(visit); }
Didn't have to change the Edit view at all.
Thursday, April 2, 2020 2:27 PM