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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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