无法基于外键映射关联 [英] Cannot map association based on foreign key

问题描述

我正在使用Visual Studio 2008 SP1支持的实体框架。在我的sql 2008数据库中，我有下面定义的表。

I am using the entity framework supported by Visual Studio 2008 SP1. In my sql 2008 database I have the following tables defined below.

当我添加这些表时，使用主键外键列没有创建任何关联的状态
StateProvinceCode

When I add these tables, no association is created out of the realtion using the primary key foreign key column
StateProvinceCode

tblCityPostalCodes中缺少上面的列。如果我删除SQL服务器中的关系并重新添加表，则会创建上面的列，但是当我尝试在实体设计器中手动添加关联时，当我保存它时会抱怨关联未被映射。

The column above is missing from tblCityPostalCodes. If I remove the relationship in SQL server and re-add the tables, the colum above IS created, but when I try to add the associatuion by hand in entity designer, when I save it complains about the association NOT being mapped.

请解释可能出错的地方

CREATE TABLE [dbo]。[tblStatesProvinces]（
[StateProvinceCode] [char]（3）NOT NULL，
[CountryCode] [varchar]（50）NOT NULL，
[StateProvinceName] [varchar]（50）NOT NULL，
CONSTRAINT [ PK_tblStatesProvinces] PRIMARY KEY CLUSTERED
（主要群集代码ASC）（WITH [PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]
）ON [PRIMARY]

CREATE TABLE [dbo].[tblStatesProvinces](
 [StateProvinceCode] [char](3) NOT NULL,
 [CountryCode] [varchar](50) NOT NULL,
 [StateProvinceName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblStatesProvinces] PRIMARY KEY CLUSTERED
(
 [StateProvinceCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo]。[tblCityPostalCodes]（
[ID] [int] IDENTITY（1,1）NOT NULL，
[StateProvinceCode] [char]（3）NULL，
[StateProvinceName] [varchar]（50）NULL，
[City] [varchar]（50）NULL，
[PostalCode] [varchar]（50）N ULL，
CONSTRAINT [PK_tblStateCityPostalCodes] PRIMARY KEY CLUSTERED
（（[ID] ASC
）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]
）ON [PRIMARY]

CREATE TABLE [dbo].[tblCityPostalCodes](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [StateProvinceCode] [char](3) NULL,
 [StateProvinceName] [varchar](50) NULL,
 [City] [varchar](50) NULL,
 [PostalCode] [varchar](50) NULL,
 CONSTRAINT [PK_tblStateCityPostalCodes] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo]。[tblCityPostalCodes] WITH CHECK ADD CONSTRAINT [FK_tblCityPostalCodes_tblStatesProvinces] FOREIGN KEY（ [StateProvinceCode]）
参考[dbo]。[tblStatesProvinces]（[StateProvinceCode]）

ALTER TABLE [dbo].[tblCityPostalCodes]  WITH CHECK ADD  CONSTRAINT [FK_tblCityPostalCodes_tblStatesProvinces] FOREIGN KEY([StateProvinceCode])
REFERENCES [dbo].[tblStatesProvinces] ([StateProvinceCode])

ALTER TABLE [dbo]。[tblCityPostalCodes] CHECK CONSTRAINT [FK_tblCityPostalCodes_tblStatesProvinces]

ALTER TABLE [dbo].[tblCityPostalCodes] CHECK CONSTRAINT [FK_tblCityPostalCodes_tblStatesProvinces]

推荐答案

我使用了创建两个表的DDL脚本以及它们之间的外键。我在VS中打开了一个实体数据模型，并在向导中选择了两个表。
在设计器中，我确实得到了两个表， 关联正确映射到表' tblCityPostalCodes' 。

您的脚本运行良好，EF Deisgner成功创建了关联并将其映射到右表。

但是，值得注意的是，目前实体设计人员不在Designer上公开外键。因此，您未在设计器中的实体上看到"外键"列"StateProvinceCode"这一事实是预期行为。
但是，您将在Mapping设计器的下拉列表中看到此外键。

单击设计器上的关联线并选择"在映射窗口中显示"将显示关联的映射信息。

如果您删除FK关系，同意您现在可以在设计师身上看到外键，但你不会看到关联设置，因为两个实体之间没有关系。

建议：使用具有FK关系的原始脚本，只需单击关联并检查映射详细信息。

乔治

I have used your DDL script that created the two tables and the foreign keys between them.
I opened an Entity Data model in VS and selected the two tables in the wizard.
In the designer I did get the two tables and the association was correctly mapped to the table 'tblCityPostalCodes'.

Your script worked well, and the EF Deisgner successfully created the association and mapped it to the right table.

However, it is important to note that currently the Entity designer does not expose the foreign keys on the Designer. So the fact that you did not see the 'Foreign Key' column 'StateProvinceCode' on the Entity in the designer is the expected behavior.
However, you will see this Foreign Key in the Drop Down List of the Mapping designer.

Clicking on the association line on the designer and selecting 'Show in Mapping Window' will reveal the mapping information of the association.

If you remove the FK Relationship, agreed you will now be able to see the Foreign Key on the designer, but you will not see the association set up as there is not relationship b/w the two entities.

Suggestion: Use the original script with the FK relation and just click on the association and check the mapping details.

George

这篇关于无法基于外键映射关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！