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
