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)
       .TransformUsing(Transformers.AliasToBean<MarkerDto>())
       .List<MarkerDto>();

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,
      }).ToList();

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.name, s.lat, s.lng from school 
where (s.lat is not null) and (s.lng is not null) 

Simple!