I've tried both "Model First" and "Generate Model from Database".
I have a basic example outlined below (DDL shown).
There is a M:N relationship between Employees and JobTitles. An Employee can have several JobTitles. This alone is trivial using the edmx visual designer.
However, I want to put an attribute (scalar property) ~~on the relationship~~, not on either of the Entities.
Let's say I have multiple job titles. I am a developer, a project manager (sometimes..on very small projects) and a dba.
My Number1 job is developer. My Number2 job is project manager. My Number3 job is dba.
So the "PriorityRank" (as I call it below) is associated with relationship between Employee and JobTitle, not on Employee and not on JobTitle.
This is "attribute on a relationship) is acceptable on a E.R. diagram. ...
I can't figure out if Entity Framework 4.0 is capable of this.
Below is my DDL and some example data to show what I'm talking about.
I'm rather new to EF4.0, so take it easy on me. I'm still learning.
Thanks.
-- START TSQL
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[EmployeeJobTitleLink]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN DROP TABLE [dbo].[EmployeeJobTitleLink] END GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[JobTitle]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN DROP TABLE [dbo].[JobTitle] END GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN DROP TABLE [dbo].[Employee] END GO
CREATE TABLE [dbo].[Employee] ( EmployeeUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID() , SSN char(9) not null )
GO
ALTER TABLE dbo.Employee ADD CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (EmployeeUUID) GO
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT CK_SSN_Unique UNIQUE (SSN) GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[JobTitle]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN DROP TABLE [dbo].[JobTitle] END GO
CREATE TABLE [dbo].[JobTitle] ( JobTitleUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID() , JobTitleName varchar(24) not null )
GO
ALTER TABLE dbo.JobTitle ADD CONSTRAINT PK_JobTitle PRIMARY KEY NONCLUSTERED (JobTitleUUID) GO
ALTER TABLE [dbo].[JobTitle] ADD CONSTRAINT CK_JobTitleName_Unique UNIQUE (JobTitleName) GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[EmployeeJobTitleLink]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN DROP TABLE [dbo].[EmployeeJobTitleLink] END GO
CREATE TABLE [dbo].[EmployeeJobTitleLink] ( EmployeeJobTitleLinkUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID() , EmployeeUUID [UNIQUEIDENTIFIER] NOT NULL , JobTitleUUID [UNIQUEIDENTIFIER] NOT NULL , PriorityRank int not null )
GO
ALTER TABLE dbo.EmployeeJobTitleLink ADD CONSTRAINT PK_EmployeeJobTitleLink PRIMARY KEY NONCLUSTERED (EmployeeJobTitleLinkUUID) GO
ALTER TABLE [dbo].[EmployeeJobTitleLink] ADD CONSTRAINT FK_EmployeeJobTitleLinkToEmployee FOREIGN KEY (EmployeeUUID) REFERENCES dbo.Employee (EmployeeUUID) GO
ALTER TABLE [dbo].[EmployeeJobTitleLink] ADD CONSTRAINT FK_EmployeeJobTitleLinkToJobTitle FOREIGN KEY (JobTitleUUID) REFERENCES dbo.JobTitle (JobTitleUUID) GO
ALTER TABLE [dbo].[EmployeeJobTitleLink] ADD CONSTRAINT [CK_EmployeeJobTitleLink_Sequence] CHECK ( ([PriorityRank] >= 1) AND ([PriorityRank] <=5) ) GO
ALTER TABLE [dbo].[EmployeeJobTitleLink] ADD CONSTRAINT CONST_UNIQUE_EmpUUID_JTUUID UNIQUE (EmployeeUUID , JobTitleUUID) GO
ALTER TABLE [dbo].[EmployeeJobTitleLink] ADD CONSTRAINT CONST_UNIQUE_EmpUUID_PriorityRank UNIQUE (EmployeeUUID , PriorityRank) GO
------------------------------START MOCK/FAKE Data
--Insert some Employees INSERT INTO dbo.Employee ( SSN ) select '111111111' union all select '222222222' union all select '333333333' --Insert some JobTitles INSERT INTO dbo.JobTitle ( JobTitleName ) select 'Developer' union all select 'ProjectManager' union all select 'DBA' union all select 'QA' union all select 'TechSupport'
-- Add in every (Employee, JobTitle and PriorityRank) combination INSERT INTO dbo.EmployeeJobTitleLink ( EmployeeUUID , JobTitleUUID , PriorityRank ) Select e.EmployeeUUID , jt.JobTitleUUID , (( ROW_NUMBER() OVER (ORDER BY e.EmployeeUUID , jt.JobTitleUUID ASC) ) % 5 ) + 1 from dbo.Employee e cross join dbo.JobTitle jt
--Delete a few rows to make the data interesting, Yes I know the syntax is Sql Server specific..... DELETE dbo.EmployeeJobTitleLink FROM dbo.EmployeeJobTitleLink link join ( select innerT.EmployeeJobTitleLinkUUID , ROW_NUMBER() OVER (ORDER BY innerT.EmployeeUUID , innerT.JobTitleUUID ASC) % 3 as ROWID from dbo.EmployeeJobTitleLink innerT ) as derived1 on link.EmployeeJobTitleLinkUUID = derived1.EmployeeJobTitleLinkUUID and derived1.ROWID = 0
--Show the Mock Data select e.SSN , jt.JobTitleName , link.PriorityRank from dbo.EmployeeJobTitleLink link join dbo.Employee e on link.EmployeeUUID = e.EmployeeUUID join dbo.JobTitle jt on link.JobTitleUUID = jt.JobTitleUUID ORDER BY e.SSN ,link.PriorityRank , jt.JobTitleName