Entity Framework Core self referencing table

Question

I have a class called Item which references the next item and the previous item.

public class Item
{
    private Item() { }

    public Item(string itemName)
    {
        ItemId = Guid.NewGuid();
        ItemName = itemName;
    }

    public Guid ItemId { get; set; }
    public string ItemName { get; set; }

    public Guid NextItemId { get; set; }
    public virtual Item NextItem { get; set; }

    public Guid PreviousItemId { get; set; }
    public virtual Item PreviousItem { get; set; }

    public Guid GroupId { get; set; }
    public virtual Group Group { get; set; }
}

I have another table called Group it is for grouping it items.

public class Group
{
    private Group() { }
    public Group(string groupName)
    {
        GroupId = Guid.NewGuid();
        GroupName = groupName;
        GroupItems = new List<Item>();
    }

    public void AddGroupItem(Item item)
    {
        if (Items.Count == 0)
        {
            Items.Add(item);
        }
        else
        {
            item.PreviousItem = Items.Last();
            item.PreviousItemId = Items.Last().ItemId;
            Items.Last().NextItem = item;
            Items.Last().NextItemId = item.ItemId;

            Items.Add(item);
        }

    }

    public Guid GroupId { get; set; }
    public string GroupName { get; set; }
    public virtual IList<GroupItem> GroupItems { get; set; }
}

Here's how I create and save items and their group.

Group group1 = new Group("first group");
Item item1 = new Item("item 1");
Item item2 = new Item("item 2");
Item item3 = new Item("item 3");

group1.AddItem(item1);
group1.AddItem(item2);
group1.AddItem(item3);

_context.Add(group1);
_context.SaveChanges();

How do I write the OnModelCreating to handle the two references to the same table.

Dorpat

It would help to see what you tried and why it didn't work, so others won't post the same solution. Also, knowing the exact EF version is indispensable. – Meyer

Answer 1

You can do it in next way. First of all you should add two new properties to you model public virtual List<Item> ParentNextItems { get; set; } and public virtual List<Item> ParentPreviousItems { get; set; }. So your model will be something like this

public class Item
{
    private Item() { }

    public Item(string itemName)
    {
        ItemId = Guid.NewGuid();
        ItemName = itemName;
    }

    public Guid ItemId { get; set; }
    public string ItemName { get; set; }

    public Guid? NextItemId { get; set; }
    public virtual Item NextItem { get; set; }
    public virtual List<Item> ParentNextItems { get; set; }

    public Guid? PreviousItemId { get; set; }
    public virtual Item PreviousItem { get; set; }
    public virtual List<Item> ParentPreviousItems { get; set; }
}

And than you can configure it in next way

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Item>()
        .HasKey(x => x.ItemId);
    modelBuilder.Entity<Item>()
        .HasOne(x => x.NextItem).WithMany(x => x.ParentNextItems).HasForeignKey(x => x.NextItemId)
        .Metadata.DeleteBehavior = DeleteBehavior.Restrict;
    modelBuilder.Entity<Item>()
        .HasOne(x => x.PreviousItem).WithMany(x => x.ParentPreviousItems).HasForeignKey(x => x.PreviousItemId)
        .Metadata.DeleteBehavior = DeleteBehavior.Restrict;


    base.OnModelCreating(modelBuilder);
}

That's all. But if you want to achieve the same with attribute configuration, than you can skip void OnModelCreating(ModelBuilder modelBuilder) changes and just write next model:

public class Item
{
    private Item() { }

    public Item(string itemName)
    {
        ItemId = Guid.NewGuid();
        ItemName = itemName;
    }

    [Key]
    public Guid ItemId { get; set; }
    public string ItemName { get; set; }

    public Guid? NextItemId { get; set; }

    [ForeignKey(nameof(NextItemId))]
    [InverseProperty(nameof(ParentNextItems))]
    public virtual Item NextItem { get; set; }

    [ForeignKey(nameof(NextItemId))]
    public virtual List<Item> ParentNextItems { get; set; }

    public Guid? PreviousItemId { get; set; }
    [ForeignKey(nameof(PreviousItemId))]
    [InverseProperty(nameof(ParentPreviousItems))]
    public virtual Item PreviousItem { get; set; }
    [ForeignKey(nameof(PreviousItemId))]
    public virtual List<Item> ParentPreviousItems { get; set; }
}

Update Also you should make PreviousItemId, NextItemId optional (Guid?), I did corresponding changes in my answer.

And as far as I know, you just can't do it in one .SaveChanges() trip. When you create your second item you should already have first item saved into database. (anyway this is subject for another question)

But anyway if you modify you code to something like that

Group group1 = new Group("first group");
_context.Add(group1);
Item item1 = new Item("item 1");
group1.AddItem(item1);
_context.SaveChanges();
Item item2 = new Item("item 2");
group1.AddItem(item2);
_context.SaveChanges();
Item item3 = new Item("item 3");
group1.AddItem(item3);
_context.SaveChanges();

You can do it in one transaction

Vesting

I get a 'System.InvalidOperationException' when saving - Additional information: A circular dependency was detected: 'Item' {'PreviousItemId'} -> 'Item' {'ItemId'}, 'Item' {'NextItemId'} -> 'Item' {'ItemId'}, 'Item' {'NextItemId'} -> 'Item' {'ItemId'}. – Dorpat

I've added more detail to the question, including the usage. – Dorpat

Thanks for the suggestion of multiple saves, that has helped – Dorpat

Answer 2

You don't need to have Collection, since as far I understand here you aim to create some type of Two Way LinkedList. A was able to fix it by adding .HasOne() to the main model.

     modelBuilder.Entity<Item>().HasOne(x => x.NextItem);
     modelBuilder.Entity<Item>().HasOne(x => x.PreviousItem);

Patio

Answer 3

public int? DeaultNextStateId { get; set; }
public State DeaultNextState { get; set; }
public virtual ICollection<State> DeaultNextStates { get; set; }

public int? OkNextStateId { get; set; }
public State OkNextState { get; set; }
public virtual ICollection<State> OkNextStates { get; set; }

public int? NotOkNextStateId { get; set; }
public State NotOkNextState { get; set; }
public virtual ICollection<State> NotOkNextStates { get; set; }

modelBuilder.Entity<Models.Workflow.State>()
    .HasKey(x => x.Id);
modelBuilder.Entity<Models.Workflow.State>()
    .HasOne(x => x.DeaultNextState).WithMany(x => x.DeaultNextStates).HasForeignKey(x => x.DeaultNextStateId)
        .Metadata.DeleteBehavior = DeleteBehavior.Restrict;

modelBuilder.Entity<Models.Workflow.State>()
    .HasOne(x => x.OkNextState).WithMany(x => x.OkNextStates).HasForeignKey(x => x.OkNextStateId)
        .Metadata.DeleteBehavior = DeleteBehavior.Restrict;

modelBuilder.Entity<Models.Workflow.State>()
    .HasOne(x => x.NotOkNextState).WithMany(x => x.NotOkNextStates).HasForeignKey(x => x.NotOkNextStateId)
        .Metadata.DeleteBehavior = DeleteBehavior.Restrict;

Tayib

There is no explanation given, nor does it match the op's question. – Thermotherapy