In Entity Framework 4 is it possible to model a recursive foreign key as one-to-one?

I have the following table:

CREATE TABLE [dbo].[Exception] (
    [ExceptionID]       INT           IDENTITY (1, 1) NOT NULL,
    [ParentExceptionID] INT           NULL,
    [ApplicationID]     INT           NOT NULL,
    [TypeName]   开发者_如何学C       VARCHAR (256) NOT NULL,
    [Message]           VARCHAR (MAX) NULL,
    [StackTrace]        VARCHAR (MAX) NULL,
    [MachineName]       VARCHAR (128) NULL,
    [UserName]          VARCHAR (64)  NULL,
    [CreatedOn]         DATETIME      NOT NULL
)

This is used to store exceptions that happen in applications. I have a foreign key as such:

ALTER TABLE [dbo].[Exception] ADD CONSTRAINT [FK_ParentException_Exception] 
FOREIGN KEY ([ParentExceptionID])
REFERENCES [dbo].[Exception] ([ExceptionID])
ON DELETE NO ACTION ON UPDATE NO ACTION;

Why I designed it as ParentExceptionID instead of InnerExceptionID, I couldn't tell you. This definitely makes it extra confusing modeling this in E.F. It's entirely arbitrary, though, as you can express the same data either way.

As you should know, an Exception can only have one inner exception. However, this schema allows multiple rows to claim to have the same ParentExceptionID. Logically, this never happens, but the data supports it.

Normally when I want to make a non-recursive one-to-one relationship in my schema I either use the same primary key column name in both tables, or I add a unique index to the foreign key of the second table.

The former would look like this:

Table A
    PrimaryKey int
    [...]

Table B
    PrimaryKey int
    [...]

Or the other way:

Table A
    PrimaryKeyA int
    [...]

Table B
    PrimaryKeyB int
    PrimaryKeyA int unique references A.PrimaryKeyA
    [...]

In either case SQL diagrams and other tools will recognize this as a one-to-one relationship. Entity Framework only recognizes the first pattern, though, I've read.

My problem is, with a recursive foreign key, I can't use either pattern. I obviously can't have the first pattern because I have only one table. I can't use the second because I can't have a unique index on the foreign key column since there will be more than one row that has a NULL ParentExceptionID. Man, I wish SQL Server allowed a unique index that didn't force NULL to also be unique!

When I try to make an E.F. model from this schema it sees that multiple rows can claim to have the same ParentExceptionID, and it forces me to model this as a 0..1 -> *. I can't see any way to stop this. My Exception entity has an InnerException navigation property and it's a collection. Clearly, this is not what I need. If I try to change the multiplicity of the association and force it to be 0..1 I get a build error:

Multiplicity is not valid in Role 'InnerException' in relationship 'FK_ParentException_Exception'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be *.

Is there any way to do what I want? Even if that means changing my schema, I might consider it. Right now I'm thinking it's impossible.

---

No there is no way to map this in EF and even Unique index on the foreign key will not help you because current version of EF doesn't have support for unique keys.

The only way is to map it as one-to-many and hide the real navigation property as for example internal member. Instead of that you will expose another property (not in the entity diagram) in your partial part of the entity class which will allow definition of only single inner exception (it will simply work with the first item from the internal collection).

The problematic part of this solution would be eager loading of related exception because you can't eager load custom properties but only navigation properties.