How to create a self referencing table using code first?

Question

I have an entity that has a reference to itself in a parent - child relationship. I need to find out how to implement this using code first and fluent API. Below is my entity class.

public class MenuItem
{
    public int Id { get; set; }
    public string LinkText { get; set; }
    public string ControllerName { get; set; }
    public string ActionName { get; set; }

    public MenuItem Parent { get; set; }
    public int ParentId { get; set; }

    private IList<Role> Roles;
    private IList<MenuItem> ChildMenuItems;

    public MenuItem()
    {
        Roles = new List<Role>();
        ChildMenuItems = new List<MenuItem>();
    }
}

I tried using the below code in my entity configuration.

HasOptional(m => m.Parent)
                .WithMany(m => m.ChildMenuItems)
                .HasForeignKey(m => m.ParentId)
                .WillCascadeOnDelete(false);

but I got this error -

One or more validation errors were detected during model generation:

Vantage.Data.EF.MenuItem_Parent: : Multiplicity conflicts with the referential constraint in Role 'MenuItem_Parent_Target' in relationship 'MenuItem_Parent'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.

All help appreciated.

Thank You.

score 0 · Answer 1 · answered Aug 30 '17 at 08:48

0

ParentId field should be nullable. You are not able to create any record if parentid is required.

Just change public int ParentId { get; set; }

to public int? ParentId { get; set; }

answered Aug 30 '17 at 08:48

Gedao

1,025
9
13

score 0 · Answer 2 · answered Jun 13 '23 at 06:42

If you'd like to use entityframework, so you can use this approach. ParentId field should be nullable so:

public class MenuItem
{
    public int Id { get; set; }
    public string LinkText { get; set; }
    public string ControllerName { get; set; }
    public string ActionName { get; set; }

    public MenuItem Parent { get; set; }
    public int? ParentId { get; set; }

    private IList<Role> Roles;
    private IList<MenuItem> ChildMenuItems;

    public MenuItem()
    {
        Roles = new List<Role>();
        ChildMenuItems = new List<MenuItem>();
    }
}

Also you should use fluentApi as follows:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Self Referencing Entity
            modelBuilder.Entity<MenuItem>()
                        .HasOptional(x => x.Parent)
                        .WithMany(x => x.ChildMenuItems)
                        .HasForeignKey(x => x.ParentId)
                        .WillCascadeOnDelete(false);

            base.OnModelCreating(modelBuilder);
        }

Now, if you'd like to use entityframeworkCore, you should use below FluentApi.

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MenuItem>(entity =>
            {
                entity.HasIndex(e => e.ParentId);

                entity.HasOne(d => d.Parent)
                    .WithMany(p => p.ChildMenuItems)
                    .HasForeignKey(d => d.ParentId);
            });
        }

Also, you can use this class without FluentApi by data annotation in efcore as follows:

public class MenuItem
{
    public int Id { get; set; }
    public string LinkText { get; set; }
    public string ControllerName { get; set; }
    public string ActionName { get; set; }
    [ForeignKey("ParentId")]
    [InverseProperty("ChildMenuItems")]
    public MenuItem Parent { get; set; }
    public int? ParentId { get; set; }

    private IList<Role> Roles; 
    [InverseProperty("Parent")]       
    private IList<MenuItem> ChildMenuItems;

    public MenuItem()
    {
        Roles = new List<Role>();
        ChildMenuItems = new List<MenuItem>();
    }
}

Of course, the index formation part on ParentId can only be done by Fluent API.

I know this question is for six years ago, but because I was looking for the answer to this question myself and now I found it, I thought it would be better to give an answer to this question for those who may face this problem in the future.

How to create a self referencing table using code first?

2 Answers2