Bug in our SQL or Entity Framework?

Question

Hi Guys,

I have some SQL from our DBA which I'm just checking it is right, since the EF doesn't seem to fully link entities together. It knows there is a relation but doesn't perform the FK to PK link.

Any ideas or thoughts (rather than use NHibernate! ) on it are appreciated.

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

You have defined NOCHECK on foreign key constraints

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

Plus, you have a duplicate index on Employee.