EF6 Does not generate foreign key association from database

c# entity-framework entity-framework-6 foreign-key-relationship sql-server

Question

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:

ER Diagram

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:

VS2012 View

RELATED LINKS WHICH DIDNT HELP ME:

ENVIRONMENT:

  • Visual Studio Ultimate 2012
  • SQL Server 2014
  • EF6
  • Web API2
1
2
5/23/2017 12:24:55 PM

Accepted Answer

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?

enter image description here

enter image description here

2
1/7/2015 6:23:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow