Derik Whittaker

Syndication

News


Creating a Select Count(*) with NHibernate using Criteria

As you are learning how to use NHibernate many of the ‘common’ things you are used to doing with standard SQL crop up.  One of them for me was to have the ability to perform a Count() query.  If I were using straight SQL my query may look something like this:

SELECT COUNT( FieldNameHere )
FROM SomeTableTable 
WHERE Field1Here = 51 
	AND Field2Here = 1 
	AND Field3Here = 1

Like many things in Nhibernate there are many ways to solve the same problem, but one way I found was to create the criteria query as below:

var count = (Int32) Session.CreateCriteria( typeof( SomeTableHere ) ) 
        .Add( Expression.Eq( "Field1Here", 1) ) 
        .Add( Expression.Eq( "Field2Here", (Int32) 2) ) 
        .Add( Expression.Eq( "Field3Here", 3) ) 
	.SetProjection( Projections.Count( "CountFieldHere" ) ).UniqueResult();

And the result from the above syntax would produce the following (the sql below was given to me via NHProfiler – kick ass tool EVERYONE should be using).

SELECT count(this_.OUTBOUND_STAGING_QUEUE_ID) as y0_ 
FROM   SomeTableHere this_ 
WHERE  this_.Field1Here= 51 /* @p0 */ 
       and this_.Field2Here = 1 /* @p1 */ 
       and this_.Field3Here = 1 /* @p2 */

As you can see creating a count query with NHibernate is pretty straight forward and will generate the exact SQL you would expect.

Till next time,


Posted 04-16-2009 7:18 AM by Derik Whittaker
Filed under: ,

[Advertisement]

Comments

Ben Scheirman wrote re: Creating a Select Count(*) with NHibernate using Criteria
on 04-16-2009 11:01 AM

IIRC I think there is a UniqueResult<T> that can eliminate that cast for you.  Not much of a gain, I know...

Derik Whittaker wrote re: Creating a Select Count(*) with NHibernate using Criteria
on 04-16-2009 11:08 AM

@Ben,

Yes you are correct.  I did not see that.

james.kovacs wrote re: Creating a Select Count(*) with NHibernate using Criteria
on 04-16-2009 2:52 PM

You can accomplish the same thing using HQL that looks a lot like the SQL. In my experience, I find the Criteria API most useful for composing queries dynamically - where you don't know the tables and/or columns you might be querying against. HQL is better suited to known queries with parameters. Just my 2 cents.

Eber Irigoyen wrote re: Creating a Select Count(*) with NHibernate using Criteria
on 04-17-2009 12:28 PM

Is there a better way of doing this?

This is ugly to say the least, hardcoded strings, but more than anything the code shows things that have nothing to do with the intent of the code

Derik Whittaker wrote re: Creating a Select Count(*) with NHibernate using Criteria
on 04-17-2009 1:28 PM

@Eber,

When working with NH strings are a reality (sadly).  But what do you mean by nothing to do with the intent?

If you wanted you could do as James suggest and use HQL

Derik Whittaker wrote Creating a nested ‘Or’ statement with NHibernate using the Criteria convention
on 04-21-2009 5:26 PM

As I pointed in a prior post ( here ) when trying to learn to harness the power of NHibernate sometimes

Scott wrote re: Creating a Select Count(*) with NHibernate using Criteria
on 05-08-2009 11:15 AM

@Derik and Eber,

Dealing with hard coded strings isn't exactly something you have to do.  Sure, it isn't out of the box with NHibernate (to my knowledge) but there is a solution that I came up with that has worked out well for me.

I created a Visual Studio 2008 T4 template that basically looks at my directory for my hbm.xml files, traverses them, and builds static classes containing string properties for my fields and associations.  This allows me to do the following...

ICriteria criteria = //get criteria

criteria.Add(Expression.Eq(MyEntityMetaData.Fields.Name, nameValue);

or

.CreateAlias(MyEntityMetaData.Associations.AnotherEntity, "aliasName")

I followed the convention of the class being named the same as my entity but tacking "MetaData" onto it.  Each class has 2 inner classes, "Fields" and "Associations".

Anyways, thats my approach and it really has added a lot of value to my codebase by allowing me to quickly identify issues when the underlying data model or mapping changes.

Trent wrote re: Creating a Select Count(*) with NHibernate using Criteria
on 08-07-2009 1:52 PM

Scott, that is  a pretty good idea.  Thinking on it a litte further, however, I think you could achieve the same thing with a LINQ provider for NH.  Which is released now with NH 2.1.

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)