医疗预约书历史

Question

此代码正确地返回一个View_appointment。是否有更好的方法通过使用左联接和联合来完成这一任务？我想把Linq学得更好一点。

组件：

• PostgreSQL 9.3 (后端数据库)
• Telerik DataAccess (实体框架)

概述：

我正在为我的办公室写一本预约书。每个“约会”根据其日期(tappointment.date)和一个单元格号是唯一的。日期和单元格数与WPF DataGrid的单元格对应1到1。每个约会单元可以根据其当前占用者的活动来重用，因此可以附加一个历史文件。也就是说，如果分配给该单元格的当前人员“取消”了他们的约会，则可以将该单元格分配给其他人。

病人可以从以下两张表中的一张被分配到牢房：

• New_patients
• Established_patients

New_patients没有Established_patients所拥有的许多字段(例如，图表或保险信息)。Established_patients反过来引用了第三个文件，Patients，它包含了对病人的完整描述。

一个单独的序列为每个预约的病人提供一个独特的票号。也就是说，票号在New_patients和Established_patients中是唯一的，给定的票号将只在New_patients或established_patients中--而不是两者都存在。

因此，当View_appointment可能在new_patient或established_patients中时，如何最好地返回ticketno记录？如果在established_patients中，那么如何将它与Patient表连接起来呢？这能用左翼联盟和工会来完成吗？

  public View_appointment get_appointment(DateTime tappointment, int cellnum)
    {
        using (var ctx = new Nova.Data.Data())
        {
            // get the appointment cell and its most recent history useage.
            var x = (from a in ctx.Appointments
                     where a.Tappointment.Date == tappointment.Date && a.Cellnum == cellnum
                     join h in ctx.Appointment_histories on a.Recid equals h.Appointment_recid
                     orderby h.Tposted
                     select new
                     {
                         APPOINTMENT = a,
                         HISTORY = h
                     }).ToList().LastOrDefault();

            if (x == null) return null;

            // retrieve the patient name associated with this ticketno
            var y = ( from e in ctx.Established_patients
                      where e.Ticketno == x.HISTORY.Ticketno
                      select e).SingleOrDefault();

            var y1 = ( from f in ctx.New_patients
                       where f.Ticketno == x.HISTORY.Ticketno
                       select f).SingleOrDefault();

            // expand records into one view
            View_appointment v = null;

            if ( y != null )
            {           // Established patients are those with a chart
                var p = (from a in ctx.Patients
                         where a.Recid == y.Patient_recid
                         select new 
                         {  
                             a.Recid,
                             a.Lastname,
                             a.Firstname,
                             a.Birthdate,
                             a.Mi,
                             a.Sex,
                             a.Phone
                         }).Single();

                v = new View_appointment {
                    Appointment_history_recid = x.HISTORY.Recid,
                    Appointment_recid = x.APPOINTMENT.Recid,
                    Birthdate = p.Birthdate,
                    Cancelled = x.HISTORY.Cancelled,
                    Cellnum = x.APPOINTMENT.Cellnum,
                    Contact_number = p.Phone,
                    Deleted = x.HISTORY.Deleted,
                    Firstname = p.Firstname,
                    Lastname = p.Lastname,
                    Mi = p.Mi,
                    Name_record = x.HISTORY.Name_record,
                    Noshow = x.HISTORY.Noshow,
                    Orderno = x.HISTORY.Orderno,
                    Patient_recid = p.Recid,
                    Rescheduled = x.HISTORY.Rescheduled,
                    Sex = p.Sex,
                    Tappointment = x.APPOINTMENT.Tappointment,
                    Ticketno = x.HISTORY.Ticketno,
                    Tposted = x.HISTORY.Tposted,
                    Why = x.HISTORY.Why
                };
            }
            else if (y1 != null)
            {       // new patients have never been seen before
                v = new View_appointment
                {
                    Appointment_history_recid = x.HISTORY.Recid,
                    Appointment_recid = x.APPOINTMENT.Recid,
                    Birthdate = y1.Birthdate,
                    Cancelled = x.HISTORY.Cancelled,
                    Cellnum = x.APPOINTMENT.Cellnum,
                    Contact_number = y1.Contact_number,
                    Deleted = x.HISTORY.Deleted,
                    Firstname = y1.Firstname,
                    Lastname = y1.Lastname,
                    Mi = y1.Mi,
                    Name_record = x.HISTORY.Name_record,
                    Noshow = x.HISTORY.Noshow,
                    Orderno = x.HISTORY.Orderno,
                    Patient_recid = null,
                    Rescheduled = x.HISTORY.Rescheduled,
                    Sex = y1.Sex,
                    Tappointment = x.APPOINTMENT.Tappointment,
                    Ticketno = x.HISTORY.Ticketno,
                    Tposted = x.HISTORY.Tposted,
                    Why = x.HISTORY.Why
                };
            }

            return v;
        }

    }

