How to render Max(Substring) with Lambda Extensions

Question

Hi everybody. I'm using NHibernate with Lambda Extensions. I'd like to know how to nest a Max function with a Substring.

The following statement retrieves Max("invoice_id")

var ret = session
          .CreateCriteria<Invoice>()
          .SetProjection(Projections.Max("invoice_id"))
          .UniqueResult();

but in my case the field invoice_id is made in this way: 12345.10 where 12345 is the invoice number, and 10 refers to the current year (2010). Moreover every year the invoice number re-starts from 1. So I need to calculate the Max function only over the first 5 digits. How can I do it?

Answer 1

Hi. You can use a server-specific function like SUBSTRING (or its equivalent in your DBMS) like so:

var ret = session
.CreateCriteria<Invoice>()
    .SetProjection(
        Projections.Max(
            new SqlFunctionProjection("SUBSTRING", 
                NHibernateUtil.String, 
                Projections.Property("invoice_id"), 
                Projections.Constant(1), 
                Projections.Constant(5))))
    .UniqueResult();

Answer 2

You should create a struct and custom NHibernate type for the invoice number rather than relying on string manipulation.

The struct and nh type will encapsulate all the logic for comparing and sorting.

http://intellect.dk/post/Implementing-custom-types-in-nHibernate.aspx