بدست آوردن دیتاهای سلسله مراتبی در SQL

public class Users
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public string FullName { get; set; }
    public ICollection<Comments> ChildComments { get; set; }
    public ICollection<Posts> Posts { get; set; }
}
public class Posts
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public string Title { get; set; }
    public string Descriptions { get; set; }
    public int UserId { get; set; }
    public Users User { get; set; }
    public ICollection<Comments> ChildComments { get; set; }
}
public class Comments
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public int PostId { get; set; }
    public int UserId { get; set; }
    public string Text { get; set; }
    public int? ParentId { get; set; }
    public Users User { get; set; }
    public Posts Post { get; set; }
    public Comments Parent { get; set; }
    public ICollection<Comments> ChildComments { get; set; }
}

public class HierarchicalDataDbContext : DbContext
{
    public HierarchicalDataDbContext()
    {
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer("Data Source=.; Initial Catalog=HierarchicalData; Integrated Security=True;");
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Users>()
            .HasData(new Models.Users
            {
                Id = 1,
                FullName = "Farhad Zamani"
            },
            new Users
            {
                Id = 2,
                FullName = "Himan falahi"
            });
        modelBuilder.Entity<Posts>()
            .HasData(new Models.Posts
            {
                Id = 1,
                UserId = 1,
                Title = "Test HierarchicalData",
                Descriptions = "HierarchicalData"
            });
        modelBuilder.Entity<Comments>()
            .HasOne(a => a.User)
            .WithMany(a => a.ChildComments)
            .HasForeignKey(a => a.UserId)
            .IsRequired()
            .OnDelete(DeleteBehavior.Restrict);
        modelBuilder.Entity<Comments>()
            .HasOne(a => a.Parent)
            .WithMany(a => a.ChildComments)
            .HasForeignKey(a => a.ParentId)
            .IsRequired(false)
            .OnDelete(DeleteBehavior.Restrict);
        modelBuilder.Entity<Comments>()
            .HasData(new Models.Comments
            {
                Id = 1,
                ParentId = null,
                PostId = 1,
                Text = "First comments",
                UserId = 1
            }, new Models.Comments
            {
                Id = 2,
                ParentId = 1,
                PostId = 1,
                Text = "Reply to first comments",
                UserId = 2
            }, new Models.Comments
            {
                Id = 3,
                ParentId = null,
                PostId = 1,
                Text = "Second comment",
                UserId = 1
            }, new Models.Comments
            {
                Id = 4,
                ParentId = 2,
                PostId = 1,
                Text = "Reply to previous comment",
                UserId = 2
            }, new Models.Comments
            {
                Id = 5,
                ParentId = 3,
                PostId = 1,
                Text = "Reply to second comment",
                UserId = 2
            });
    }
    public DbSet<Users> Users { get; set; }
    public DbSet<Posts> Posts { get; set; }
    public DbSet<Comments> Comments { get; set; }
}

static async Task Main(string[] args)
{
    using (SqlConnection connection = new SqlConnection("Data Source=.; Initial Catalog=HierarchicalData; Integrated Security=True;"))
    {
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("CommentId", 1, DbType.Int32);
        string query = @"
                    WITH Hierachy(Id, ParentId,UserId,Text) AS (
                                      SELECT Id, ParentId,UserId,Text
                                          FROM Comments
                                      UNION ALL
                                      SELECT h.Id, comment.ParentId,h.UserId,h.Text
                                      FROM Comments comment
                                      INNER JOIN Hierachy h ON comment.Id = h.ParentId
                              )
	                 Select * from Comments where Id in 
	                 ( SELECT Id
	                 	 FROM Hierachy h
	                 	 WHERE h.ParentId = @CommentId)";
        var affectedRows = (await connection.QueryAsync<Comments>(query, parameters)).AsEnumerable();
        foreach (var comment in affectedRows)
        {
            Console.WriteLine($"{comment.Id}\t{comment.Text}");
        }
        Console.ReadLine();
    }
}

2       Reply to first comments
4       Reply to previous comment

<PackageReference Include="Dapper" Version="2.0.35" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.7" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.7" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.7">
  <PrivateAssets>all</PrivateAssets>
  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>