EF6不会从数据库生成外键关联 [英] EF6 Does not generate foreign key association from database

问题描述

问题：

我正在尝试使用数据库第一种方法创建一个EF6模型。简单来说，我有两个表 tblUser 和 tblMilkMan 在上有一个外键关系UserID 列。但是当我创建一个新的EDMX图表并添加了2个表格时：

1. 我没有看到关系


2. 没有导航属性被创建

请告诉我我做错了什么！

数据库架构

表脚本：

 code> CREATE TABLE [dbo]。[tblMilkMan] 
（
 [RecordID] [INT] NOT NULL 
 IDENTITY（1，1），
 [UserID] [ INT] NOT NULL，
 [IsMyTurn] [BIT] NOT NULL，
 [RoundRobinOrder] [INT] NOT NULL 
）
 ON [PRIMARY] 
 GO 
 ALTER TABLE [dbo]。[tblMilkMan] ADD CONSTRAINT [PK_tblMilkMan] PRIMARY KEY CLUSTERED（[RecordID]）ON [PRIMARY] 
 GO 
 ALTER TABLE [dbo]。[tblMilkMan] ADD CONSTRAINT [FK__MilkMan__User__UserID ] FOREIGN KEY（[UserID]）REFERENCES [dbo]。[tblUser]（[UserID]）ON DELETE CAS CAD $ $ $ $ $ $ $ $ $ $ $ $ $））））））））））））））））））））））））））））））））））））））））））））））））））））） ，
 [LogonName] [VARCHAR]（20）COLLATE SQL_Latin1_General_CP437_CI_AS 
 NOT NULL，
 [Client] [CHAR]（4）COLLATE SQL_Latin1_General_CP437_CI_AS 
 NOT NULL，
 [密码] [VARCHAR]（20）COLLATE SQL_Latin1_General_CP437_CI_AS 
 NOT NULL，
 [PasswordExpires] [DATETIME] NULL，
 [PasswordExpiresAfter] [INT] NOT NULL 
 CONSTRAINT [DF_tblUser_PasswordExpiresAfter] DEFAULT （9999），
 [FirstName] [VARCHAR]（20）COLLATE SQL_Latin1_General_CP437_CI_AS 
 NOT NULL，
 [LastName] [VARCHAR]（20）COLLATE SQL_Latin1_General_CP437_CI_AS 
 NOT NULL 
）
 ON [PRIMARY] 
 WITH（
 DATA_COMPRESSION = ROW）
 
 GO 
 ALTER TABLE [dbo]。[tblUser] ADD CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED（[UserID]）WITH（DATA_COMPRESSION = ROW）ON [PRIMARY] 
 GO 
 CREATE UNIQUE NONCLUSTERED INDEX [IX_tblUser] ON [dbo]。[tblUser]（[UserID]）WITH（FILLFACTOR = 90，DATA_COMPRESSION = ROW）ON [PRIMARY] 
 GO 
  

EDMX设计器视图

相关链接什么帮助我：

• 为什么EF 4不会使用
  唯一索引生成与列关联的FK关系？




• EF不会生成外键来自
  的外币的折扣表

ENVIRONMENT：

• Visual Studio Ultimate 2012


• SQL Server 2014


• EF6


• Web API2

解决方案

是以下行 -

  CREATE UNIQUE NONCLUSTERED INDEX [IX_tblUser] ON [dbo]。[tblUser]（[UserID]）
 WITH（FILLFACTOR = 90，DATA_COMPRESSION = ROW）ON [PRIMARY] 
 GO 
  

我不知道在同一列上拥有Clustered和NonClustered索引的原因。一个常见的最佳做法是创建一个主键作为聚集索引。 （来源）

如果您删除 NONCLUSTERED INDEX ，它将在EDMX中看到关系。 >

如何删除？

PROBLEM:

I am trying to create a EF6 model using the database first approach. Simply speaking, I have 2 tables tblUser and tblMilkMan which have a foreign key relationship on the UserID column. But when I create a new EDMX diagram and add the 2 tables:

1. I dont see the relationship
2. No navigation properties are created

Please tell me what Im doing wrong!

DB Schema:

Table Scripts:

CREATE TABLE [dbo].[tblMilkMan]
    (
      [RecordID] [INT] NOT NULL
                       IDENTITY(1, 1) ,
      [UserID] [INT] NOT NULL ,
      [IsMyTurn] [BIT] NOT NULL ,
      [RoundRobinOrder] [INT] NOT NULL
    )
ON  [PRIMARY]
GO
ALTER TABLE [dbo].[tblMilkMan] ADD CONSTRAINT [PK_tblMilkMan] PRIMARY KEY CLUSTERED  ([RecordID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblMilkMan] ADD CONSTRAINT [FK__MilkMan__User__UserID] FOREIGN KEY ([UserID]) REFERENCES [dbo].[tblUser] ([UserID]) ON DELETE CASCADE
GO

CREATE TABLE [dbo].[tblUser]
    (
      [UserID] [INT] NOT NULL
                     IDENTITY(1, 1) ,
      [LogonName] [VARCHAR](20) COLLATE SQL_Latin1_General_CP437_CI_AS
                                NOT NULL ,
      [Client] [CHAR](4) COLLATE SQL_Latin1_General_CP437_CI_AS
                         NOT NULL ,
      [Password] [VARCHAR](20) COLLATE SQL_Latin1_General_CP437_CI_AS
                               NOT NULL ,
      [PasswordExpires] [DATETIME] NULL ,
      [PasswordExpiresAfter] [INT] NOT NULL
                                   CONSTRAINT [DF_tblUser_PasswordExpiresAfter] DEFAULT ( 9999 ) ,
      [FirstName] [VARCHAR](20) COLLATE SQL_Latin1_General_CP437_CI_AS
                                NOT NULL ,
      [LastName] [VARCHAR](20) COLLATE SQL_Latin1_General_CP437_CI_AS
                               NOT NULL
    )
ON  [PRIMARY]
    WITH (
         DATA_COMPRESSION = ROW)

GO
ALTER TABLE [dbo].[tblUser] ADD CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED  ([UserID]) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblUser] ON [dbo].[tblUser] ([UserID]) WITH (FILLFACTOR=90, DATA_COMPRESSION = ROW) ON [PRIMARY]
GO

EDMX Designer View:

RELATED LINKS WHICH DIDNT HELP ME:

• Why doesn't EF 4 generate association for FK relation to column with unique index?

• EF does not generate foreign key association from foreign key in table

ENVIRONMENT:

• Visual Studio Ultimate 2012
• SQL Server 2014
• EF6
• Web API2

解决方案

The problem is the following line -

CREATE UNIQUE NONCLUSTERED INDEX [IX_tblUser] ON [dbo].[tblUser] ([UserID])
WITH (FILLFACTOR=90, DATA_COMPRESSION = ROW) ON [PRIMARY]
GO

I do not know the reason behind having Clustered and NonClustered indexes on same column. A common best practice is to create a Primary Key as Clustered Index. (Source)

If you delete that NONCLUSTERED INDEX, it will see the relation in EDMX.

How to delete it?

这篇关于EF6不会从数据库生成外键关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！