Ayende @ Rahien

Refunds available at head office

NHibernate Queries - Examples

Today was the first day of my NHibernate course, and I think that it might be good to point out a few of the samples that we worked with. Those are pretty basic NHibernate queries, but they are probably going to be useful for beginners.

Let us take my usual Blog model, and see what kind of queries (and results) we can come up with:

image

Let us find a blog by its identifier:

var blog = s.Get<Blog>(1);

Which results in:

image

We can also try:

var blog = s.Load<Blog>(1);

Which would result in… absolutely no SQL queries. You can look at a more deep discussion of that here.

Now, let us try to search by a property:

var blogs = s.CreateCriteria<Blog>()
.Add(Restrictions.Eq("Title", "Ayende @ Rahien"))
.List<Blog>();

Which results in:

image

If we try to make the same with HQL, it would look:

var blogs = s.CreateQuery("from Blog b where b.Title = :title")
.SetParameter("title","Ayende @ Rahien")
.List<Blog>();

Which results in slight different SQL than using the criteria:

image 

What about trying a more complex conditional? Let us try to see comparing two properties:

var blogs = s.CreateCriteria<Blog>()
.Add(Restrictions.Eq("Title","Ayende @ Rahien"))
.Add(Restrictions.Eq("Subtitle", "Send me a patch for that"))
.List<Blog>();

Which results in:

image

Let us do that again, but using two properties using an OR:

var blogs = s.CreateCriteria<Blog>()
.Add(Restrictions.Disjunction()
.Add(Restrictions.Eq("Title", "Ayende @ Rahien"))
.Add(Restrictions.Eq("Subtitle", "Send me a patch for that")))
.List<Blog>();

Which would result in:

image

We can also execute the same SQL using the following syntax:

var blogs = s.CreateCriteria<Blog>()
.Add(
Restrictions.Eq("Title", "Ayende @ Rahien") ||
Restrictions.Eq("Subtitle", "Send me a patch for that")
)
.List<Blog>();

Doing the same using HQL would be:

var blogs = s.CreateQuery("from Blog b where b.Title = :title and b.Subtitle = :subtitle")
.SetParameter("title","Ayende @ Rahien")
.SetParameter("subtitle", "Send me a patch for that")
.List<Blog>();

Which results in:

image

And changing that to an OR is pretty self explanatory :-)

var blogs = s.CreateQuery("from Blog b where b.Title = :title or b.Subtitle = :subtitle")
.SetParameter("title","Ayende @ Rahien")
.SetParameter("subtitle", "Send me a patch for that")
.List<Blog>();

Giving us:

image

Let us try something a bit more complex, finding a blog by a post title:

var blogs = s.CreateCriteria<Blog>()
.CreateCriteria("Posts")
.Add(Restrictions.Eq("Title","NHibernate Rocks"))
.List<Blog>();

That gives us:

image

You will note that we force a load of the Posts collection. We can try something else, though:

var blogs = s.CreateCriteria<Blog>()
.Add(Subqueries.PropertyIn("id",
DetachedCriteria.For<Post>()
.Add(Restrictions.Eq("Title","NHibernate Rocks"))
.SetProjection(Projections.Property("Blog.id"))
))
.List<Blog>();

Which would give us the same result, but without loading the Posts collection:

image

This is a pretty common example of changing the way that we compute complex conditionals when we want to avoid wide result sets.

Let us do the same with HQL:

var blogs = s.CreateQuery("from Blog b join b.Posts p where p.Title = :title")
.SetParameter("title", "NHibernate Rocks")
.List<Blog>();

Which would result:

image

We have the same issue as with the first Criteria API, and we can resolve it in the same way:

var blogs = s.CreateQuery("from Blog b where b.id in (from Post p where p.Title = :title)")
.SetParameter("title", "NHibernate Rocks")
.List<Blog>();

And the same result as in the Criteria API show us:

image

And the final test, let us try to find a blog that has a post posted by a specific user:

var blogs = s.CreateCriteria<Blog>()
.Add(Subqueries.PropertyIn("id",
DetachedCriteria.For<Post>()
.SetProjection(Projections.Property("Blog.id"))
.CreateCriteria("User")
.Add(Restrictions.Eq("Username","Ayende"))
))
.List<Blog>();

And this give us:

image

The same thing with HQL will give us:

var blogs = s.CreateQuery("from Blog b where b.id in (from Post p where p.User.Username = :user)")
.SetParameter("user","Ayende")
.List<Blog>();

And that results:

image

Comments

Jimmy Chan
05/19/2009 10:44 AM by
Jimmy Chan

Ayende,

Great tips. I hope there will be continued. Thanks

Erik
05/19/2009 11:43 AM by
Erik

Thanks for the examples. I know this might be an old discussion but which do You recommend: HQL or ICriteria? Are there any differences in performance and caching?

huey
05/19/2009 12:06 PM by
huey

That explains detached criteria pretty well. Also, for some reason I didn't think you could do "from Post p where p.User.Username = :user" and had to do "from Post p join p.User u where u.Username = :user". Learn something new everyday. This is a good NH series :)

Jernej
05/19/2009 12:15 PM by
Jernej

You didn't mention NHibernate.LambdaExpressions. I know they're not in the current version, but still... :)

Ayende Rahien
05/19/2009 01:25 PM by
Ayende Rahien

Jernej,

That is because the principle is the same.

I am going to introduce them at some point

Ayende Rahien
05/19/2009 01:26 PM by
Ayende Rahien

Erik,

In general, no, there aren't any perf differences. And certainly no caching differences.

I'll put out a more detailed post on the 24th on the topic

Ian Nelson
05/19/2009 01:51 PM by
Ian Nelson

It occurs to me that NHProf must make it much easier to teach NHibernate. Was that partly a source of inspiration for the project, or a happy side effect?

Ayende Rahien
05/19/2009 01:57 PM by
Ayende Rahien

Ian,

It makes a tremendous difference in the teaching experience, I'll talk about it later, though

Petar Petrov
05/19/2009 03:41 PM by
Petar Petrov

Ayende, is it possible to provide a solution(for download) of your Blog model as in your post ( ayende.com/.../...ce.net-nhibernate-workshops.aspx). It will help other developers to setup and experiment with it. You have provided the class diagram but it will take me(and others) some time to create the model and the mappings.

Thanks.

Anyway great series keep going.

josh
05/19/2009 04:27 PM by
josh

Interesting how you can affect the query with minor changes in the code. It seems so simple to me.. why don't more companies use NH. /why won't my client use NH!

Adam D.
05/19/2009 09:04 PM by
Adam D.

For refactoring problems and lack of intellisense, are you going to be introducing NHQG at any point?

Ayende Rahien
05/19/2009 09:21 PM by
Ayende Rahien

Adam,

NHQG is deprecated, so no.

Jimmy Chan
05/20/2009 03:56 AM by
Jimmy Chan

Ayende,

I have same question with Erik above, and wait for your comment.

Erik said: Thanks for the examples. I know this might be an old discussion but which do You recommend: HQL or ICriteria? Are there any differences in performance and caching?

Thanks.

vlad
05/20/2009 06:36 AM by
vlad

Hi!

Great post, as usual, but it look like a bug in 2 queries: where blog.id in (select post.id ....)

Ayende Rahien
05/20/2009 07:44 AM by
Ayende Rahien

@Ayende & @Eric,

I have a post on that, and that would show up in the queue on the 27th or 29th

@Vlad,

You are correct, it should be post.Blog.id

Comments have been closed on this topic.