How to map a nested entity while both are attached to any other single Entity in EF Core 3

[ajcvickers]

Moved from discussions.

Question

From @hamidyaseen

To store Notes objects/entities in Folder and it's sub Folder. Actually, a "Catergory" Entity have many "Folder"/s (say parent), which itself can have further "Folder" (say child folder), but no more level of sub-folder. So relation is as
Category => Folder/s (parent folders)=> Folder/s. (child folders). that's it.
How to map/link a "Note" entity, which could be in a Parent-Folder or in any Child Folder?

Replies

From @ajcvickers

@hamidyaseen Please post the C# code for the types that you want to map.

---

From @hamidyaseen

public class Category  {
        public Category() {
              Childrens = new List<Folder>();
        }		
        public int Id { get; set; }
        public string Name { get; set; }
        public string Type { get; set; }
        
        public List<Folder> Childrens { get; set; }
}
public class Folder {
        public Folder() {
	       Notes = new List<Note>();
               Children = new List<SubFolder>();
        }		
        public int Id { get; set; }
        public string Name { get; set; }
        public int Type { get; set; }

	public List<Note> Notes { get; set; }
        public List<SubFolder> Children { get; set; }
}
public class SubFolder {
	public SubFolder() {
		Notes = new List<Note>();            
        }
        public int Id { get; set; }
        public string Name { get; set; }
        public int Type { get; set; }
		
	public List<Note> Notes { get; set; }
}
	
public class Note  {
        public int Id { get; set; }
        public string Title { get; set; }
        public DateTime Deadline { get; set; }
        public DateTime Reminder { get; set; }
        public string Detail { get; set; }
	public int ContainerId { get; set;} //Could be folder/subFolder id 		
}

Quest:- As Folder & SubFolder are almost the same entities (except no more folders under SubFolder). For simplicity, considering them two separate entities, so EFcore mapped to two DB tables. right. As a Note entity could be in a Folder or SubFolder. But mapped Note Db table have two foreign keys, one for Folder and one for SubFolder. Instead of a single foreign key for containing folder key.

[ajcvickers]

@hamidyaseen I don't think there is a way to map this using EF Core. The ContainerId FK could reference either a Folder or SubFolder, and EF has no way of determining which one.

@AndriySvyryd Would this be possible after #10140?

[AndriySvyryd]

@ajcvickers No, #10140 won't help here

@hamidyaseen You can derive Folder from SubFolder, so it will inherit the relationship with Note