Using QueryOverProjectionBuilder with QueryOver

We all strive to keep our code DRY and sometimes we may have several QueryOver methods that returns the same DTO. This may then lead us to try and create a method that returns a SelectList to reuse in our queries. This gives us the benefit of only changing one method if for example our DTO changes.

First lets look at the QueryOver method. As you can see I have a method call named GetDtoList()

return Session.QueryOver<InvoiceDto>()
    .SelectList(GetDtoList())
    .TransformUsing(Transformers.AliasToBean<InvoiceDto>())
    .List<InvoiceDto>();

Now for the GetList method. As you can see we return a Func of QueryOverProjectionBuilder. In the code we simply build the list as we normally would and just return it:-

Func<QueryOverProjectionBuilder<InvoiceDto>, 
    QueryOverProjectionBuilder<InvoiceDto>> GetDtoList() {
    InvoiceDto dto = null;
    return list => list
      .Select(w => w.ClientName).WithAlias(() => dto.ClientName)
      .Select(w => w.InvoiceDate).WithAlias(() => dto.InvoiceDate)
      .Select(w => w.InvoiceId).WithAlias(() => dto.InvoiceId);
}

Pretty nice I think although the use case for this may be a little limited. The next challenge is to use this when we join to another entity i.e.

return Session.QueryOver<Invoice>()
    .JoinQueryOver<Client>()
    .SelectList(GetDtoList())
    .TransformUsing(Transformers.AliasToBean<InvoiceDto>())
    .List<InvoiceDto>();

SetResultTransformer into an anonymous type

The other day I was looking for a way to shortcut the SetResultTransformer and cast a list to an anonymous type rather than having to hand craft DTO classes with public properties (getters/setters). I stumbled on a GitHub Gist by Buthrakaur (Filip Kinský) that did just this.

Why would we want to do this?

• We may want to return a few columns from an entity
• We want to return Json from an MVC view which just gets thrown away
• We want to cut down the amount of code that just handles redundant DTO's because we want to pass data around

The solution to this is really easy (thanks Filip), first we create an anonymous type inline and then project straight into this using an extension method .ListAs(dto)

//first create our anonymous type DTO
var dto = new { 
    Id = 0L, 
    Source = string.Empty, 
    Destination = string.Empty, 
    Is301 = false
};

//notice the ListAs(dto) extension method
var model = Session.QueryOver<CmsRedirect>()
  .SelectList(s => s
    .Select(x => x.Id).WithAlias(() => dto.Id)
    .Select(x => x.Source).WithAlias(() => dto.Source)
    .Select(x => x.Destination).WithAlias(() => dto.Destination)
    .Select(x => x.Do301).WithAlias(() => dto.Is301)
  )
  .Take(take).Skip(page * pageSize)
  .ListAs(dto);

return Json(new { Total = total, List = model }, 
    JsonRequestBehavior.AllowGet);

The source to the GIST can be found here.

public static class NHibernateExtensions {
  public static IList<TRes> ListAs<TRes>(
      this IQueryOver qry, TRes resultByExample) {

    var ctor = typeof(TRes).GetConstructors().First();

    return qry.UnderlyingCriteria
      .SetResultTransformer(
        Transformers.AliasToBeanConstructor(
         (ConstructorInfo) ctor)
        ).List<TRes>();
  }
}

And the Json that gets returned is:-

{
  "Total":3,
  "List":[
    {"Id":101000,"Source":"clients.aspx",
       "Destination":"portfolio","Is301":true},
    {"Id":101101,"Source":"consultancy.aspx",
       "Destination":"consultancy","Is301":true},
    {"Id":101102,"Source":"contact.aspx",
       "Destination":"contact","Is301":true},
  ]
}

This is perfect for plugging straight into my Kendo UI Grid.

A big thank you to Filip for taking some of the repetiveness out of my day.

Simple.Data explicit joins

I am delving a bit more into Simple.Data and starting to look at joins. I am going to begin with a classic one-to-many relationship between the tables cmsmeta and cmspage:-

So how does simple.data handle joins? In this blog I am going to show you explicit joins.

The following code shows how we can project 2 columns from both tables into a DTO using the on(...) form.

var db = Database.OpenConnection(...);

var pages = db.CmsPage
  .Query()
  .Join(db.CmsMeta).On(db.CmsPage.MetaId == db.CmsMeta.Id)
  .Select(
    db.CmsPage.Id, 
    db.CmsPage.PageName, 
    db.CmsMeta.Keywords.As("MetaKeywords"),
    db.CmsMeta.Title.As("MetaTitle")
  ).Cast<PageDto>();

