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

Question

考虑以下构建论坛应用程序的要求

家长帖

- Child Post1

    - Child Post1-1
    - Child Post1-2
        - Child Post1-2-1
- Child Post2
    - Child Post

- Child Post3

表结构

tblPost
-

zip

ChildPostId

标题

发表内容

用户名

======================

我可以使用递归 CTE 检索此类数据。我不确定这是最好的方法。

问题

使用 SQL 检索此数据的最佳方法是什么？

有没有更好的方法来使用 ORM 加载这些数据？

如果我们走 SQL 路线，将这些数据加载到如下所示的类中的最佳方法是什么:

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

使用 Razor 语法来显示这种数据怎么样？

Answer 1

根据您的评论，您愿意接受有关改进当前数据库架构的建议，其中您基本上拥有 post_id和 child_post_id列来执行层次关系。

所以让我们继续:

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?

使用诸如 NHibernate 和 EF 之类的 ORM 可以做到这一点，但我会留到下一次。您可能会考虑将您的问题拆分为多个 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; }
}

和下面的 Controller (我显然已经对值进行了硬编码，但是在阅读了我在文章开头链接到的教程之后，您将能够使用单个 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, left, right, title) VALUES (2, 2, 9, 'Child Post1');
INSERT INTO posts (id, left, right, title) VALUES (3, 3, 4, 'Child Post1-1');
INSERT INTO posts (id, left, right, title) VALUES (4, 5, 8, 'Child Post1-2');
INSERT INTO posts (id, left, right, title) VALUES (5, 6, 7, 'Child Post1-2-1');
INSERT INTO posts (id, left, right, title) VALUES (6, 10, 13, 'Child Post2');
INSERT INTO posts (id, left, right, title) VALUES (7, 11, 12, 'Child Post');
INSERT INTO posts (id, left, right, title) VALUES (8, 14, 15, 'Child Post3');

现在你可以拿来它们了。

但像往常一样，在实际做某事之前，你会描述你想做的事情。那就是:你定义一个契约(Contract):

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);
    }
}

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