Derik Whittaker

Syndication

News


NHibernate & SetProjections/SetProjections w/ List Gotcha

Today I was needing to create a query which looked a bit like this:

SELECT   max(this_.SEQUENCE) as y0_,
         this_.ADMISSION_ID  as y1_
FROM     PT_Status this_
GROUP BY this_.ADMISSION_ID

As you can see, I was trying to do a group by one column and a Max() on another column (legacy DB’s gotta love them).

Since I only wanted to project a few properties I decided to use project and provide a list of columns I wanted, my code looked like:

var detachedCriteria = CriteriaBuilder.CreateCritieraFor()
	.SetProjection( 
		Projections.ProjectionList()                    
        		.Add( Projections.Property( "Sequence" ) ) 
	                .Add( Projections.Property( "AdmissionID" ) )                    
			)                
	.SetProjection( Projections.Max( "Sequence" ) )
	.SetProjection( Projections.GroupProperty( "AdmissionID" ) )
        .SetResultTransformer( Transformers.AliasToBean( typeof( Status ) ) );

When I would run this code it would not give me the sql I wanted, and everything looked ok.   Then through trial and error I realized my issue.  The code below is the code which works

var detachedCriteria = CriteriaBuilder.CreateCritieraFor()
	.SetProjection( 
		Projections.ProjectionList()                    
        		.Add( Projections.Max( "Sequence" ) ) 
	                .Add( Projections.GroupProperty( "AdmissionID" ) )                    
			)                
        .SetResultTransformer( Transformers.AliasToBean( typeof( Status ) ) );

If you look at the first example i am both setting up my column projections and my group/max projects, but they are on the same column (not needed) and my group/max projections is OUTSIDE my first projection list.  Turns out that I only needed my first projection list as it would setup the group/max as well as my return columns.

Hope this helps.

Till next time,


Posted 05-27-2009 10:21 AM by Derik Whittaker
Filed under: ,

[Advertisement]

Comments

Victor Kornov wrote re: NHibernate & SetProjects/SetProjects w/ List Gotcha
on 05-27-2009 3:47 PM

The way it actually works seem more "logical" & "right" for me. I guess, I'm lucky with this one. Hate not intuitive APIs...

Hudson Akridge wrote re: NHibernate & SetProjects/SetProjects w/ List Gotcha
on 05-28-2009 10:26 AM

Did you mean SetProjection instead of SetProject in the title man?

Glad everything got worked out though, that's a heck of a legacy system you're hooking up :)

Derik Whittaker wrote re: NHibernate & SetProjects/SetProjects w/ List Gotcha
on 05-28-2009 10:43 AM

@Hudson,

Doh, you are right, title will be updated.

better shoesclothes wrote re: NHibernate & SetProjections/SetProjections w/ List Gotcha
on 05-30-2009 9:53 AM

good top

Tuna Toksoz wrote re: NHibernate & SetProjections/SetProjections w/ List Gotcha
on 06-03-2009 5:31 AM

Trunk has a more natural syntax for projections:

var detachedCriteria = CriteriaBuilder.CreateCritieraFor()  

.SetProjection(Projections.Max( "Sequence" ),  

Projections.GroupProperty( "AdmissionID" ))

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)