Answer 1

我不太了解LINQ或C#，所以我只会查看表单，因为我在您的代码中发现了一个明显的问题。

命名事物

您编写的整个脚本主要使用一个字母的标识符，如x、a、h、y甚至y1。当我试图阅读您的代码时，我发现自己经常在页面上上下移动，查看这些标识符所引用的内容，因为您无法从它们的名称中分辨出来。这使得专注于代码的实际工作变得更加困难。

这一部分特别难以理解，并说明了这一点：

V=新View_appointment { Appointment_history_recid = x.HISTORY.Recid，Appointment_recid = x.APPOINTMENT.Recid，生日= p.Birthdate，x.APPOINTMENT.Recid= x.HISTORY.Cancelled，Cellnum = x.APPOINTMENT.Cellnum，Contact_number = p.Phone，Deleted = x.HISTORY.Deleted，Firstname = p.Firstname，Lastname = p.Lastname，Mi = p.Mi，Name_record = x.HISTORY.Name_record，Noshow = x.HISTORY.Noshow，Orderno = x.HISTORY.Orderno，Patient_recid = p.Recid，Rescheduled = x.HISTORY.Rescheduled，性别= p.Sex，T腺= x.APPOINTMENT.Tappointment，Ticketno = x.HISTORY.Ticketno，Tposted = x.HISTORY.Tposted，Why = x.HISTORY.Why }；

使用几乎任何IDE都可以轻松地重构->重命名，因此这将使您的代码更加清晰。使用短名称和/或缩写是可以的，但这并不会使代码难以维护。一个可以帮助您的优点是，医学术语有完整的已批准缩写列表；这是维基百科的一个。

考虑重构：

a -> appt
v -> viewAppt
x -> apptHistory
p -> pt
y -> estPt
y1 -> newPt
etc.

现在，这一节读起来要容易得多，例如。在整个代码中这样做，当您再次查看代码时，您将在几个月后感谢自己！

viewAppt = new View_appointment {
    Appointment_history_recid = apptHistory.HISTORY.Recid,
    Appointment_recid = apptHistory.APPOINTMENT.Recid,
    Birthdate = pt.Birthdate,
    Cancelled = apptHistory.HISTORY.Cancelled,
    Cellnum = apptHistory.APPOINTMENT.Cellnum,
    Contact_number = pt.Phone,
    Deleted = apptHistory.HISTORY.Deleted,
    Firstname = pt.Firstname,
    Lastname = pt.Lastname,
    Mi = pt.Mi,
    Name_record = apptHistory.HISTORY.Name_record,
    Noshow = apptHistory.HISTORY.Noshow,
    Orderno = apptHistory.HISTORY.Orderno,
    Patient_recid = pt.Recid,
    Rescheduled = apptHistory.HISTORY.Rescheduled,
    Sex = pt.Sex,
    Tappointment = apptHistory.APPOINTMENT.Tappointment,
    Ticketno = apptHistory.HISTORY.Ticketno,
    Tposted = apptHistory.HISTORY.Tposted,
    Why = apptHistory.HISTORY.Why
};