Fluent Nhibernate

Question

我对NHibernate和DB非常陌生。我有三张桌子，它们与许多许多关系有关。

下面是创建表的脚本：

create table PATIENT_INFO(patient_Code  int not null identity(1,1) PRIMARY KEY, 
PATIENT_NAME VARCHAR(30), PATIENT_ADRESS VARCHAR(30), PATIENT_BIRTHDAY DATE, 
RECIVED_DATE DATE, ASSIGNED_TO int, FOLLOWUP_DATE DATE, FOLLOWUP_ID INT,
NOTES VARCHAR(750), DATE_INIATED DATE, FOUNDATIONS int, DRUGS VARCHAR(30), NOTIFICATIONS int, GRUNTS int);

CREATE TABLE DOCTORS(DOCTORS_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,DOCTORS_NAME VARCHAR(30),SPETIALTY VARCHAR(30));

CREATE TABLE PATIENT_DOCTOR (PATIENT_ID INT, DOCTOR_ID INT,
CONSTRAINT PATIENT_DOCTOR_PK PRIMARY KEY(PATIENT_ID, DOCTOR_ID),
CONSTRAINT FK_PATIENT FOREIGN KEY(PATIENT_ID) REFERENCES PATIENT_INFO (patient_Code),
CONSTRAINT FK_DOCTOR FOREIGN KEY(DOCTOR_ID) REFERENCES DOCTORS (DOCTORS_ID));

下面是它们的图表：

以下是为这三张表格制作的模型：

public partial class PatientDoctor
    {
        public virtual int Id { get; set; }
        public virtual int? PatientID { get; set; }
        public virtual int? DoctorID { get; set; }
        public virtual DoctorEF Doctor { get; set; }
        [IgnoreDataMember]
        public virtual PatientInfoEF PatientInfo { get; set; }
    }

public partial class PatientInfo
    {
        public PatientInfo()
        {
            this.PatientDoctor = new List<Doctor>();
            this.PatientDrugs = new List<Drug>();
        }

        public virtual int PatientCode { get; set; }
        public virtual string PatientName { get; set; }
        public virtual string PatientAdress { get; set; }

        [DataType(DataType.Date, ErrorMessage = "Please enter date")]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public virtual DateTime? PatientBirthday { get; set; }

        [DataType(DataType.Date, ErrorMessage = "Please enter date")]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public virtual DateTime? RecivedDate { get; set; }
        public virtual int? AssignedTo { get; set; }
        public virtual int? FollowupId { get; set; }
        public virtual string Notes { get; set; }

        [DataType(DataType.Date, ErrorMessage = "Please enter date")]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public virtual DateTime? DateIniated { get; set; }
        public virtual int? FoundationId { get; set; }
        public virtual  int? DrugId { get; set; }
        public virtual int? NotificationId { get; set; }
        public virtual int? GrantId { get; set; }
        public virtual string IfOther { get; set; }
        public virtual bool IsInEnrollmentQueue { get; set; }
        public virtual bool IsInGrandTrackerQueue { get; set; }
        public virtual FollowUpItems FollowUpItems { get; set; }
        public virtual Foundation Foundation { get; set; }
        public virtual Grant Grant { get; set; }
        public virtual Notification Notification { get; set; }
        [IgnoreDataMember]
        public virtual IList<Doctor> PatientDoctor { get; set; }
        [IgnoreDataMember]
        public virtual IList<Drug> PatientDrugs { get; set; }
    }

public partial class Doctor
    {
        public Doctor()
        {
            this.PatientDoctor = new List<PatientInfo>();
        }

        public virtual int DoctorsId { get; set; }
        public virtual string DoctorsName { get; set; }
        public virtual string Spetialty { get; set; }
        public virtual IList<PatientInfo> PatientDoctor { get; set; }
    }

以下是映射：

