Derik Whittaker

Syndication

News


Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Nested CreateCriteria ‘gotcha’ when using NHibernate

In an attempt to provide full disclosure I am a complete NOOBIE when it comes to NHibernate.  In fact I have only been using it for a few weeks or so. What this means is take everything I say in this post with a grain of salt cause there may be a better, easier way.

When exploring more of the really cool details on how to utilize NHibernate I ran into what I would call a ‘gotcha’ when using nested CreateCriteria commands.

In order to better explain, it would be a good idea to give you full background.  I was trying to replace a Linq-2-Sql query in which I was doing a lookup on a field in a child table.  The table layout below will better illustrate.

image

I was trying to create a query which would attempt to filter on the file name on the download item AND the episode number (these are the values given to me via iTunes).  My first attempt at it looked something like this

var result = dataSession.CreateCriteria( typeof( EpisodeITunesItem ) )
    .SetFetchMode( "Episode", FetchMode.Eager )
    .SetFetchMode( "ServerLocation", FetchMode.Eager )
    .Add( Expression.Eq( "FileName", fileName ) )
    .CreateCriteria( "Episode" ) // this creates the 2nd ICriteria
    .Add( Expression.Eq( "EpisodeNumber", episodeNumber ) )
    .UniqueResult();

What really shocked me when I looked at the output via NHProfiler, I did not expect to see that 2 queries had run.  In an attempt to figure out what was wrong I headed over to the NHibernate docs site, in particular the page about QueryCritiera.  After doing a bit of digging I realized my issue.  When you use .CreateCriteria multiple times it creates multiple instances of ICriteria (which as far as I can tell means multiple SQL statements).  As I knew I did not want to have multiple statements ran, I set out to tweak my statement to resolve the issue. 

Below is the code I came up with which only creates one sql statement.

        
var result = dataSession.CreateCriteria( typeof( EpisodeITunesItem ) )
	.CreateAlias( "Episode", "e" )
        .SetFetchMode( "Episode", FetchMode.Eager )
        .SetFetchMode( "ServerLocation", FetchMode.Eager )                
        .Add( Expression.Eq( "FileName", fileName ) )
        .Add( Expression.Eq( "e.EpisodeNumber", episodeNumber ) )
        .UniqueResult();

As you can see the differences between the 2 statements is subtle, but huge.  The thing to look for is how I replaced .CreateCriteira with .CreateAlias and use the new alias in my where (.add) part of the statement.

Till next time,


Posted 02-03-2009 6:40 AM by Derik Whittaker
Filed under:

[Advertisement]

Comments

Geir-Tore Lindsve wrote re: Nested CreateCriteria ‘gotcha’ when using NHibernate
on 02-03-2009 9:08 AM

Thanks for this post!

We have one project at work where we use NHibernate and we definitely have some queries where we use nested CreateCriterias. Will definitely look into this!

Dew Drop - February 3, 2009 | Alvin Ashcraft's Morning Dew wrote Dew Drop - February 3, 2009 | Alvin Ashcraft's Morning Dew
on 02-03-2009 9:54 AM

Pingback from  Dew Drop - February 3, 2009 | Alvin Ashcraft's Morning Dew

Why Nesting Tables | Roundsofas wrote Why Nesting Tables | Roundsofas
on 02-03-2009 12:00 PM

Pingback from  Why Nesting Tables | Roundsofas

DotNetShoutout wrote Nested CreateCriteria ‘gotcha’ when using NHibernate - Derik Whittaker
on 02-03-2009 3:26 PM

Thank you for submitting this cool story - Trackback from DotNetShoutout

Reflective Perspective - Chris Alcock » The Morning Brew #279 wrote Reflective Perspective - Chris Alcock » The Morning Brew #279
on 02-04-2009 3:38 AM

Pingback from  Reflective Perspective - Chris Alcock  » The Morning Brew #279

Mark Woodhall wrote re: Nested CreateCriteria ‘gotcha’ when using NHibernate
on 02-04-2009 6:51 AM

Hi Derik,

I am also a relatively new to nHibernate and have nested criteria's as per your first example.

However, in my situation I didnt end up with multiple SQL statements.

Is CreateAlias the recommened best practice for this type of action?

Chris Marisic wrote re: Nested CreateCriteria ‘gotcha’ when using NHibernate
on 02-04-2009 9:47 AM

Also be careful using .UniqueResult without using SetMaxResults(1) as it will throw an exception if there are multiple results instead of just picking the top result.

Another recommendation is use .UniqueResult<TYPE>() as it will return you a typed object so you don't need to do deal with casting since I think the actual type of your result variable would be object not TYPE.

Derik Whittaker wrote re: Nested CreateCriteria ‘gotcha’ when using NHibernate
on 02-04-2009 9:54 AM

@Chris,

actually i did use the UniqueResult<> but the <> got removed when I put in the code snippet.

Gary Hall wrote re: Nested CreateCriteria ‘gotcha’ when using NHibernate
on 05-15-2009 10:29 AM

In that case, why use a var?

Derik Whittaker wrote re: Nested CreateCriteria ‘gotcha’ when using NHibernate
on 05-15-2009 10:40 AM

@Gary,

I am one of those guys who loves var and i use it in most cases.

Remember var != dynamic or late typing

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)