How to define parent/children relation with EntityFrameworkCore?-CodePudding

I have a project that uses EntityFrameworkCore to access data from a database.

I am trying to create a parent/children relation for a single object (i.e, Category). My object looks like this

public class Category
{
    public int Id { get; set;}

    public string Name { get; set; }

    public int? ParentCategoryId { get; set; }

    public Category ParentCategory { get; set; }

    public ICollection<Category> Children { get; set; }
}

The property ParentCategoryId is optional, but when set, it'll determine the parent of the current category. At the same time, when the Children relation is included, I want to be able to pull all the categories where ParentCategoryId equal the id of the current category.

I added the following code in the context to define the relations

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    builder.Entity<Category>(cat =>
    {
        cat.HasMany(x => x.Children).WithOne().HasForeignKey(x => x.ParentCategoryId);
        cat.HasOne(x => x.ParentCategory).WithOne();
    });
}

So when the following code is called I want to get the ParentCategory and all Children of category id 10.

var category = _context.Categories.Where(cat => cat.Id == 10)
                       .Include(x => x.ParentCategory)
                       .Include(x => x.Children)
                       .ToList();

However, the above code gives me the following error

Invalid column name 'ParentCategoryId1'." string

How can I correctly define the relations for ParentCategory and Children?

CodePudding user response：

You have to use more simple names for navigation properties, otherwise EF getting confused.This code was tested and working properly

public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public int? ParentId { get; set; }
        public virtual Category Parent { get; set; }

        public virtual ICollection<Category> Children { get; set; }

     }

and dbcontext

       public virtual DbSet<Category> Categories { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            modelBuilder.Entity<Category>()
            .HasOne(s => s.Parent)
            .WithMany(m => m.Children)
            .HasForeignKey(e => e.ParentId);

            OnModelCreatingPartial(modelBuilder);
        }