Creating a Select Count(*) with NHibernate using Criteria - Derik Whittaker - Devlicio.us

Derik Whittaker

The Lounge

Wicked Cool Jobs

Syndication

News

Blogs I Read

Current Readings

Archives

Derik Whittaker » Creating a Select Count(*) with NHibernate using Criteria

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: NHibernate, HowTo

[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);

.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.

Sponsors