NHibernate: sometimes I want one item, other times I want them all

Question

I have a database schema that stores one "Page" with many "Revisions". Like a simple wiki.

90% of the time when I load a page, I am just interested in the latest revision. However, sometimes I want all revisions.

With NHibernate I can map the Page to the Revisions, and tell it to lazy-load. However, when I access the latest revision, it will load all other revisions - a big waste of I/O.

My page class currently resembles:

public class Page
{
    public Page()
    {
        Revisions = new HashedSet<Revision>();
    }

    public virtual ISet<Revision> Revisions { get; private set; }

    public virtual Revision LatestRevision
    {
        get { return Revisions.OrderByDescending(x => x.Revised).FirstOrDefault(); }
    }

    public virtual Revision Revise()
    {
        var revision = new Revision();
        // ...
        revision.Entry = this;
        revision.Revised = DateTime.UtcNow;
        Revisions.Add(revision);
        return revision;
    }
}

How would I model this such that the LatestRevision is automatically loaded when the Page is loaded, but the other revisions are lazy-loaded if, for instance, I attempted to iterate them?

I would particularly like a solution that works with LINQ to NHibernate, but using ICriteria (or even SQL if I have to) is good enough.

Answer 1

I'm tackling a similar problem as well.

What about mapping exactly as you have it there. The LatestRevision property could be mapped as a one-to-one mapping to the revisions table and the revisions would be as you've already got it. You would have to have a setter (probably make it private) and manage the relationship in the revise method.

One problem would be that the the LatestRevision would still be in the set of revisions.

I've also come across a post by Ayende which uses the formula attribute for the property, I've never used it but looks like it might fit the bill.

Answer 2

You could use a derived property in your mapping file (rather than performing the logic in the property). It might look something like this:

<property name="LatestRevision"
          forumla="select top r.f1, r.f2, r.etc from Revisions order by revised desc"
          type="Revision" />

For more info on this approach search for 'nhibernate derived properties'.

https://www.hibernate.org/hib%5Fdocs/nhibernate/1.2/reference/en/html%5Fsingle/

Answer 3

Add a LatestRevision column (maintain it) and map to that. It will save you a lot of headaches.

Answer 4

I ended up going with the solution from here:

http://stackoverflow.com/questions/1244995/partially-populate-child-collection-with-nhibernate

My page now has these properties:

public virtual Revision CurrentRevision 
{ 
    get
    {
        return _revision ?? Revisions.OrderByDescending(x => x.Revised).FirstOrDefault();
    }
    set { _revision = value; }
}

public virtual ISet<Revision> Revisions { get; private set; }

The loading code is:

public Page GetPage(string name)
{
    var entryHash = (Hashtable)_session.CreateCriteria<Page>("page")
        .Add(Restrictions.Eq("page.Name", name))
        .CreateCriteria("Revisions", "rev")
            .AddOrder(Order.Desc("rev.Revised"))
        .SetMaxResults(1)
        .SetResultTransformer(Transformers.AliasToEntityMap)
        .UniqueResult();
    var page = (Page)entryHash["page"];
    page.LatestRevision = (Revision)entryHash["rev"];
    return page;
}

NHProf shows this as the only query being executed now, which is perfect:

SELECT   top 1 this_.Id            as Id3_1_,
               this_.Name          as Name3_1_,
               this_.Title         as Title3_1_,
               rev1_.Id            as Id0_0_,
               rev1_.Body          as Body0_0_,
               rev1_.Revised       as Revised0_0_,
               ....
FROM     [Page] this_
         inner join [Revision] rev1_
           on this_.Id = rev1_.PageId
WHERE    this_.Name = 'foo' /* @p0 */
ORDER BY rev1_.Revised desc

Answer 5

What the problem to have LatestRevision property and corresponding column in Page table?

public class Page
{
    public Page()
    {
        Revisions = new HashedSet<Revision>();
    }

    public virtual ISet<Revision> Revisions { get; private set; } // lazy="true"

    public virtual Revision LatestRevision { get; private set; } // lazy="false"

    public virtual Revision Revise()
    {
        var revision = new Revision();
        // ...
        revision.Entry = this;
        revision.Revised = DateTime.UtcNow;
        Revisions.Add(revision);
        LatestRevision = revision; // <- there you have latest revision
        return revision;
    }
}