Best practice to store graph with ordered edges (entity with ordered collection navigation) in Entity Framework Core

Saving a graph structure which edges are ordered (like parent-child relationship in trees) into database with EF Core involves with keeping order of child nodes unchanged after loading back entity from database, which is unfortunately not provided by normal relationship of relational database. There are already some issues about it. (#9067 #30551 #2919)

For an interleaved two type graph example from official document, the most promising way to me is to change the relationship to two unidirectional many-to-many one, add an ordering number to both mapping entities. However I didn't find a good event or interceptor to auto generate the ordering number from the ICollection field when the entity is saved to database.

The supposed event should be like:

dbContext.SavingChangesOfEntity += fun;

private void fun(object? sender, SomeArg arg) 
{
    if (sender is Post{} post)
    {
        foreach(var (tag, i) in post.Tags.Zip(Enumerable.Range(0, 0x7fffffff))) 
        {
            (dbContext.FindMappingEntity(post, tag) as PostTag).OrderNumber = i;
        }
    } 
    else if (sender is Tag{} tag)
    {
        foreach(var (post, i) in tag.Postss.Zip(Enumerable.Range(0, 0x7fffffff))) 
        {
            (dbContext.FindMappingEntity(tag, post) as TagPost).OrderNumber = i;
        }
    }
}

public class Post 
{
    public int Id { get; set; }
    public List<Tag> Tags { get; } = new();
}

public class Tag 
{
    public int Id { get; set; }
    public List<Post> Posts { get; } = new();
}

public class PostTag 
{
    public int PostId { get; set; }
    public int TagId { get; set; }
    public int OrderingNumber { get; set; }
}

public class TagPost 
{
    public int PostId { get; set; }
    public int TagId { get; set; }
    public int OrderingNumber { get; set; }
}

It's also common to store a graph as self-reference type, like:

dbContext.SavingChangesOfEntity += fun;

private void fun(object? sender, SomeArg arg) 
{
    if (sender is RichText{} richtext)
    {
        foreach(var (child, i) in richtext.Children.Zip(Enumerable.Range(0, 0x7fffffff))) 
        {
            (dbContext.FindMappingEntity(richtext, child) as RichTextBlogObject).OrderNumber = i;
        }
    }
}

public abstract class BlogObject 
{
    public int Id { get; set; }
}

public class Text : BlogObject { ... }
public class Image : BlogObject { ... }
public class Link : BlogObject { ... }

public class RichText : BlogObject 
{
    public List<BlogObject> Children { get; set; } = new();
}

public class RichTextBlogObject 
{
    public int RichTextId { get; set; }
    public int BlogObjectId { get; set; }
    public int OrderingNumber { get; set; }
}

I think both scheme looks the same in the scenario of this question, and could share a common solution.

So is it achievable for current EF Core, or are there some other practices that can do the job? Or maybe it's better to give up on the idea, and turn to some other ideas, such as only storing IDs of related entity and manually load it from database, throwing away any structured relations?

PS: It's basically the same as this question, although the author already have to set the order manually, which is not the case of this question.

You only need one joining table for PostTag or TagPost. It is merely the joining table between the two entities. Attempting to have both would likely lead to errors or worse.

You aren't describing a tree, which is a self-referencing structure, meaning a post contains other posts which forms a tree where those posts contain more posts. Instead a Post with tags where you want to maintain the order of the tags as they were added (or arranged) is just an ordered many to many.

public class Post
{
    public int Id {get; set;}
    public virtual ICollection<PostTag> PostTags { get; protected set;} = new List<PostTag>();

    [NotMapped]
    public IReadOnlyCollection<Tag> Tags 
    {
        get => PostTags
            .OrderBy(x => x.OrderBy)
            .Select(x => x.Tag)
            .ToList()
            .AsReadOnly();
    }
}

public class Tag
{
    public int Id {get; set;}
    // Optional if you intend to load tags and want their posts.
    public virtual ICollection<PostTag> PostTags { get; protected set;} = new List<PostTag>();

    [NotMapped]
    public IReadOnlyCollection<Post> Posts 
    {
        get => PostTags
            .Select(x => x.Post)
            .ToList()
            .AsReadOnly();
    }
}

public class PostTag
{
    public int PostId { get; set; }
    public int TagId { get; set; }  // Both need to be configured as a composite key
    public int OrderBy { get; set; }
    [ForeignKey(nameof(PostId))]
    public virtual Post Post { get; set; }
    [ForeignKey(nameof(TagId))]
    public virtual Tag Tag { get; set; }
}

Unless it makes sense to load Posts associated to a Tag when querying tags I'd recommend leaving the navigation unidirectional, removing Tag.TagPosts / Tag.Posts and using a .HasMany(post => post.PostTags).WithMany() on the Post configuration rather than .HasMany(post => post.PostTags).WithMany(tag => tag.PostTags). You can still get all Posts for a Tag querying posts using Post.PostTags. Note the [NotMapped] collection for Post.Tags (and Tag.Posts if needed) can be provided for convenience. You just need to ensure that you don't try and use this collection in Linq expressions prior to a query being materialized. You need to use the PostTags collection for Linq2SQL expressions. Also note that the OrderBy clause is applied to Post.Tags, but would not be used in Tag.Posts as it implies the order of tags within a post. Ordering Tag.Posts would return Posts by the position of the tag within Post.Tags so a post with that tag as the first tag would be returned before a Post with that tag in the second spot.(Unless that is an ordering you intend) If Posts should be ordered then Post should also have a SortOrder type column or order them by something like Created Date.

Edit a normal Tree hierarchy with a many-to-one TPH (table-per-hierarchy) model:

public abstract class BlogObject 
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public int OrderingNumber { get; set; } = 0;
}

public class Text : BlogObject { ... }
public class Image : BlogObject { ... }
public class Link : BlogObject { ... }

public class RichText : BlogObject 
{
    public List<BlogObject> Children { get; set; } = new();
}

For each item you create, whether Text, Image, Link, or RichText that you create at a level (I.e. within Children of a RichText, or within a collection of a top level Document or whatever object) you would set an OrderingNumber value to represent the order you want them to appear.

So if I have 3x top level RichText, they might be 1, 2, and 3. Then when I add various other elements, under each RichText I give those appropriate OrderingNumbers of 1, 2, ... or whatever, the value doesn't matter, only the order.

Alternatively you can also utilize something like SQL Server's HierarchyId to both organize the hierarchy of items without relying on things like ParentId, and even manage sorting as the hierarchy #'s do not need to reflect the IDs of the items being organized. You can see examples of this at: https://www.meziantou.net/using-hierarchyid-with-entity-framework-core.htm

public abstract class BlogObject 
{
    public int Id { get; set; }
    public HierarchyId Hierarchy { get; set; } // /1   /2  /3  /1/1  /1/2 ..
}

The numbers in a Hierarchy represent the unique order of the item at that level rather than the Id/parentId etc.