Association Mapping Details confusion?
- by AaronLS
I have never understood why the associations in EntityFramework look the way they do in the Mapping Details window.
When I select the line between 2 tables for an association, for example FK_ApplicationSectionsNodes_FormItems, it shows this:
Association
Maps to ApplicationSectionNodes
FormItems
(key symbol) FormItemId:Int32 <--> FormItemId:int
ApplicationSectionNodes
(key symbol) NodeId:Int32 <--> (key symbol) NodeId : int
Fortunately this one was create automatically for me based on the foreign key constraints in my database, but whenever no constraints exist, I have a hard to creating associations manually(when the database doesn't have a diagram setup) because I don't understand the mapping details for associations.
FormItems table has a primary key identity column FormItemId, and ApplicationSectionNodes contains a FormItemId column that is the foreign key and has NodeId as a primary key identity column.
What really makes no sense to me is why the association has anything listed about the NodeId, when NodeId doesn't have anything to do with the foreign key relationship? (It's even more confusing with self referencing relationships, but maybe if I could understand the above case I'd have a better handle).
CREATE TABLE [dbo].[ApplicationSectionNodes](
[NodeID] [int] IDENTITY(1,1) NOT NULL,
[OutlineText] [varchar](5000) NULL,
[ParentNodeID] [int] NULL,
[FormItemId] [int] NULL,
CONSTRAINT [PK_ApplicationSectionNodes] PRIMARY KEY CLUSTERED
(
[NodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_ApplicationSectionNodesFormItemId] UNIQUE NONCLUSTERED
(
[FormItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ApplicationSectionNodes] WITH NOCHECK ADD CONSTRAINT [FK_ApplicationSectionNodes_ApplicationSectionNodes] FOREIGN KEY([ParentNodeID])
REFERENCES [dbo].[ApplicationSectionNodes] ([NodeID])
GO
ALTER TABLE [dbo].[ApplicationSectionNodes] NOCHECK CONSTRAINT [FK_ApplicationSectionNodes_ApplicationSectionNodes]
GO
ALTER TABLE [dbo].[ApplicationSectionNodes] WITH NOCHECK ADD CONSTRAINT [FK_ApplicationSectionNodes_FormItems] FOREIGN KEY([FormItemId])
REFERENCES [dbo].[FormItems] ([FormItemId])
GO
ALTER TABLE [dbo].[ApplicationSectionNodes] NOCHECK CONSTRAINT [FK_ApplicationSectionNodes_FormItems]
GO
FormItems Table:
CREATE TABLE [dbo].[FormItems](
[FormItemId] [int] IDENTITY(1,1) NOT NULL,
[FormItemType] [int] NULL,
CONSTRAINT [PK_FormItems] PRIMARY KEY CLUSTERED
(
[FormItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FormItems] WITH NOCHECK ADD CONSTRAINT [FK_FormItems_FormItemTypes] FOREIGN KEY([FormItemType])
REFERENCES [dbo].[FormItemTypes] ([FormItemTypeId])
GO
ALTER TABLE [dbo].[FormItems] NOCHECK CONSTRAINT [FK_FormItems_FormItemTypes]
GO
