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?