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

NHibernate 3.3 and medium trust

With the new release of NHibernate 3.3 it seems from my early testing, that the Medium Trust issues have now disappeared and its good to see NHibernate 3.3 works in Medium Trust environments. I need to play with the NH Linq provider more to see what fails but from what I have seen so far I have to congratulate the NH development Team.

As a side note, if anyone is wondering how I test this then please download the modified medium trust file (called policy.config from Rackspace cloud) unzip it and save it to your web root folder.

Next simply add this to your web.config:-

<system.web>
  <securitypolicy>
    <trustlevel name="Custom" policyFile="policy.config" />
  </securityPolicy>
  <trust level="Custom" originUrl="" />
</system.web>

You now have a local development system running under medium trust. For those that do not run Rackspace cloud I would recommend asking your hosting provider for their medium trust policy file.

This is an update from a previous post when using NHibernate 3.2 (September 2011). The JIRA issue can be found here.

Using filters for unmapped columns in NHibernate

Using filters in NHibernate is a little known feature to new users. Filters can be applied in quite few places.

This blog shows us how we can apply a filter onto the class definition:-

<class name="Domain.Model.BlogPost, Domain.Model" table="Posts" 
       where="(IsLive=1)">
    ...
</class>

However be warned this will always return blog posts that are live. Sometimes you may want to get blog posts that are not live. If you use a filter this way then you will never be able to return isLive=0 or ALL blogs regardless of the flag.

Fortunately NHibernate allows us to switch filters on/off at a flick of a switch, add this to your mappings file:-

<filter-def name="BlogFlag">
    <filter-param name=":isLive" type="System.Int"/>
</filter-def>

<class name="Domain.Model.BlogPost, Domain.Model" table="Posts">
  <filter name="BlogFlag" condition="(isLive=:flag)"/>
    ...
</class>

Using the flag could not be simpler, just set the flag before you query:-

//return live blogs
session.EnableFilter("BlogFlag").SetParameter("isLive", 1);
session.QueryOver<MyEntity>();

//return blogs that are not live
session.EnableFilter("BlogFlag").SetParameter("isLive", 0);
session.QueryOver<MyEntity>();

//return all blogs
session.QueryOver&t;MyEntity>();

Now one question you are asking is why would I use filters? Why wouldn't I just use

//Use a where clause! Isn't this more sensible?
session.QueryOver<MyEntity>()
  .Where(w => w.IsLive == isLive);

However one of the unknown undocumented features of filters is that the database column IsLive, DOES not need to mapped to a fully mapped property.

The second reason is that if I ALWAYS want just live Blog posts therefore adding where="(IsLive=1)"> to the class definition makes sense as the developer will does not need to remember to add the where clause for EVERY query.

In the next blog post I will show you how to use filters on collections.

Use a button instead of an anchor tag

When using JQuery and href's we sometimes see the following:-

<a id='MyLink' href="#">My Link</a>

or even worse:-

<a id='ClickMe' href="javascript:void(0);">Click Me</a>

and some jQuery which executes some code, BUT does not redirect the user.

<script>
 $('#ClickMe').click(function(e) {
   e.preventDefault();
   alert('Me clicked');
 });
</script>

Now this works if:-

1. Javascript is enabled
2. If JS is disabled then the link should actually should have a fall back, however in this case we don't want to redirect anywhere
3. Not all browsers support e.preventDefault() so to circumvent this we add a return false at the end of the function
4. If the page is long; that is longer than the height of the screen then in some browsers the page will jump to the top, losing the Y position

If you think about it these "anchors" exist solely to provide a click event, but do not actually link to other content.

So is there a better solution?

One very nice approach is to convert the anchor tag to a button element.

It can be styled like so:

<button id='Click Me' style="border:none; background:transparent; cursor: pointer;">Click me</button>

the javascript can then be changed to:-

<script>
 $('#ClickMe').click(function(e) {
   alert('Me clicked');
 });
</script>

And of course click events can be attached to buttons without worry of the browser jumping to the top, and without adding extraneous javascript such as onclick="return false;" or event.preventDefault() or even return false.

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>();

Using stored procedures with NHibernate

For some reason newbies to NHibernate struggle to find answers on Google or S.O when trying to use Stored Procedures with NHibernate. I have posted this here as a guide to help.

First define in a mapping file that is marked as embedded resource, call it StoredProcs.hbm.xml (the name doesn't matter but the extension .hbm.xml does) :-

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <sql-query name="SummaryReport">
    exec getSummaryReport  :productId
  </sql-query>
</hibernate-mapping>

The stored procedure returns the following SQL columns:-

int ProductId
nvarchar(75) ProductName
decimal(18,2) SalesTotal
bit IsActive 

First we define our c# class:-

public class SummaryReport {
  public virtual int ProductId { get; set; }
  public virtual string ProductName { get; set; }
  public virtual decimal SalesTotal { get; set; }
  public virtual boolen IsActive { get; set; }
}

NOTE: The column names returned from the stored procedure MUST match exactly (case sensitive) the property names on your c# class.

Now for the calling code, as you can see we make use of the method GetNamedQuery and SetResultTransformer:-

var results = Session
  .GetNamedQuery("SummaryReport")
  .SetInt32("productId", productId);
  .SetResultTransformer(
    new AliasToBeanResultTransformer(typeof(SummaryReport)));
return results.List<SummaryReport>();

Note: we call the stored procedure with a parameter :productId. The colon tells NHibernate that this is a parameter. In our calling code we have .SetInt32("productId", productId);, notice that the colon is not required.

Simple isn't it?

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.

QueryOver restriction using an alias

Adding a restriction on a where clause from an alias can be tricky to understand using QueryOver. The SQL we are trying to achieve is:-

from projects p
inner  join user u on p.UserId = u.UserId
where m.UserId = 100 or p.OwnerId = 100

Our goal is to achieve the following:-

• We have a project that has a owner and a list of managers.
• Both managers and owners are users
• We want to get all projects for a user that they are the owner OR the manager
• We want to use QueryOver
  

The domain:-

public class User {
    long Id;
    string Name;
}

public class Project {
    long Id;
    User Owner;
    IList<User> Managers;
}

The query is built like this:-

User manager = null;

var query = session
  .QueryOver<Project>()
  .JoinAlias(j => j.Managers, () => manager)
  .Where(w => manager.Id == user1.Id || w.Owner.Id == user1.Id)
  .List<Project>();

The above query joins Project to Managers using an alias manager. This allows us to then use the alias manager in the where statement e.g.

.Where(w => manager.Id == user1.Id || w.Owner.Id == user1.Id)

This works correctly but could have a small glitch. What happens if our domain contains a user that is a owner BUT is not a manager? The problem is the SQL will be created using an inner join. This problem can be fixed by simply adding a LEFT join to the query.

.JoinAlias(j => j.Managers, () => manager).Left

It should be noted that this was an answer I gave to a recent StackOverFlow question.

Back to the blog

After almost 3 months lay off due to our second son being born (Nico) I have decided to start blogging again (well Nico has decided that I can). He is starting to sleep more through the night and I am now waking up in the morning feeling (almost) refreshed and quite lucid.

I still will be blogging about the .net framework which will include MVC, NHibernate and Simple.Data.

Fingers crossed I can get 4-6 posts out a month and work my way towards 8 posts. If there is anything you want to know then give me a shout!

Thanks for everyones support, Rippo