asp.net-mvc-3 – 如何有效地从自相关表中加载数据

那就让我们继续吧：

What is the best way to retreive this data using SQL?

我建议你看看following article,它说明了一种非常好的技术,可以非常有效地管理这种分层数据.它使用嵌套集模型,您可以在其中使用左侧和右侧节点定义集合,然后您可以使用单个SQL查询构建整个树：

Is there a better way to load this data using an ORM?

有很多方法可以使用ORM来实现这一点,例如NHibernate和EF,但是下次我会留下这个.您可以考虑将您的问题分成多个SO问题,因为主题非常广泛.如果您使用普通ADO.NET学习如何执行此操作,您将更好地理解所涉及的基础技术,以便明天您决定使用这样的ORM,您将知道要查找的内容以便进行有效查询.

How about displaying this kind of data say using the razor syntax for
a view??

一旦构建了分层模型,它就非常简单.您所要做的就是为Post类型定义一个自定义显示模板,您可以在其中为所有子帖子调用显示模板.

所以假设以下模型：

public class Post
{
    public int PostId { get; set; }
    public string PostTitle { get; set; }
    public IEnumerable<Post> ChildPosts { get; set; }
}

和下面的控制器(我显然已经硬编码了这些值,但在阅读了我在帖子开头链接到的教程后,您将能够使用单个SQL查询构建此模型)：

public class HomeController : Controller
{
    public ActionResult Index()
    {
        // Hardcoding the model here,but you could use the 
        // Nested Set Model technique I have linked to 
        // in order to build this model from your database
        var post = new Post
        {
            PostId = 1,PostTitle = "Parent Post",ChildPosts = new[]
            {
                new Post 
                {
                    PostId = 2,PostTitle = "Child Post 1",ChildPosts = new[]
                    {
                        new Post 
                        {
                            PostId = 3,PostTitle = "Child Post 1-1",ChildPosts = new[]
                            {
                                new Post
                                {
                                    PostId = 4,PostTitle = "Child Post 1-2-1"
                                }
                            }
                        },new Post 
                        {
                            PostId = 5,PostTitle = "Child Post 1-2"
                        },}
                },new Post 
                {
                    PostId = 6,PostTitle = "Child Post 2",ChildPosts = new[]
                    {
                        new Post
                        {
                            PostId = 7,PostTitle = "Child Post"
                        }
                    }
                },new Post 
                {
                    PostId = 8,PostTitle = "Child Post 3"
                },}
        };
        return View(post);
    }
}

然后你会有一个?/ Views / Home / Index.cshtml视图：

@model Post
<ul>
    @Html.DisplayForModel()
</ul>

当然还有一个相应的显示模板(?/ Views / Home / DisplayTemplates / Post.cshtml),在我们的例子中它将是递归的,以呈现完整的树：

@model Post
<li>
    @Html.DisplayFor(x => x.PostTitle)
    <ul>
        @Html.DisplayFor(x => x.ChildPosts)
    </ul>
</li>

当然,最终结果是人们可能期望的结果：

更新：

根据评论部分的要求,这里有一个例子,说明如何填充Post模型.假设您已按照nested set model设计数据库表：

CREATE TABLE posts (id int primary key,left int,right int,title nvarchar(100));

并且你已经填写了帖子：

INSERT INTO posts (id,left,right,title) VALUES (1,1,16,'Parent Post');
INSERT INTO posts (id,title) VALUES (2,2,9,'Child Post1');
INSERT INTO posts (id,title) VALUES (3,3,4,'Child Post1-1');
INSERT INTO posts (id,title) VALUES (4,5,8,'Child Post1-2');
INSERT INTO posts (id,title) VALUES (5,6,7,'Child Post1-2-1');
INSERT INTO posts (id,title) VALUES (6,10,13,'Child Post2');
INSERT INTO posts (id,title) VALUES (7,11,12,'Child Post');
INSERT INTO posts (id,title) VALUES (8,14,15,'Child Post3');

现在你可以获取它们.

但是,在实际做某事之前,你要描述你想要做什么.那就是：你定义一个合同：

public interface IPostsRepository
{
    Post GetPost();
}

现在你开始做了.在这种情况下,我们将使用普通的ADO.NET来查询数据库并构建Post对象.我们将使用带有堆栈的迭代算法来构建树,但您也可以使用递归算法：

public class PostsRepositoryAdoNet: IPostsRepository
{
    private readonly string _connectionString;
    public PostsRepositoryAdoNet(string connectionString)
    {
        _connectionString = connectionString;
    }

    private class Scalar
    {
        public int Depth { get; set; }
        public Post Post { get; set; }
    }

    public Post GetPost()
    {
        using (var conn = new SqlConnection(_connectionString))
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText =
            @"
                SELECT p.id,p.title,(COUNT(parent.title) - 1) AS depth
                FROM posts AS p,posts AS parent
                WHERE p.left BETWEEN parent.left AND parent.right
                GROUP BY p.title
                ORDER BY p.left;
            ";
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                {
                    return null;
                }

                var nodes = new Stack<Post>();
                var scalar = FromDataReader(reader);
                var rootNode = scalar.Post;
                int currentDepth = 0;
                var currentNode = rootNode;
                while (reader.Read())
                {
                    var depth = reader.GetInt32(reader.GetOrdinal("depth"));
                    if (depth > currentDepth)
                    {
                        nodes.Push(currentNode);
                        currentDepth = depth;
                    }
                    else if (depth < currentDepth)
                    {
                        while (depth < currentDepth)
                        {
                            --currentDepth;
                            nodes.Pop();
                        }
                    }
                    scalar = FromDataReader(reader);
                    currentNode = scalar.Post;
                    var p = nodes.Peek();
                    if (p.ChildPosts == null)
                    {
                        p.ChildPosts = new List<Post>();
                    }
                    p.ChildPosts.Add(currentNode);
                }
                nodes.Clear();
                return rootNode;
            }
        }
    }

    private Scalar FromDataReader(DbDataReader reader)
    {
        return new Scalar
        {
            Depth = reader.GetInt32(reader.GetOrdinal("depth")),Post = new Post
            {
                PostId = reader.GetInt32(reader.GetOrdinal("id")),PostTitle = reader.GetString(reader.GetOrdinal("title"))
            }
        };
    }
}

现在我们有了这个存储库,我们可以将这些部分组合在一起

public class HomeController : Controller
{
    private readonly IPostsRepository _repository;
    public HomeController(IPostsRepository repository)
    {
        _repository = repository;
    }

    public ActionResult Index()
    {
        var post = _repository.GetPost();
        return View(post);
    }
}

最后一部分是配置您最喜欢的依赖注入框架,以注入所需的存储库实现,因为到目前为止我们只有一个是PostsRepositoryAdoNet.如果明天你决定切换到ORM,你所要做的就是编写实现IPostsRepository接口的相应存储库.