c# - 我们的 SQL 或 Entity Framework 中存在错误？

Question

我有一些来 self 们的 DBA 的 SQL，我只是检查它是否正确，因为 EF 似乎没有将实体完全链接在一起。它知道存在关系但不执行 FK 到 PK 链接。

对它的任何想法或想法(而不是使用 NHibernate!)表示赞赏。

1   CREATE TABLE [dbo].[Employee]( 2       [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 3       [PersonID] [int] NOT NULL, 4       [GenderID] [int] NULL, 5       [EthnicOriginID] [int] NULL, 6       [MaritalStatusID] [int] NULL, 7       [DateOfBirth] [datetime] NULL, 8       [LeaveDate] [datetime] NULL, 9       [OptOut] [bit] NULL CONSTRAINT [DF_employee_OptOut]  DEFAULT (0), 10      [OptOutDate] [datetime] NULL, 11      [PassportNumber] [nvarchar](50) NULL, 12      [WorkPermitNumber] [nvarchar](50) NULL, 13      [WorkPermitExpiryDate] [datetime] NULL, 14      [PayrollNumber] [nvarchar](50) NULL, 15      [NINumber] [nvarchar](50) NULL, 16      [Visa] [bit] NULL CONSTRAINT [DF_employee_Visa]  DEFAULT (0), 17      [VisaNumber] [nvarchar](50) NULL, 18      [VisaExpiryDate] [smalldatetime] NULL, 19      [GuaranteeAmount] [money] NULL, 20      [GuaranteeDuration] [int] NULL, 21      [GuaranteeEndDate] [datetime] NULL, 22      [GuaranteePeriod] [int] NULL, 23      [IsDisabled] [bit] NULL CONSTRAINT [DF_employee_IsDisabled]  DEFAULT (0), 24      [DisabilityReferenceNumber] [nvarchar](20) NULL, 25      [IsActive] [bit] NULL CONSTRAINT [DF_employee_Active]  DEFAULT (1), 26      [IsUKResident] [bit] NULL, 27      [BankAccount] [nvarchar](100) NULL, 28   CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED  29  ( 30      [ID] ASC 31  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY], 32   CONSTRAINT [IX_employee] UNIQUE NONCLUSTERED  33  ( 34      [ID] ASC 35  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 36  ) ON [PRIMARY] 37   38  GO 39  ALTER TABLE [dbo].[Employee]  WITH NOCHECK ADD  CONSTRAINT [FK_Employee_People] FOREIGN KEY([PersonID]) 40  REFERENCES [dbo].[Person] ([Id]) 41  NOT FOR REPLICATION  42  GO 43  ALTER TABLE [dbo].[Employee] NOCHECK CONSTRAINT [FK_Employee_People] 44   45   46  ---- 47   48  CREATE TABLE [dbo].[EmployeeWorkHistory]( 49      [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 50      [EmployeeId] [int] NOT NULL, 51      [JobTitle] [nvarchar](50) NULL, 52      [OfficeId] [int] NULL, 53      [DepartmentId] [int] NULL, 54      [StartDate] [smalldatetime] NULL, 55      [EndDate] [smalldatetime] NULL, 56      [Salary] [decimal](18, 0) NULL, 57      [ContractTypeId] [int] NULL, 58      [CommentID] [int] NULL, 59      [WorkHours] [float] NULL, 60      [ManagerEmployeeID] [int] NULL, 61      [EmployeeTypeID] [int] NULL, 62      [LastWorkingDay] [smalldatetime] NULL, 63      [GardenLeave] [bit] NULL CONSTRAINT [DF_employeeWorkHistory_GardenLeave]  DEFAULT (0), 64      [WorkingHours] [nvarchar](20) NULL CONSTRAINT [DF_EmployeeWorkHistory_WorkingHours]  DEFAULT ('8.30am - 5.30pm'), 65      [WorkingDays] [nvarchar](100) NULL CONSTRAINT [DF_EmployeeWorkHistory_WorkingDays]  DEFAULT ('Monday to Friday'), 66      [TerminationId] [int] NULL, 67      [TerminiationDate] [smalldatetime] NULL, 68   CONSTRAINT [PK_employeeWorkHistory] PRIMARY KEY CLUSTERED  69  ( 70      [id] ASC 71  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 72  ) ON [PRIMARY] 73   74  GO 75  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EmployeeWorkHistory', @level2type=N'COLUMN',@level2name=N'WorkingHours' 76  GO 77  ALTER TABLE [dbo].[EmployeeWorkHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_EmployeeWorkHistory_ContractType] FOREIGN KEY([ContractTypeId]) 78  REFERENCES [dbo].[ContractType] ([Id]) 79  NOT FOR REPLICATION  80  GO 81  ALTER TABLE [dbo].[EmployeeWorkHistory] CHECK CONSTRAINT [FK_EmployeeWorkHistory_ContractType] 82  GO 83  ALTER TABLE [dbo].[EmployeeWorkHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_EmployeeWorkHistory_Employee] FOREIGN KEY([EmployeeId]) 84  REFERENCES [dbo].[Employee] ([ID]) 85  NOT FOR REPLICATION  86  GO 87  ALTER TABLE [dbo].[EmployeeWorkHistory] CHECK CONSTRAINT [FK_EmployeeWorkHistory_Employee] 88  GO 89   90   91  ------ 92   93   94  CREATE TABLE [dbo].[ContractType]( 95      [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 96      [Text] [nvarchar](50) NOT NULL, 97      [IsActive] [bit] NOT NULL CONSTRAINT [DF_ContractType_IsActive]  DEFAULT (1), 98   CONSTRAINT [PK_ContractType] PRIMARY KEY CLUSTERED  99  ( 100     [Id] ASC 101 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY] 102 ) ON [PRIMARY] 103  104 -- 105  106 CREATE TABLE [dbo].[EmployeeReference]( 107     [ID] [int] IDENTITY(1,1) NOT NULL, 108     [EmployeeID] [int] NOT NULL, 109     [RefereePersonID] [int] NOT NULL, 110     [Company] [nvarchar](200) NULL, 111     [CommentID] [int] NULL, 112     [DateRequested] [smalldatetime] NULL, 113     [DateReceived] [smalldatetime] NULL, 114     [TimeKnownFor] [nvarchar](100) NULL, 115     [ReferenceDocument] [nvarchar](500) NULL, 116     [ReferenceTypeID] [int] NOT NULL, 117     [FileAttachmentID] [int] NULL, 118     [ContactDetailId] [int] NULL, 119  CONSTRAINT [PK_EmployeeReference] PRIMARY KEY CLUSTERED  120 ( 121     [ID] ASC 122 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 123 ) ON [PRIMARY] 124  125 GO 126 ALTER TABLE [dbo].[EmployeeReference]  WITH NOCHECK ADD  CONSTRAINT [FK_EmployeeReference_Person] FOREIGN KEY([RefereePersonID]) 127 REFERENCES [dbo].[Person] ([Id]) 128 NOT FOR REPLICATION  129 GO 130 ALTER TABLE [dbo].[EmployeeReference] NOCHECK CONSTRAINT [FK_EmployeeReference_Person] 131 GO 132 ALTER TABLE [dbo].[EmployeeReference]  WITH NOCHECK ADD  CONSTRAINT [FK_EmployeeReferenceMapping_Employee] FOREIGN KEY([EmployeeID]) 133 REFERENCES [dbo].[Employee] ([ID]) 134 NOT FOR REPLICATION  135 GO 136 ALTER TABLE [dbo].[EmployeeReference] NOCHECK CONSTRAINT [FK_EmployeeReferenceMapping_Employee] 137  138 --- 139  140  141 CREATE TABLE [dbo].[Person]( 142     [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 143     [SalutationId] [int] NULL, 144     [Firstname] [nvarchar](50) NULL, 145     [Middlename] [nvarchar](50) NULL, 146     [Surname] [nvarchar](50) NULL, 147     [PreferredName] [nvarchar](50) NULL, 148     [ContactDetailId] [int] NULL, 149     [PersonTypeId] [int] NULL, 150     [IsActive] [bit] NOT NULL CONSTRAINT [DF_people_Active]  DEFAULT (1), 151     [EnteredBy] [int] NULL, 152     [EnteredDate] [datetime] NULL, 153     [UpdatedBy] [int] NULL, 154     [UpdatedDate] [datetime] NULL, 155  CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED  156 ( 157     [Id] ASC 158 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 159 ) ON [PRIMARY] 160  161 GO 162 GO 163  

Answer 1

你已经在外键约束上定义了 NOCHECK

  ALTER TABLE [dbo].[Employee] NOCHECK CONSTRAINT [FK_Employee_People] 

另外，您在 Employee 上有一个重复的索引。