Say I have a storage table/class and a folder table/class. A storage always has one root folder (which is created when the storage is created and will exist until the storage is deleted), and a folder can have zero or more children.

Storage class:

[Table("Storage")]
public class Storage
{
    public int Id { get; set; }

    public string Name { get; set; }

    public Folder RootFolder { get; set; }
}

Folder class:

[Table("Folder")]
public class Folder
{
    public int Id { get; set; }

    public int StorageId { get; set; }

    public string Name { get; set; }

    public int? ParentFolderId { get; set; }

    public virtual Collection<Folder> Children { get; set; }
}

Storage table:

 ---- ----------- 
| Id |   Name    |
 ---- ----------- 
|  1 | Storage 1 |
|  2 | Storage 2 |
 ---- ----------- 

Folder table:

 ---- ----------- ------ ---------------- 
| Id | StorageId | Name | ParentFolderId |
 ---- ----------- ------ ---------------- 
|  1 |         1 |      | null           |
|  2 |         1 | foo  | 1              |
|  3 |         1 | bar  | 1              |
|  4 |         1 | baz  | 2              |
|  5 |         2 |      | null           |
 ---- ----------- ------ ---------------- 

As you can see, to get the root folder of a storage, you need to join using Storage.Id = Folder.StorageId and ParentFolderId is null. With EF Core, how would one configure such a relationship in the OnModelCreating method?

The following obviously does not work, since the ParentFolderId is null is not included in the relationship definition:

modelBuilder.Entity<Storage>(entity =>
{
    entity.HasOne(d => d.RootFolder)
        .WithOne()
        .HasForeignKey<Storage>(d => d.StorageId);
});

CodePudding user response：

We are taking about 2 Foreign Key (FK) relations here

1. Between Storage and Folder
2. Self reference relation of Folder

So, while defining relations, you need to define the 2 FK relations such that StorageId column (FK of Storage table in Folder) is not nullable and ParentFolderId ( Self reference FK) is nullable.

Finding the root folder is more of a query logic than a FK definition. So, while querying, add a additional condition like .Where(s => s.ParentFolderId == null)