foreach (var page in pages)
  Console.WriteLine(string.Concat(page.Id, " ", page.PageName, ",", page.MetaKeywords, "-", page.MetaTitle));

The DTO looks like this:-

internal class PageDto { {
  public long Id { get; set; }
  public string PageName { get; set; }
  public string MetaTitle { get; set; }
  public string MetaKeywords { get; set; }
}

As you can see we are joining CmsPage with CmsMeta using on(...).

There is also another way to describe an explicit join and that is to use named parameters, that is describe the join as Id: db.CmsPageMetaId, whichever you choose is down to personal taste. For example:-

var pages = db.CmsPage
  .Query()
  .Join(db.CmsMeta, Id: db.CmsPage.MetaId)
  .Select(...)

Both pieces of code produces the following:-

and in case you are wondering the generated SQL looks like this:-

select 
  cmspage.Id,
  cmspage.PageName,
  cmsmeta.Keywords AS MetaKeywords,
  cmsmeta.Title AS MetaTitle
from 
  cmspage
JOIN cmsmeta ON (cmsmeta.Id = cmspage.MetaId)

There is another type of join that is called Natural Joins which requires referential integrity and uses the Foreign Key constraints on the tables. Read more about natural joins. Unless you are using the innoDb engine in MySql then natural joins are not supported.

Simple.Data a projection into a dto

Sorry for the long delay since my last post but my wife gave birth to our son Nico a few weeks ago and to honest this has taken up all my time....

Anyway back to the blog, today I decided to have a quick look into Simple.Data. First I created a new console application and then installed the package via nuget:-

PM> Install-Package Simple.Data.Mysql

If you are using Sql Server then just use:-

PM> Install-Package Simple.Data.SqlServer

The first thing I decided to try was projecting a couple of columns from a single table into a DTO. My schema looks like this (note mysql):-

var db = Database.OpenConnection("server=1.1.1.1;user=usera;database=wildesoft;password=*********;");

IEnumerable<CmsPageDto> pages = db.CmsPage
  .All()
  .Select(db.CmsPage.PageId.As("Id"), db.CmsPage.PageName)
  .Cast<CmsPageDto>();

foreach (var page in pages) {
  Console.WriteLine(page.Id + " " + page.PageName);
}

The dto looks like this:-

public class CmsPageDto {
  public int Id { get; set; }
  public string PageName { get; set; }
}

One thing that I could not fathom out was how to change the column PageId to just Id. After downloading the Simple.Data source code and looked at the tests I worked out that I needed to use the .As("NewColumnName")

and volia...

The generated SQL looks like this:-

select
  cmspage.PageId AS Id,
  cmspage.PageName 
from
  cmspage

A sweet spot is that preserves the case of the table and columns names, very important for MySql server.

Boy is Simple.Data well just simple to use! I am going to use Simple.Data in my next project and hopefully be able to blog about it a bit more..

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
  .QueryOver<Product>()
  .SelectList(i => i  
    .Select(p => p.Name).WithAlias(() => dto.Name)  
    .Select(p => p.GetType()).WithAlias(() => dto.ProductType)
  )  
  .TransformUsing(Transformers.AliasToBean<ProductDto>())  
  .List<ProductDto>();  

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

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.

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!

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.

QueryOver with multiple joins into a DTO

Consider the following NHibernate HQL. As you can see it is joining three tables and pulling back three columns from two different tables.

The [Organisation] table joins to the [OrganisationType] which in turn joins to the [IncomeType] table.

hql = @"
 select 
  t.Name, ot.LocalRespend, ot.LocalRespendOutArea
 from
  organisation o 
 inner join
  organisationincometype ot on ot.OrganisationId = o.Id
 inner join
  incometype t on ot.IncomeTypeId = t.Id
 where 
  o.Id = :orgId and t.Id = :incomeTypeId;";

How would you do the following using the QueryOver syntax?

Simple when you know how:-

var oit = null;
var  it = null;

return Session.QueryOver<organisation>()
   .Where(w => w.Id == orgId)
  .JoinAlias(r => r.OrganisationIncomeTypeList, () => oit)
  .JoinAlias(r => oit.IncomeTypeList, () => it)
   .Where(() => it.Id == incomeTypeId)
  .Select(
   Projections.Property(() => it.Name),
   Projections.Property(() => oit.LocalRespend),
   Projections.Property(() => oit.LocalRespendOutArea)
  )
  .TransformUsing(Transformers.AliasToBean<organisationincometypedto>())
  .SingleOrDefault<organisationincometypedto>();

The secret is that we are using JoinAlias into temporary local variables and then projecting these aliases into our DTO. The beauty of using the QueryOver syntax is that we are losing all the magic strings that we would get with HQL or Criteria.