Clever Geek Handbook

How can I preload records with native parent-child links using Castle ActiveRecord?

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 displays 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 am using ActiveRecord to extract the roots of a tree 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 the SQL Profiler trace shows the child pages are loaded with a separate query for each non-leaf node in the tree.

How can I get an ActiveRecord to load the whole batch ahead ("SELECT * FROM Page")

and then sort the objects in memory to give me the parent-child relationship I want?

0


source to share


2 answers


The easiest way to do this is to fetch the entire table and then filter the result. It's pretty easy if you are using linq.



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


source


Try the following:

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 during the initial query, which should reduce the overall query load per query.

-1


source






More articles:


All Articles