NHibernate Query Woes

May 21st 2008, 1:17 pm in NHibernate, Personal.

I’m banging my head trying to solve a particular type of querying problem. I think I can simplify it like this:

Categories have many Posts, Posts have many Comments

I’d like to search for Comments with a particular author, which is easy enough, but I’d like my result set to be made up of Blogs which contain Posts which contain Comments with author = ‘ramsay’.

The problem I’m having is that if I have 50 Comments then it’ll return 50 duplicate Blogs to contain those comments. Argh! I can add groupings (i.e. group by Blog), but that means it’ll only pull back the things I group by and not the full Blog objects.

Responses to “NHibernate Query Woes”

  1. a simple hql query should give you what you want:

    var query = session.CreateQuery(”from Post p left join p.Comments c where c.Author like :author”)
    .SetParameter(”author”, “ramsay”);

    var posts = query.List();

    is this not what you want? if you want to load just comments (no post or blog) then you can lazy load those associations easily.

    var query = session.CreateQuery(”from Comment c where c.Author like :author”).SetParameter(”author”, “ramsay”);
    var comments = query.List(); // comment -> posts is lazy loaded so theres a single select statement generated here.

    Ben Scheirman

  2. I had a similar problem and resolved it by using the distinct key word. Therefore I guess you could use the distinct keyword on the blog object as that is the only object you are interested in as its children will get loaded lazily.

    Good Luck.

    Glyn Darkin

  3. Hmm, thanks for those tips guys. I’ll have a look now and see if that’ll do what I’m after. I suspect this is more a problem with my NHibernate knowledge than NH itself anyway…

    Colin Ramsay

  4. is this not a job for DistinctRootEntityResultTransformer?

    Matt Hinze

Post a Reply