如何使用 Castle ActiveRecord 预加载带有父子自引用的记录？

发布于 2024-07-08 20:49:18 字数 940 浏览 5 评论 0原文

我的 SQL 表如下所示：

CREATE TABLE Page (
    Id int primary key,
    ParentId int, -- refers to Page.Id
    Title varchar(255),
    Content ntext
)

并映射到我的 ActiveRecord 模型中的以下类：

[ActiveRecord]
public class Page {

    [PrimaryKey]
    public int Id { get; set; }

    [BelongsTo("Parent")]
    public virtual Page Parent { get; set; }

    [Property]
    public string Title { get; set; }

    [Property]
    public string Content { get; set; }

    [HasMany(typeof(Page), "Parent", "Page")]
    public IList<Page> Children { get; set; }
}

我正在使用 ActiveRecord 使用以下代码检索树根：

var rootPages = new SimpleQuery<Page>(@"from Page p where p.Parent is null");
return(rootPages.Execute());

这为我提供了正确的对象图，但 SQL Profiler 跟踪显示子页面由树中每个非叶节点的单独查询加载。

如何让 ActiveRecord 预先加载全部内容 ("SELECT * FROM Page") 然后对内存中的对象进行排序以获得所需的父子关系？

原文

My SQL table looks like this:

CREATE TABLE Page (
    Id int primary key,
    ParentId int, -- refers to Page.Id
    Title varchar(255),
    Content ntext
)

and maps to the following class in my ActiveRecord model:

[ActiveRecord]
public class Page {

    [PrimaryKey]
    public int Id { get; set; }

    [BelongsTo("Parent")]
    public virtual Page Parent { get; set; }

    [Property]
    public string Title { get; set; }

    [Property]
    public string Content { get; set; }

    [HasMany(typeof(Page), "Parent", "Page")]
    public IList<Page> Children { get; set; }
}

I'm using ActiveRecord to retrieve the tree roots using the following code:

var rootPages = new SimpleQuery<Page>(@"from Page p where p.Parent is null");
return(rootPages.Execute());

This gives me the correct object graph, but a SQL Profiler trace shows that child pages are being loaded by a separate query for every non-leaf node in the tree.

How can I get ActiveRecord to load the whole lot up front ("SELECT * FROM Page") and then sort the in-memory objects to give me the required parent-child relationships?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜扑 2024-07-15 20:49:18

最简单的方法是获取整个表，然后过滤结果。如果您使用 linq，这非常简单。

var AllPages = ActiveRecordMediator<Page>.FindAll();
var rootPages = AllPages.Where(p => p.Parent == null);

The easiest way to do this is to fetch the entire table, then filter the result. This is pretty easy, if you are using linq.

var AllPages = ActiveRecordMediator<Page>.FindAll();
var rootPages = AllPages.Where(p => p.Parent == null);

回复收藏 0 原文

满地尘埃落定 2024-07-15 20:49:18

试试这个：

var rootPages = new SimpleQuery<Page>(@"from Page p left join fetch p.Children where p.Parent is null");
return(rootPages.Execute());

这将导致结果集中每个 Page 的 Children 集合在初始查询期间被填充，这应该将整体查询负载减少到单个查询。

Try this:

var rootPages = new SimpleQuery<Page>(@"from Page p left join fetch p.Children where p.Parent is null");
return(rootPages.Execute());

This will cause the Children collection of each Page in the result set to be populated out during the initial query, which should reduce your overall query load to a single query.

回复收藏 0 原文

~没有更多了~