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

Question

I have run into a scenario where Entity Framework 4.0 does not generate an association to an entity backed by a table having a unique index, and I am wondering why.

The basic setup is this: Let's say I have two tables in SQL Server 2008 R2 and a foreign key relation:

CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [GroupId] [int] NULL,
 CONSTRAINT [PK_User] 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].[Group](
[Id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Group] 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].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Group] 
    FOREIGN KEY([GroupId])
REFERENCES [dbo].[Group] ([Id])

Moreover, assume the following index is present:

CREATE NONCLUSTERED INDEX [IX_Group] ON [dbo].[Group] 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

If I tell the designer in Visual Studio 2010 to generate an ADO.NET Entity Data Model I get a model with two classes, User and Group, User having a Navigation Property called Group. That is all fine and well.

Now, let's say instead that the index looked like this:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Group] ON [dbo].[Group] 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

That is, the only thing I have done is make the index a unique index. Having done this, when I tell Visual Studio's designer to generate an Entity Model, the association between users and groups doesn't show up and the User has no navigation properties. Inspecting the generated EDMX file reveals that the storage model has no AssociationSet at all.

Can anyone explain why this is? Why does the unique index prevent EF from modeling the relationship?

Thank you.

Answer 1

A unique index allows for 1 NULL value, a primary key doesn't allow NULLS. How will you match the NULL when nothing is equal to NULL not even another NULL

Answer 2

I have the same problem. However, in my case, the column that I'm attempting to link to is not the primary key. Hence, it won't work. The EDMX file won't compile saying that it has to link to a primary key. This makes sense. The only reason I'm running into the problem is that I'm dealing with a legacy database that was poorly designed.

However, in your case, you are creating a unique index on an identity column. Why? An identity column is guaranteed to be unique anyway. Also, it is your primary key which would have an index on it by default.

I found this link that explains that associations to unique constraints aren't currently supported in EF, but, it looks like they are planning it for a future release.

http://blogs.msdn.com/b/efdesign/archive/2011/03/09/unique-constraints-in-the-entity-framework.aspx?wa=wsignin1.0&CommentPosted=true#commentmessage