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)
		.List<LmSchool>();

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

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

3 comments:

  1. Hi, since I've been in need to use something like this recently, I found out it is even easier in LINQ provider:

    var schoolList = (from school in LmSchoolDao.Query()
    where school.Lat != null && school.Lng != null
    select new MarkerDto { Name = school.Name, Lat = school.Lat, Lng = school.Lng })
    .ToList();

    ReplyDelete
  2. @Twoflower, lol my next post on Monday explains exactly the same thing! Thanks for comment

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

    ReplyDelete