class DoctorMap:ClassMap<Doctor>
    {
       public DoctorMap()
        {
            Table("DOCTORS");
            Id(x => x.DoctorsId).Column("DOCTORS_ID");
            Map(x => x.DoctorsName).Column("DOCTORS_NAME");
            Map(x => x.Spetialty).Column("SPETIALTY");
            HasManyToMany(x => x.PatientDoctor)
                .Cascade.All().Inverse()
                .Table("PATIENT_DOCTOR").ParentKeyColumn("DOCTOR_ID").ChildKeyColumn("PATIENT_ID");
        }
    }

    public PatientInfoMap()
            {
                Table("PATIENT_INFO");
                LazyLoad();
                Id(x => x.PatientCode).Column("PATIENT_ID");
                Map(x => x.PatientName).Column("PATIENT_NAME");
                Map(x => x.PatientAdress).Column("PATIENT_ADRESS");
                Map(x => x.PatientBirthday).Column("PATIENT_BIRTHDAY");
                Map(x => x.RecivedDate).Column("RECIVED_DATE");
                Map(x => x.AssignedTo).Column("ASSIGNED_TO");
                //Map(x => x.FollowupId).Column("FOLLOWUP_ID");
                Map(x => x.Notes).Column("NOTES");
                Map(x => x.DateIniated).Column("DATE_INIATED");
                //Map(x => x.FoundationId).Column("FOUNDATION_ID");
                Map(x => x.DrugId).Column("DRUG_ID");
                //Map(x => x.NotificationId).Column("NOTIFICATION_ID");
               // Map(x => x.GrantId).Column("GRANT_ID");
                Map(x => x.IfOther).Column("IF_OTHER");
                Map(x => x.IsInEnrollmentQueue).Column("IS_IN_ENROLLMENT_QUEUE");
                Map(x => x.IsInGrandTrackerQueue).Column("IS_IN_GRAND_TRACKER_QUEUE");

                HasManyToMany(x => x.PatientDoctor)
                    .Cascade.All()
                    .Table("PATIENT_DOCTOR").ParentKeyColumn("PATIENT_ID").LazyLoad();

                HasManyToMany(x => x.PatientDrugs)
                    .Cascade.All()
                    .Table("PATIENT_DRUGS").ParentKeyColumn("PATIENT_ID").LazyLoad();

                References(x => x.FollowUpItems).Column("FOLLOWUP_ID").Index("ID");

                References(x => x.Foundation);

                References(x => x.Grant);

                References(x => x.Notification);
            }
        }

请告诉我，我的数据库连接是否良好，以及映射和模型是否适合这个数据库。

Answer 1

关于映射，没有什么可说的。这些东西要么起作用，要么不起作用，既然它们被张贴在这里，我就认为它们起作用了。

我会评论一下fluent API代码的格式，例如在这个片段中：

        HasManyToMany(x => x.PatientDoctor)
            .Cascade.All().Inverse()
            .Table("PATIENT_DOCTOR").ParentKeyColumn("DOCTOR_ID").ChildKeyColumn("PATIENT_ID");

将链式调用放在新行上会更容易读，如下所示：

        HasManyToMany(x => x.PatientDoctor)
            .Cascade.All()
            .Inverse()
            .Table("PATIENT_DOCTOR")
            .ParentKeyColumn("DOCTOR_ID")
            .ChildKeyColumn("PATIENT_ID");

除此之外，这是个人偏好，我发现NHibernate配置文件中的XML映射更清晰。

创建表的T脚本并不完全可维护；比较如下：

创建表PATIENT_INFO(patient_Code int null标识(1，1)主键，PATIENT_NAME VARCHAR(30)，PATIENT_ADRESS VARCHAR(30)，PATIENT_BIRTHDAY DATE，RECIVED_DATE DATE，ASSIGNED_TO INT，FOLLOWUP_DATE DATE，FOLLOWUP_ID int，NOTES (750)，DATE_INIATED DATE，FOUNDATIONS int，PATIENT_NAME VARCHAR(30)，NOTIFICATIONS，GRUNTS int)；

对此：

create table PATIENT_INFO(
    patient_Code  int not null identity(1,1) PRIMARY KEY, 
    PATIENT_NAME VARCHAR(30), 
    PATIENT_ADRESS VARCHAR(30), 
    PATIENT_BIRTHDAY DATE, 
    RECIVED_DATE DATE, 
    ASSIGNED_TO int, 
    FOLLOWUP_DATE DATE, 
    FOLLOWUP_ID INT,
    NOTES VARCHAR(750), 
    DATE_INIATED DATE, 
    FOUNDATIONS int, 
    DRUGS VARCHAR(30), 
    NOTIFICATIONS int, 
    GRUNTS int);

我要补充的是，我发现ALL_CAPS约定在眼睛上很难看到；我更喜欢看到全大写的T关键字，以及PascalCase中的标识符，就像这样，我还喜欢显式地指定要在其中创建一个表的模式，而且我发现如果它只是名为Id，则PK更容易识别，所以我会这样做：

CREATE TABLE dbo.PatientInfo(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    PatientName VARCHAR(30), 
    PatientAddress VARCHAR(30), 
    PatientBirthday DATE, 
    ReceivedDate DATE, 
    AssignedTo INT, 
    FollowUpDate DATE,
    FollowUpId INT,
    Notes VARCHAR(750), 
    InitiatedDate DATE, 
    Foundations INT, 
    Drugs VARCHAR(30), 
    Notifications INT, 
    Grunts INT);

因为这是一个正在开发的模型的脚本，所以我会把它写成一个滴加创建的脚本。

至于最后一个this片段中的约束，我更喜欢将它们与CREATE TABLE指令分开编写，因此典型脚本的结构如下：

/* drop constraints */
IF EXISTS... DROP CONSTRAINT...

/* drop tables */
IF EXISTS... DROP TABLE...

/* create tables */
CREATE TABLE...

/* create constraints */
ALTER TABLE.. ADD CONSTRAINT...