NHibernate - Get parent with paged child collection

Question

I'm currently working on writing a very basic online forum, and I want to retrieve a thread with a child collection of paged posts. So my mappings are:

<class name="Thread" table="ForumThreads">
    <id name="Id">
        <generator class="identity"></generator>
    </id>

    <property name="Title"></property>

    <bag name="Posts">
        <key column="ThreadID"></key>
        <one-to-many class="Post"/>
    </bag>
</class>

<class name="Post" table="ForumPosts">
    <id name="Id">
        <generator class="identity"></generator>
    </id>

    <property name="Content"></property>

    <many-to-one name="Thread"
                 class="Thread"
                 column="ThreadID">
    </many-to-one>
</class>

And I want to do something like this:

public class Thread
{
    public virtual int Id { get; set; }
    public virtual string Title { get; set; }
    public virtual IEnumerable<Post> Posts { get; set; }
}

public class Post
{
    public virtual int Id { get; set; }
    public virtual Thread Thread { get; set; }
    public virtual string Content { get; set; }
}

public Thread GetThread(int threadId, int page, int pageSize, out int count)
{
    var session = SessionFactory.CurrentSession;

    // Query to get the thread with a child collection of paged posts. 

    return thread;
}

Is it possible to do this with one query, or am I going to have to split it in to two?

Thanks

There are some resources on these SO questions: http://stackoverflow.com/q/2430863/654134 http://stackoverflow.com/q/4284438/654134 — Henrique Baggio, Apr 03 '12 at 14:33

score 3 · Accepted Answer · answered Apr 03 '12 at 14:35

3

var threadId = ...
session.QueryOver<Thread>
  .Where(thread => thread.Id == threadId)
  .Fetch(thread => thread.Posts).Eager
  .Take(pageSize)
  .Skip(page)
  .TransformUsing(Transformers.DistinctRootEntity)
  .List<Thread>();

answered Apr 03 '12 at 14:35

Anton

1,583
12
17

score 0 · Answer 2 · answered Apr 04 '12 at 12:41

If you are thinking of doing db level paging, then the approach would be something like this,

You have to use the collection to support lazy loading with extra mode

You may need to use a filter to load the collection, (but you cannot return thread as the result as if you try to access the collection it will load all posts)

public List<Post> GetThreadPosts(int threadId, int page, int pageSize, out int count)
{
    var session = SessionFactory.CurrentSession;
     Thread thread = (Thread )session.Get(typeof(Thread ), threadId);
     var posts = session.CreateFilter(thread .Posts, "").SetFirstResult((page - 1)  * pageSize).SetMaxResults(pageSize).List();

    return posts ;
}

NHibernate - Get parent with paged child collection

2 Answers2