How to work with true self referencing entities in code first EF5?

Question

There are a few questions out there on this topic, but my question is very specific to true self referencing. All the examples for other questions are circular references and that doesn't help me in this case.

Lets say I have this model:

public class User
{
    [Key]
    public int Id { get; set; }

    ...

    public int CreatedByUserId { get; set; }
}

and this map:

public class UserMap : EntityTypeConfiguration<User>
{
    public UserMap()
    {
        this.HasRequired(a => a.CreatedByUser)
            .WithMany()
            .HasForeignKey(u => u.CreatedByUserId);
    }
}

After migrations generates a database with this code I can manually add a User in SQL Management Studio with Id = 1, and CreatedByUserId = 1 so that tells me that self references like this can work.

However when using EF to create a user, I run into a "unable to determine a valid ordering for dependent operations" issue. There are a lot of questions on the matter that involve a new entity that refers another new entity that has a foreign key on the first entity, which is a circular reference. The solution in those cases is either save one of entities first or to have a nullable id on the circular entity foreign key. I can not do either of those because the first would be impossible and the second is a external constraint that I cannot have nullable ids.

So, seeing how I can achieve this by adding a entry manually I can assume it's a limitation of EF5. What are the work arounds?

Answer 1

You can still satisfy your interface and do the save first then set method by adding another property to act as a nullable backer for CreatedByUserId:

public class User : ICreatable 
{
    [Key]
    public int Id { get; set; }

    ...

    public int CreatedByUserId 
    { 
        get 
        {
            if (!_CreatedByUserId.HasValue)
                //throw new exception, something went wrong.

            return _CreatedByUserId;
        }       
        set 
        {
            _CreatedByUserId = value;
        }
    }

    int? _CreatedByUserId { get; set; }
}

Answer 2

You may want to rethink the possibility that a user can create him or herself...

However if you really want to do this then there is a solution. Your main problem is the fact that your column is an IDENTITY column which means that EF doesn't specify the Id, SQL server is giving each row an auto-incrementing Id. Any value you set as the Id is ignored. You don't necessarily know when executing the INSERT what the next Id is going to be so you can't create a reference to a row that doesn't exist yet.

Change your mapping code to something like the following:

this.Property(x => x.Id)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

this.HasRequired(x => x.CreatedByUser)
    .WithMany();

You don't need to specify the foreign key if the name pattern matches (eg. CreatedByUser and CreatedByUserId).

Now when you insert a User you can specify the Id and the CreatedById. Although note that you must now always specify the Id to insert a new User. This is common practice if you are using GUIDs as Ids because you can just generate a new GUID without having to first query for the next "available" Id before creating a new object.