We are building an application using .NET 6 and EF Core 6 with an existing SQL Server database. We are using the database first approach and running the Scaffold-DbContext tool we were able to generate the dbcontex class. Everything works fine, a part for a parent child relation between two tables:

The scaffold tool, for the above tables generated the following two classes:

public partial class TreeNode
    {
        public TreeNode()
        {
            TreeNodeHierarchyChildren = new HashSet<TreeNodeHierarchy>();
            TreeNodeHierarchyParents = new HashSet<TreeNodeHierarchy>();
        }

        public int Id { get; set; }
        public string Name { get; set; }
        public string Code { get; set; }
        public bool IsLeaf { get; set; }
        public int? OrganisationId { get; set; }
        public bool IsDeleted { get; set; }

        public virtual ICollection<TreeNodeHierarchy> TreeNodeHierarchyChildren { get; set; }
        public virtual ICollection<TreeNodeHierarchy> TreeNodeHierarchyParents { get; set; }
    }


public partial class TreeNodeHierarchy
    {
        public int Id { get; set; }
        public int ParentId { get; set; }
        public int ChildId { get; set; }

      
        public virtual TreeNode Child { get; set; }

        public virtual TreeNode Parent { get; set; }
    }

And in the dbcontext class the following mapping:

modelBuilder.Entity<TreeNode>(entity =>
            {
                entity.ToTable("TreeNode");
                entity.Property(e => e.Code).HasMaxLength(100);
                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(255);
            });

            modelBuilder.Entity<TreeNodeHierarchy>(entity =>
            {
                entity.ToTable("TreeNodeHierarchy");

                entity.HasOne(d => d.Child)
                    .WithMany(p => p.TreeNodeHierarchyChildren)
                    .HasForeignKey(d => d.ChildId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_TreeNodeHierarchy_TreeNode_Child");

                entity.HasOne(d => d.Parent)
                    .WithMany(p => p.TreeNodeHierarchyParents)
                    .HasForeignKey(d => d.ParentId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_TreeNodeHierarchy_TreeNode_Parent");
            });

Here is the issue, when I write the following:

var nodes = _context.TreeNodes.Include(th => th.TreeNodeHierarchyChildren)
                .Where(tn => tn.IsLeaf)
                .....

it loads the child but not the parent.

This relation works properly in the current application (.net 4.7) using LINQ to SQL.

Am I missing something?

Updated

as suggested from @SpruceMoose, I included also the TreeNodeHierarchyParents property in the query but it didn't fix the issue.

var nodes = _context.TreeNodes
            .Include(th => th.TreeNodeHierarchyChildren)
            .Include(th => th.TreeNodeHierarchyParents)
            .Where(tn => tn.IsLeaf)

CodePudding user response：

You need to explicitly (eagerly) load the Parent elements by using an Include() on the TreeNodeHierarchyParents navigation property (as you are currently for the TreeNodeHierarchyChildren navigation property).

Change your linq query to the following:

var nodes = _context.TreeNodes
            .Include(th => th.TreeNodeHierarchyChildren)
            .Include(th => th.TreeNodeHierarchyParents)
            .Where(tn => tn.IsLeaf)
            .....

CodePudding user response：

I think your relationship mapping is wrong. You say one child has many children and one parent has many parents. It should be one child has many parents, and one parent has many children.

I think it's also a good idea to define these kinds of relationships on both sides, so that if you get something wrong it shows up as an error faster. Note also that I think some of these statements would already be the default.

Also, important, note that I think you need to use Nullable Reference Types to indicate nullability. Anything that is supposed to be nullable should have a ? on its type name in the entity types. Though I think it's possible you should cascade delete, not set null. It depends how your model works.

Something like this, though I can't guarantee compilation:

modelBuilder.Entity<TreeNode>(tnb => {
   tnb.ToTable("TreeNode");
   tnb.Property(tn => tn.Code).HasMaxLength(100);
   tnb.Property(tn => tn.Name).IsRequired().HasMaxLength(255);

   tnb
   .HasMany(tn => tn.TreeNodeHierarchyParents)
   .WithOne(tnh => tnh.Child);

   tnb
   .HasMany(tn => tn.TreeNodeHierarchyChildren)
   .WithOne(tnh => tnh.Parent);   
});

modelBuilder.Entity<TreeNodeHierarchy>(tnhb => {
   tnhb.ToTable("TreeNodeHierarchy");

   tnhb
   .HasOne(tnh => tnh.Child)
   .WithMany(tn => tn.TreeNodeHierarchyParents)
   .HasForeignKey(tnh => tnh.ChildId)
   .OnDelete(DeleteBehavior.ClientSetNull)
   .HasConstraintName("FK_TreeNodeHierarchy_TreeNode_Child");

   tnhb
   .HasOne(tnh => tnh.Parent)
   .WithMany(tn => tn.TreeNodeHierarchyChildren)
   .HasForeignKey(tnh => tnh.ParentId)
   .OnDelete(DeleteBehavior.ClientSetNull)
   .HasConstraintName("FK_TreeNodeHierarchy_TreeNode_Parent");
});

One thing you can do to try to make sure your model definition is correct is to create an empty second database with it and compare its model against the real one, and then keep fine tuning it until you get it right.