Querying on Child Count With NHibernate

This is a recent question raised in NHibernate Users Group. The user wanted to realize the following query with Criteria api.

var result = db.Person.Where(x => x.Pets.Count > 0 && x.Alive).OrderBy(x => x.Name);

This is not a simple query, but it has a solution

  
DetachedCriteria crit = DetachedCriteria.For(typeof (Person), "p2")
    .CreateCriteria("p2.Pets","Pets")
    .Add(Restrictions.EqProperty("p.Id", "p2.Id"))
    .SetProjection(Projections.Count("Pets.Id"));

ICriteria c = s.CreateCriteria(typeof (Person), "p")
    .Add(Restrictions.Gt(Projections.SubQuery(crit), 0))
    .Add(Restrictions.Eq("p.Alive",true))
    .AddOrder(Order.Asc("p.Name"));

What we had to do is to create a DetachedCriteria and on that execute CreateCriteria so that we can do querying on our collection.

The other way is simpler, but requires you to use HQL (below query is provided by Fabio Maulo)

session.CreateQuery("from Person p where  size(p.Pets) > 0 and p.Visible = true order by p.Name")

or

session.CreateQuery("from Person p where  p.Pets.size > 0 and p.Visible = true order by p.Name")

I hope this helps.

kick it on DotNetKicks.com

Shout it


Posted 07-10-2009 2:23 AM by Tuna Toksoz
Filed under:

[Advertisement]

Comments

DotNetShoutout wrote Querying on Child Count With NHibernate - Tuna Toksoz - Devlicio.us
on 07-10-2009 7:18 AM

Thank you for submitting this cool story - Trackback from DotNetShoutout

DotNetKicks.com wrote Querying on Child Count With NHibernate
on 07-10-2009 8:20 AM

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Hudson Akridge wrote re: Querying on Child Count With NHibernate
on 07-10-2009 10:44 AM

Glad to know that there's not an easier way to do it. That's pretty much how we're doing it currently in our SearchBuilder which is a client friendly interface for building criteria searches based on their custom business domain.

Eventually, we'll be swapping that to be an HQL backed system, instead of Criteria, but until then...detached criterias!! :)

ANaimi wrote re: Querying on Child Count With NHibernate
on 07-10-2009 9:20 PM

Hello Toksoz,

I seriously don't see the advantage of using NHibernate rather than other ORMs (despite the big advantage of supporting several RDBMs.)

I spent the first half of today and the whole day yesterday trying to replace LINQ2SQL of my current project to NHibernate. And let me say: It. Was. Hell.

The first method you presented seems way too complicated (relative to the LINQ method), and the second is not semantically safe.

I will be using NHibernate once LINQ support is mature enough (and from my experience in the last hours, it is not nearly usable).

Thanks.

Tuna Toksoz wrote re: Querying on Child Count With NHibernate
on 07-11-2009 5:08 AM

If you're only considering the querying aspect of an ORM, you're free to use L2S. I don't like my entities being inherited from a base class, and i don't like my db driving my domain. Needless to say, I don't like the xml it creates :)

There are many other factors in favor of NH, integrated cache, contrib, many many extension points (take a look around, you'll see many).

One  last reason is I am a committer, and i have emotional ties to it :)

Tuna Toksoz wrote re: Querying on Child Count With NHibernate
on 07-11-2009 5:11 AM

@ANaimi

You can call my by my first name, last name is too formal

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)