Create a double linked list in the Entity Framework

Question

I have an object that can have a link to the next and / or previous record. Something like that:

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, an Unable to determine the principal end of an association between the types Foo and Foo error message appears.

The idea is that by setting PreviousId , the previous Foo will automatically receive Next EF. This is to prevent errors caused by the synchronization of Next and Previous . Also note that PreviousId may be null , in which case no entry in the database should have Next pointing to this entry. Is there any way to implement this?

Answer 1

I managed to achieve what you wanted using the free api aproach. I needed to remove the PreiousId property from the Foo class - it will be added later by matching.

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

Change all your properties to virtual, as this will allow ef to dynamically monitor the state of properties in memory. Then, inside the DbContext derived class, you must override the OnModelCreating method and define the 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 the Foo table column PreviousId, which will be the foreign key of the relation. He will determine the relationship 1-0. If you assign one Foo object to another previous property, then the assigned object will refer to it in the Next property. I checked 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 who use the core of the entity, this is what I launched

 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); }