Monday, 29 August 2011

Projecting a discriminator column into a DTO

To project the discriminator value into a DTO is quite a simple thing to achieve using QueryOver in NHibernate.

I have the following table that I am using the table-per-class-hierarchy strategy. The discriminator column is a string and contains values such as 'Movie', 'Book' etc.

For the following mapping code:-
public class ProductMapping : EntityMapping<Product> {
  public ProductMapping() {
    Property(x => x.Name);
    Property(x => x.Description);
    Discriminator(x => x.Column("ProductType"));
Note: I am not exposing the discriminator as a separate property, we really should let NHibernate handle this for us.

The DTO looks like this:-
public class ProductDto {
  public string ProductType { get; set; }
  public string Name { get; set; }
Now we need to select build the Query, notice we are using .GetType() to return the underlying discriminator value and project it into a string, for example:-
ProductDto dto = null;

return Session
  .SelectList(i => i  
    .Select(p => p.Name).WithAlias(() => dto.Name)  
    .Select(p => p.GetType()).WithAlias(() => dto.ProductType)
The above code returns:-

Is it possible to do the same using linq? The answer of course is yes, however we need to also add the .GetType().Name which gets the name of the current member:-
return (from product in Session.Query<product>()
  select new ProductDto{  
    Name = product.Name,
    ProductType = product.GetType().Name
You should be aware though that the Linq version will return the underlying class name and not the discriminator value. This means that your class name should be the same as your discriminator value. QueryOver however does NOT have the same limitations.

Wednesday, 24 August 2011

NHibernate and optimising with lazy='extra'

I came across a stack over flow post today that asked how you could retrieve the count of children from a parent without having to load the entire collection.

The answer is quite simple all you need is to add lazy='extra' on your mappings. Lets put this to the test, for the following domain:-
public class Movie : Entity {
  public virtual string Name { get; set; }
  public virtual string Director { get; set; }
  public virtual IList<Actor> ActorList { get; set; }

public class Actor : Entity {
  public virtual string Name { get; set; }
  public virtual string Role { get; set; }
The only mapping of importance is the mapping for the movie:-
public class MovieMapping : SubclassMapping<Movie> {
  public MovieMapping() {
    Property(x => x.Name, x => x.NotNullable(true));
    Property(x => x.Director, x => x.NotNullable(true));
    Bag(x => x.ActorList, bag => {
      bag.Key(k => { 
         k.Column(col => col.Name("MovieId"));
      bag.Cascade(Cascade.All | Cascade.DeleteOrphans);
    }, action => action.OneToMany());
We now have the following code that retrieves a Movie and then the count of all actors:-
var movie = Session.Query<Movie>()
              .Where(w => w.Id == id).SingleOrDefault();
var actorCount = movie.ActorList.Count();
This actually will send two queries to the database:-
-- statement #1
select movie0_.Id          as Id1_,
       movie0_.Name        as Name1_,
       movie0_.Director    as Director1_
from   Movie movie0_
where  movie0_.Id = 'ffffffff-eeee-dddd-cccc-000000000005' /* @p0 */

-- statement #2
SELECT actorlist0_.MovieId as MovieId1_,
       actorlist0_.Id      as Id1_,
       actorlist0_.Id      as Id2_0_,
       actorlist0_.Name    as Name2_0_,
       actorlist0_.Role    as Role2_0_
FROM   ActorRole actorlist0_
WHERE  actorlist0_.MovieId = 'ffffffff-eeee-dddd-cccc-000000000005' /* @p0 */
As you can see this is NOT the intended results we would expect. We have the first SQL query that retrieves a Movie then the second SQL query performs a select * from ActorRole. This means that the second query selects the whole collection and performs the count in memory.

Is there a easy solution that we can use to instruct NHibernate to use a select count(*) from rather than a select * ? If you read the title of this Blog and are still awake, you are probably screaming out lazy='extra'. So how do we use this?

If you are using XML mappings then you would use:-


If you are using the new mapping by code then you can achieve the same by adding:-
Bag(x => x.ActorList, bag => {
  bag.Key(k => { 
    k.Column(col => col.Name("MovieId"));
  bag.Cascade(Cascade.All | Cascade.DeleteOrphans);
}, action => action.OneToMany());
Now lets look at the SQL that gets sent to the database:-
-- statement #1
select movie0_.Id          as Id1_,
       movie0_.Name        as Name1_,
       movie0_.Director    as Director1_
from   Movie movie0_
where  movie0_.Id = 'ffffffff-eeee-dddd-cccc-000000000005' /* @p0 */

-- statement #2
SELECT count(Id)
FROM   ActorRole
WHERE  MovieId = 'ffffffff-eeee-dddd-cccc-000000000005' /* @p0 */
It should also be noted that it does not matter if you are using HQL, Query, QueryOver or ICriteria to query your data the outcome is exactly the same. Don't you just love it when being able to tweak the mappings so as to optimise your code.

Sunday, 21 August 2011

NHibernate 3.2 nxtgenug presentation at Southampton

Just a quick blog post explaining my recent presentation at the Southampton NxtGenUg meeting that took place on Thursday the 18th August.

The presentation was broken up into 4 parts:-

Part 1

Models and mappings - Shows how to model components, inheritance and collections using hbm XMLmappings

Part 2

Mapping by code - Shows how to model the same model in part 1 using the new sexy mapping by code syntax new to NHibernate 3.2. A simple console application showed how to retrieve some data.

Part 3

Sessions and transactions - Shows how NHibernate can be configured into a MVC 3 application, how a unit of work can implemented and how we can use NHProf.

Part 4

Queries - Shows how we break up a MVC 3 application into layers comprising of a Domain.Model, a service layer, a repository layer and the MVC 3 layer. The application uses Unity to achieve a loosely coupled design. NHProf is used extensively to show how we can optimise the queries. This part also covers eager loading versus lazy loading and how you should be aware of unwanted side effects.

Click here to download the complete presentation, the RAR file contains:-
  • DB structure
  • DB data
  • Presentation notes
  • All four VS2010 SLN

UPDATE : Newer demo and slides can be found here.

My next presentation is at Oxford on September 13th.

If you would like me to present NHibernate to your user group in the U.K. (or South Africa anytime in February 2012) then please contact me via twitter @rippo or email me richard AT

Wednesday, 17 August 2011

Linq into a DTO

The QueryOver API has been in NHibernate since 3.0. It combines the use of extension methods and lambda expressions to provide a statically typesafe wrapper round the ICriteria API. This basically means that we lose all the 'magic strings' that the ICriteria API gave us. Another bonus is that refactoring tools like 'Find All References', and 'Refactor->Rename' work perfectly.

So QueryOver sounds like a dream querying tool for nHibernate. The following code shows how we would project a few columns from a table into a DTO:-
var schoolList = Session.QueryOver<lmschool>()
         .SelectList(i => i
         .Select(p => p.Name).WithAlias(() => dto.Name)
         .Select(p => p.Lat).WithAlias(() => dto.Lat)
         .Select(p => p.Lng).WithAlias(() => dto.Lng)
       .Where(w => w.Lat != null && w.Lng != null)
However is this as intuitive as you would like? Does it roll out of your head? There is another way to perform the same query and that is to use the built in Linq provider. I shied away from the Linq provider in both version 3.0 and 3.1. However since the release of NH3.2 there has been many improvments, sure there is still a lot you can't do and you get the dreaded Not supported exception but it is coming of age. To rewrite the above QueryOver code with linq we can do this:-
var schoolList = (from school in Session.Query<lmschool>()
        .Where(w => w.Lat != null && w.Lng != null)
      select new LmSchoolMarkerDto {
  		    Name = school.Name,
                    Lat = school.Lat,
                    Lng = school.Lng,
Isn't this just a bit easier to understand and to be honest I think this is something that is easily remembered.

In both cases the following SQL is generated:-
select,, s.lng from school 
where ( is not null) and (s.lng is not null) 

Monday, 15 August 2011

Getting orders and the count of orderlines using Linq

When using NHibernate we sometimes need to pick the right tool from the toolbox. One example of this is when you have the follow problem.

I need to get a list of orders and the count of order items against each order.

Now me being a bit savvy with QueryOver would sit down and would write a complex piece of code. This code doesn't roll out the top of your head and you need to fit a few pieces together to get it right.

Another option is to use the NHibernate built in Linq provider. In my opinion before NH3.1 the Linq provider just was not quite up to the job and people shied away from it. However the NHibernate team has done a lot of work on the built in Linq provider and it is now maturing into a useful tool.

So back to the solution to our problem, simply first create a DTO:-
public class OrderDto
    public long Id { get; set; }
    public DateTime OrderDate { get; set; }
    public int LineCount { get; set; }
I would always try and project into a DTO rather than using filters or formula's as I try to keep my mappings as clean as possible. This gives me better flexibility and does not back me into a corner.

The linq query is very easy:-
return Session.Query<Order>()
        .Select(o => new OrderDto
            Id = o.Id,
            OrderDate = o.DateOfOrder,
            LineCount = o.ActorList.Count
This would produce the following SQL:-
select o.Id, o.OrderDate,
       (select cast(count(*) as INT) from  OrderLines
          where  Order.Id = OrderLines.orderId) as LineCount
from   Order o

I am going to look more closely into the built in NHibernate Linq provider as it can produce some easy and beautiful code.

Thursday, 11 August 2011

Queryover into a DTO

Sometimes I get a stuck with some of syntax with QueryOver and how you go about projecting into a DTO. The code below simply goes to the database and pulls back all the schools that have a Lat and a Lng. However I am only interested in three columns, my [school] table contains around 15 columns which also includes a LONG TEXT column. If I simply call select * from school then a lot of redundant data will be sent down the wire that is not actually needed.

So I first would write the following code snippet that pulls back the all the columns from the database. I then iterate through the list and select only a small sub set of the data. e.g:-
var schoolList = Service.Lm.LmSchoolDao.QueryOver()
		.Where(w => w.Lat != null && w.Lng != null)

var xml = new XElement("markers",
  from s in schoolList
    select new XElement("marker",
      new XAttribute("name", s.Name),
      new XAttribute("lat", s.Lat.ToString()),
      new XAttribute("lng", s.Lng.ToString())
The above code works great but I get that nagging feeling that I need to optimise this a bit. Therefore I think it will be a good idea to just select a few columns from the table and project the result into a list. Using QueryOver I would write something along the lines of:-
var schoolList = Service.Lm.LmSchoolDao.QueryOver()
        .SelectList(i => i
          .Select(p => p.Name)
          .Select(p => p.Lat)
          .Select(p => p.Lng)
        .Where(w => w.Lat != null && w.Lng != null)

public class MarkerDto
  public string Name { get; set; }
  public decimal? Lat { get; set; }
  public decimal? Lng { get; set; }
The above compiles fine, it returns the correct data and columns from the database and I can see that my schoolList has the correct number of rows, so far so good.

However it has one major flaw, each property on the schoolList is NULL. You sit back scratch your head and realise that you have not told NHibernate how to map the returned columns in to the correct properties. The missing piece of the jigsaw is the .WithAlias() QueryOverProjectionBuilder. Armed with this knowledge you tweak your code as follows:-
MarkerDto dto = null;
var schoolList = Service.Lm.LmSchoolDao.QueryOver()
         .SelectList(i => i
         .Select(p => p.Name).WithAlias(() => dto.Name)
         .Select(p => p.Lat).WithAlias(() => dto.Lat)
         .Select(p => p.Lng).WithAlias(() => dto.Lng)
       .Where(w => w.Lat != null && w.Lng != null)
Sometimes you just got to sit back and think. However I still personally prefer the QueryOver syntax rather than using Linq. In fact I got no idea if the NHibernate Linq provider can do this, maybe this would make a good blog post for another day.

Wednesday, 10 August 2011

Gary Short does node.js

A big thank you to Gary Short as he drove a whopping 410 miles from Dundee to Hereford NxtGenUG to talk about node.js. Respect!

The presentation started with Gary taking it easy with us:-

  • He first explained what node.js was and how to install on Ubuntu it via the package manager apt-get 
  • We then went on to see how to run node.js via a putty client and saw a "hello world" example

At this point I could not work out why I would use node.js over using .net.

After pizza (yes we always serve pizza) Gary went on to show us how how node.js can monitor lots of twitter feeds and store the results into CouchDB. From here he then showed us how node.js can send back Json via a HTTP request. A simple webforms (!) project then pulled back this Json and produced charting.

The reason Gary chose Ubuntu was due to the fact that twitter servers are up and down like yoyo's and utilising Ubuntu allowed him to take advantage of Upstart and respawn.

The benefits of using node.js are:-
  1. It can run on a low spec machine as it runs linux (Ubuntu)
  2. It is totally non blocking and does not deadlock
  3. It only uses a small heap allocation as it sleeps until a connection is made
  4. It uses less compute cycles as it employs a callback strategy, that it sleeps until the callback is made
  5. It can handle heavy loads of HTTP traffic
  6. It is therefore ideal for the cloud where you pay for compute cycles

All in all the presentation was a real eye opener and I for one would recommend it to anyone. I think he has a proposed a more advanced session for DDD North

If you would like to see any future sessions at Hereford then please click here.

Monday, 8 August 2011

Nhibernate 3.2 and medium trust environments

Note: There is a new blog post based on this one, however you should read this one first to get an idea of the problems.
I am trying out NHibernate in a medium trust environment using the built in DefaultProxy. However I get the following error:-
Operation could destabilize the runtime. 

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Source File: ProxyFactory.cs Line: 51 
Stack Trace: 
 [VerificationException: Operation could destabilize the runtime.] CmsContentProxy..ctor() +27 
 [TargetInvocationException: Exception has been thrown by the target of an invocation.] 
   System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, 
   RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) +0
   System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache) +98 
   System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache) +241 
   System.Activator.CreateInstance(Type type, Boolean nonPublic) +69 
   System.Activator.CreateInstance(Type type) +6 
   NHibernate.Proxy.DynamicProxy.ProxyFactory.CreateProxy(Type instanceType, IInterceptor interceptor, Type[] baseInterfaces) in ProxyFactory.cs:51 
   NHibernate.Proxy.DefaultProxyFactory.GetProxy(Object id, ISessionImplementor session) in DefaultProxyFactory.cs:20
Line 51 within the NHibernate sources:-
/Proxy/DyanmicProxy/ProxyFactory.cs line 51 
  object result = Activator.CreateInstance(proxyType); 
Things I have tried:-
1. Castle Proxy - They have created a proxy for 3.1 and not 3.2. I have looked at the NHibernate sources for both 3.1 and 3.2 but the interface to implement has changed.
2. Googled how to work around this problem but to no avail
3. Asked the question on NHusers user group.

I could wait to see if the castle/linfu/spring teams release a proxy that is compatible with version 3.2 however it would be nice to identify the problem within NHibernate itself so as I don't need a dependency on a third party proxy.

If anyone is interested in helping me I have provided a policy.config file, all you need to do is to place it in your web root and place this in your web.config.
  <trustLevel name="Custom" policyFile="policy.config" /> 
<trust level="Custom" originUrl="" /> 
I really would love to get this NH3.2 running in Medium trust as I love the new mapping by code syntax, any ideas, pointers, workarounds will be gratefully received.

Whats next:-
I can see that Spring has now updated there framework so as to be compatible with NH3.2 GA, I will try this next...

Wednesday, 3 August 2011

Gary Short and node.js

NxtGenUG are proud to announce that Gary Short is visiting Hereford on Monday 8th August 2011, to talk about Node.Js. It seems that Node.js is gaining momentum in the developer community.

Gary's presentation will cover a quick introduction to Node.js before going on to de-construct a network appliance application written in Node.js. By the end of the session you will have a better appreciation for this language which is sure to become more important in the coming years.

According to WIKI:-
Node.js is an event-driven I/O server-side JavaScript (built on V8 JavaScript engine) environment for Unix-like platforms. It is intended for writing scalable network programs such as web servers. It was created by Ryan Dahl in 2009, and its growth is sponsored by Joyent, which employs Dahl. Node.js is similar in purpose to Twisted for Python, Perl Object Environment for Perl, libevent for C and EventMachine for Ruby. Unlike most JavaScript, it is not executed in a web browser, but is instead a form of server-side JavaScript. Node.js includes a REPL environment for interactive testing.

HTTP Server version of hello world in Node.js:
var http = require('http');
http.createServer(function (request, response) {
    response.writeHead(200, {'Content-Type': 'text/plain'});
    response.end('Hello World\n');
If you would like to find out more then please register on the NxtGenUG site. The event is FREE for non members, details on how to find us are:-

Shire Hall
Union Street

Monday, August 08, 2011, 7:00 PM - 9:00 PM

We hope to see you there.

Monday, 1 August 2011

Return parents based on a child filter with QueryOver

Sometimes an ORM doesn't quite do what you think it should. One classic example of this when we need to get a parent entity based on a where condition on the child.

Given the following domain:-
public class Movie : Entity {
  public virtual string Name { get; set; }
  public virtual string Description { get; set; }
  public virtual IList<actor> ActorList { get; set; }
public class Actor : Entity {
  public virtual string Name { get; set; }
  public virtual string Role { get; set; }
We have a Movie (parent) that has a collection of Actor (children). Now comes the challenge we want to get a list of movies where an actors name starts with the letter m, at first glance this is fairly trivial and we would sit down and construct the following query:-
return Session
  .Inner.JoinQueryOver<actor>(a => a.ActorList)
  .Where(a => a.Name.IsLike("m%"))
What do you think will get returned here?

Lets take one step back and look at the sql that gets generated:-
SELECT this_.Id          as Id0_1_,
       this_.Name        as Name0_1_,
       this_.Description as Descript4_0_1_,
       actor1_.Id        as Id2_0_,
       actor1_.Name      as Name2_0_,
       actor1_.Role      as Role2_0_
FROM   Movie this_
       inner join ActorRole actor1_
         on this_.Id = actor1_.MovieId
WHERE  actor1_.Name like 'm%' /* @p0 */
This then returns the following rows:-

As you can see 4 rows are returned but the same movie appears twice as there are two actors that start with the letter M for The Dark Knight. You would probably expect NHibernate to hydrate 3 parent entities but in actual fact it hydrates 4. After a bit of head scratching you realise NHibernate is doing what it should it is taking the 4 rows from the database and hydrating the results. The problem now is that your UI would display 4 results rather than 3.

So how do we instruct NHibernate to filter duplicates from the parent. One very simple approach is to specify a ResultTransformer:-
return Session
  .Inner.JoinQueryOver<Actor>(a => a.ActorList)
  .Where(a => a.Name.IsLike("m%"))
Now there is oneimportant thing to note here. This only performs the filter on the client side. You still get 4 rows returned from the database BUT only distinct parents are hydrated. Depending on a number of factors like you don't require paging and size of the children collections are small this might be suitable.

So is there another solution to this problem? Can we only get three parents returned from NHibernate? Maybe we want to page the results and don't like the idea of transmitting a lot of repeated data down the wire. Well there is a solution we need to introduce a sub query. If we were to sit down and write this in SQL we may come up with something like:-
SELECT this_.Id          as Id0_0_,
       this_.Name        as Name0_0_,
       this_.Description as Descript4_0_0_,
FROM   Movie this_
WHERE  this_.Id in (SELECT distinct this_0_.Id as y0_
                        FROM   Product this_0_
                               inner join ActorRole actor1_
                                 on this_0_.Id = actor1_.MovieId
                        WHERE  actor1_.Name like 'm%' /* @p0 */)
Ok this is not sub optimal SQL but you get the picture, we want to return distinct movies that have a at least one actor beginning with the letter a. The corresponding QueryOver code would look like this:-
var moviesWithActor = QueryOver.Of<Movie>()
  .JoinQueryOver<Actor>(m => m.ActorList)
  .Where(a => a.Name.IsLike("m%"))
  .Select(Projections.Distinct(Projections.Property<Movie>(m => m.Id)));

return Session
  .WhereProperty(m => m.Id)
This now returns 3 rows down the wire and we require no client side transfomations (filtering):-

Is this the final solution? In reality the optimum SQL might look something like this:-
SELECT distinct
  Movie m
inner join 
  ActorRole a on m.Id = a.MovieId
WHERE a.Name like 'm%'
However I will leave this to another day!