Using DateAdd with NHibernate Linq

The goal is to do something like this using NHibernate and Linq:-

Session.Query<Item>.Where(x => x.Start.AddHours(3) > x.Finish );

If you run this code you will get the following error message:-

[NotSupportedException: System.DateTime AddHours(Double)]

So is there a solution?

The answer is yes, NHibernate is very extensible. The solution below is based on NHibernate 3.3 and the Loquacious configuration.

First you need to create a custom dialect and tell NHibernate about the MsSql `dateadd` function:-

public class CustomDialect : MsSql2008Dialect
{
    public CustomDialect()
    {
        RegisterFunction(
             "AddHours",
             new SQLFunctionTemplate(
                  NHibernateUtil.DateTime,
                  "dateadd(hh,?2,?1)"
                  )
             );
    }
}

Now you need to add the following two classes, the first one extends the DefaultLinqToHqlGeneratorsRegistry and then implement our AddHoursGenerator method.

public class MyLinqtoHqlGeneratorsRegistry : 
    DefaultLinqToHqlGeneratorsRegistry
{
    public MyLinqtoHqlGeneratorsRegistry()
    {
        this.Merge(new AddHoursGenerator());
    }
}

public class AddHoursGenerator : BaseHqlGeneratorForMethod
{
    public AddHoursGenerator()
    {
        SupportedMethods = new[] {
        ReflectionHelper.GetMethodDefinition<DateTime?>(d =>      
                d.Value.AddHours((double)0))
          };
    }

    public override HqlTreeNode BuildHql(MethodInfo method,
      System.Linq.Expressions.Expression targetObject,
      ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
      HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
    {
        return treeBuilder.MethodCall("AddHours",
                visitor.Visit(targetObject).AsExpression(),
                visitor.Visit(arguments[0]).AsExpression()
            );
    }
}

Now all you need to do is to add the CustomDialect to your configuration and tell NHibernate about your custom AddHours generator

var configure = new Configuration()
          .DataBaseIntegration(x => {
              x.Dialect<CustomDialect>();
              x.ConnectionStringName = "db";
          })
          .LinqToHqlGeneratorsRegistry<MyLinqtoHqlGeneratorsRegistry()
          .CurrentSessionContext<WebSessionContext>();

Note: we add .LinqToHqlGeneratorsRegistry<MyLinqtoHqlGeneratorsRegistry()

I have based this on this blog post by fabio.

You can now use your code as is:-

Session.Query<Item>.Where(x => x.Start.AddHours(3) > x.Finish );

This is also possible in 3.2 but the public override HqlTreeNode BuildHql(..) parameters are slightly different...

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!

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

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.