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

5 comments:

  1. I kinda missed out at first by forgetting to add NHibernate.linq to my usings. (you can delete above post)

    This really is a great post.
    I'm quite new to NHibernate and hope to learn a lot from your posts here.
    Nice to see that you started blogging again since this week.

    Thx for this usefull item!

    ReplyDelete
  2. Hi Guan, comment deleted as requested. Many thanks for your kind words its comments like this that makes it all worth while blogging.

    ReplyDelete
  3. Your welcome. Just tweet about your new blog posts and I'll see them. (@Gigi...)

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Is there any way to do nested loops when we join multiple tables

    ReplyDelete