Creating a double linked list in Entity Framework

Question

I have an object which can optionally have a reference to a next and/or previous record. Something like this:

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

  [ForeignKey("Previous")]
  public int? PreviousId {get; set;}

  public Foo Previous {get; set;}

  [InverseProperty("Previous")]
  public Foo Next {get; set;}
}

Unfortunately this does not work, instead resulting in the error message Unable to determine the principal end of an association between the types Foo and Foo.

The idea is that by setting the PreviousId, the Previous Foo will get its Next set automatically by EF. This is to prevent errors caused by Next and Previous getting out of sync. Also note that PreviousId can be null, in which case no record in the database should have a Next pointing at that record. Is there any way to implement this?

Answer 1

I've managed to achieve what you wanted by using fluent api aproach. I needed to remove PreiousId property from Foo class - it will be added later on by mapping.

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

    public virtual Foo Previous { get; set; }

    public virtual Foo Next { get; set; }
}

Change as well all your properties to virtual as this will allow ef to dynamically track state of the properties in the memory. Then inside DbContext derived class you need to override OnModelCreating method and define mapping there:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Foo>()
        .HasOptional(f => f.Next)
        .WithOptionalPrincipal(f => f.Previous)
        .Map(c => c.MapKey("PreviousId"));

    base.OnModelCreating(modelBuilder);
}

This will add to Foo table PreviousId column which will be the foreign key of the relationship. It will define 1-0 relationship. If you assign one Foo entity to another's Previous property then assigned entity will have reference to it in Next property. I tested it with the following code:

using(MyDbContext context = new MyDbContext("Test"))
{
    context.Database.Delete();
    Foo foo1 = context.Foos.Create();
    Foo foo2 = context.Foos.Create();
    foo1.Next = foo2;
    context.Foos.Add(foo1);
    context.Foos.Add(foo2);
    context.SaveChanges();
}
using (MyDbContext context = new MyDbContext("Test"))
{
    Foo foo1 = context.Foos.OrderBy(f => f.Id).First();
    Foo foo2 = context.Foos.OrderBy(f => f.Id).Skip(1).First();
    // foo1.Next == foo2 and foo2.Previous == foo1
}

Answer 2

For those out there using entity framework core, this is what I wound up doing

public class LinkedListNode
{
    public int Id { get; set; }
    public int? NextId { get; set; }
    public virtual LinkedListNode Next { get; set; }
    public int? PrevId { get; set; }
    public virtual LinkedListNode Prev { get; set; }
    public long SortOrder { get; set; }
}


protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<LinkedListNode>()
        .HasOne<LinkedListNode>(x => x.Next)
        .WithMany()
        .HasPrincipalKey("Id")
        .HasForeignKey("NextId")
        .OnDelete(DeleteBehavior.Restrict)
        .IsRequired(false);

    builder.Entity<LinkedListNode>()
        .HasOne<LinkedListNode>(x => x.Prev)
        .WithMany()
        .HasPrincipalKey("Id")
        .HasForeignKey("PrevId")
        .OnDelete(DeleteBehavior.Restrict)
        .IsRequired(false);
}