locked
New to Entity Framework - trying to build a proof of concept database web site, need custom accessors RRS feed

  • 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:

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/database-first-development/creating-the-web-application

    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