I have posted the next installment in the series which actually diverges slightly from the previously-planned curriculum. The plan for this session was to cover INSERTs, UPDATEs, and DELETEs but looking back at the topics for Session 02 I realized that there were some aspects of querying that I had wanted to cover but overlooked as the clock ran out (I have a self-imposed time-limit for these of no more than about 90 minutes of total length so that they remain somewhat easy to sit through without squirming 
).
Enter: Session 02a
To address this oversight, I this next installment is called Session 02a since its topics are really more in-line with the content of Session 02 rather than a completely new set of topics. Topics focused on in this installment:
- Order By clauses
- Group By clauses
- Returning ‘partial objects’ from the database (retrieving only what you need)
The good news is that this session only runs about 60 minutes since its not a complete stand-alone session per-se. This means that there are only two parts of the ZIP file that you need to download from the www.4shared.com drop.io site so 1/3 less annoyance in the downloading process 
.
As always, comments and feedback are appreciated.
Download Links for Session 02a
- Summer of NHibernate Session 02a Screencast Part 1 of 2
- Summer of NHibernate Session 02a Screencast Part 2 of 2
- Summer of NHibernate Session 02a Code Sample
Note that as with all of these screencasts, to successfully view them on your computer you will need to download and install the TechSmith Camtasia Codec. +Screencast+is+Available&referrer=){kind=small}
Update: As mentioned here, all content has been moved to the Summer of NHibernate Web Site.
Where is the example of using the Criteria API to get a strongly typed collection from a GROUP BY type SQL query? I have been unable to find an example of this and the NHibernate documentation is extremely vague.
Also, your test CanGetCountsOfCustomerFirstNames() is incorrect. It only checks that the results of the query are in the expectedCounts collection, it does not check to make sure that all of the items in the Dictionary are actually returned in the results. If your query returned “Steve”, 3, the test would still pass, even though Mike, Peter, Joe and Yuliana are all missing but expected.
@Ted
I don’t know how to respond directly to you, or if this is even relevant for you anymore, but just in case you or anyone else comes through here looking for this answer, I was able to track it down.
http://stackoverflow.com/questions/718887/how-do-i-do-a-custom-projection-with-the-criteria-api-in-nhibernate/751657#751657
That link shows you how to AliasToBean transformer to put your results into a new object. In Steve’s example, it would look something like this:
(Criteria setup…)
.Add(Projections.Property(c.Firstname), “firstname”)
.Add(Projections.Property(count(C.Firstname)), “count”)
.SetResultTransformer(Transformers.AliasToBean(typeof(CustomerFirstnameCounter)));
The other catch here is that the CustomerFirstnameCounter object needs an empty parameter constructor, rather the parmaterized one the HQL example uses.
When using the sample code using NHibernate 2.1.0 or 2.1.1, HQL’s with the “select from Customer c” formats fail throwing the following exception.
“NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: Exception of type ‘Antlr.Runtime.NoViableAltException’ was thrown”
The HQL’s should be changed to “from Customer c” or “select c from Customer c” formats
See the related SO question
http://stackoverflow.com/questions/1228146/nhibernate-querysyntaxexception
@Leyu Sisay:
Thanks for the updates — that’s sure to be one of the ‘gotchas’ from using a freeform query language (HQL, SQL, or whatever): the syntax can change right out from under you on a whim 🙂
The ‘shorthand’ HQL form that I was using in the screencasts under 1.2 was deprecated in 2.0 and eliminated in 2.1.x as the default query parser was switched out to be the more strict option.
Thanks again for offering the update!
-Steve B.
Brilliant job with these screen casts Steve, they’re superb!
The code below shows how to use the Criteria Api to get the non-persisted strongly typed collection using group by. My naming conventions are slightly different to Steve’s.
return session.CreateCriteria(typeof (Customer))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“FirstName”), “FirstName”)
.Add(Projections.Count(“FirstName”), “Count”)
.Add(Projections.GroupProperty(“FirstName”)))
.SetResultTransformer(Transformers.AliasToBean(typeof(CustomerFirstNameCounter)))
.List< CustomerFirstNameCounter >();
Posted some additional info here.
http://thelongwayback.wordpress.com/2009/11/27/nhibernate-criteria-api-group-by-example-non-persisted-class/
Steve,
I’ve watched sessions 1 and 2 and I have a question/comment regarding your test strategy. In multiple places you run some query, such as getting all customers by the first name of “steve”, and then you assert that the number of records matches some known constant. For instance, you inspect the DB, see that there are 3 Steves, and you assert that your DAL call yields 3 results.
Obviously, this is extremely brittle. What happens if you eventually test a Create method that creates a new “steve”? Or if you delete a Steve during a delete test? Are you ignoring those cases for the sake of simplicity?
The pattern I normally use is this:
1) In my setup/teardown, I start and rollback a DB transaction;
2) My tests always create the data they expect, usually via [Setup], unless my fixture setup restores the DB to a known and stable state;
3) To test something like GetByFirstName(“steve”), I would create TWO customers, Steve and Fred. I can then be confident that my DAL is including only what it should, nothing more and nothing less.
Is this what you would do in production, or do you think this overly complicates the tests?
@Seth:
Great question; those are (obviously) very real concerns. Later on in subsequent sessions you will begin to see how I leverage a testing support framework called NDbUnit to permit my tests to properly manage state, ensure that expected records are present in the DB before the test, and cleans up after the tests.
I don’t generally like using DB transaction rollbacks as my method of reverting DB state after tests run b/c there are so many things that can go wrong w that strategy (especially if/when part of the DB methods you might be testing makes use of transactions internally and would need to be aware of outer transaction scope, etc. to properly enlist in the right transaction, etc.).
NDbUnit places this responsibility in a tooling framework that doesn’t impact the running tests in any way. You can see evidence of this approach in later sessions in the series, read more about NDbUnit at http://ndbunit.googlecode.com , and read more about the availability of some of the tooling that makes working with NDbUnit easier in this post: http://unhandled-exceptions.com/blog/index.php/2009/03/16/proteus-unit-test-utility-and-domain-foundation-code-goes-oss/
Hope this helps, and thanks for the comments~!
-Steve B.
Where is the example of using the Criteria API to get a strongly typed collection from a GROUP BY type SQL query? I have been unable to find an example of this and the NHibernate documentation is extremely vague.
I have no Projections library. Did I miss something during this screencast?
I tried to add using NHibernate.Expressions.Projections directive but it seems that there is no Expressions either.
@Adam:
What version of NH are you using? Are you using NH 1.2 (as in the sample code downloads and the screencasts)?
In NH 2.1x and later the NHibernate.Expressions namespace was renamed to NHibernate.Restrictions to better correlate with the structure of the Java Hibernate codebase. See if that helps and let me know~!
-Steve B.