How to Calculate Age given date of birth using NHibernate QueryOver

Question

Lets say i have a class as follows:

public class Person
{
    public int PersonId {get; set;}
    public string Firstame {get; set;}
    public string Lastname {get; set;}
    public datetime Birthdate {get; set;}
}

Then TSQL as:

select (Firstname + ' ' + Lastname) as Name,
    Birthdate,
    case 
        when (month(cast(Birthdate as date)) > month(getdate())
        or (day(cast(Birthdate as date)) > day(getdate()) and month(cast(Birthdate as date)) = month(getdate())))
            then datediff(year, cast(Birthdate as date), getdate())-1
        else datediff(year,cast(Birthdate as date), getdate())
    end as Age
from Person
go

I am new to NHibernate and QueryOver and i have been trying to come up with a way to translate the following into QueryOver.

case 
    when (month(cast(Birthdate as date)) > month(getdate())
    or (day(cast(Birthdate as date)) > day(getdate()) and month(cast(Birthdate as date)) = month(getdate())))
        then datediff(year, cast(Birthdate as date), getdate())-1
    else datediff(year,cast(Birthdate as date), getdate())
end as Age

Any suggestions on how this can be achieved using QueryOver or even better as an IProjection extension?

The following are some of the materials i have been looking at, but being a beginner at this, i am having a hard time putting something concreat together.

NHibernate QueryOver CASE WHEN calculate on column value

http://www.andrewwhitaker.com/blog/2014/08/15/queryover-series-part-7-using-sql-functions/

Answer 1

After a little bit of struggle, i was able to cook up something similar to what i was looking to archive. There maybe be better solutions out there, but for now, this works as expected.

public IQueryOver<Person> GetQuery(ISession session)
{
    Person person = null;

    DateTime? endDate = DateTime.Today;

    SomePersonView dto = null;

    IProjection birthDate = Projections.Conditional(
        Restrictions.IsNull(Projections.Property(() => person.BirthDate)),
        Projections.Constant(endDate, NHibernateUtil.DateTime),
        Projections.Property(() => person.BirthDate));

    var personQuery = session.QueryOver<Person>(() => person)
        .Select(
            Projections.Distinct(
                Projections.ProjectionList()
                    .Add(Projections.SqlFunction("concat",
                            NHibernateUtil.String,
                            Projections.Property(() => person.FirstName),
                            Projections.Constant(" "),
                            Projections.Property(() => person.LastName)).WithAlias(() => dto.Name))
                     .Add(Projections.Property(() => person.BirthDate).WithAlias(() => dto.BirthDate))
                     .Add(DateProjections.Age("yy", birthDate, endDate).WithAlias(() => dto.Age))))
        .TransformUsing(Transformers.AliasToBean<SomePersonView>());

    return personQuery;
}

And here is the Extension where 'DateProjections.Age' comes from.

public static class DateProjections
{
    private const string DateDiffFormat = "datediff({0}, ?1, ?2)";

    // Maps datepart to an ISQLFunction
    private static Dictionary<string, ISQLFunction> DateDiffFunctionCache = new Dictionary<string, ISQLFunction>();

    public static IProjection DateDiff(string datepart, IProjection startDate, DateTime? endDate)
    {
        ISQLFunction sqlFunction = GetDateDiffFunction(datepart);

        return Projections.SqlFunction(
            sqlFunction,
            NHibernateUtil.Int32,
            startDate,
            Projections.Constant(endDate));
    }

    //Get exact age of a person as of today
    public static IProjection Age(string datepart, IProjection startDate, DateTime? endDate)
    {
        IProjection myAge = DateDiff("yy",
            startDate, endDate);

        IProjection ageMinusOne = Projections.SqlFunction(
            new VarArgsSQLFunction("(", "-", ")"), NHibernateUtil.Int32, myAge,
            Projections.Constant(1));

        IProjection datePartMonthBirthdate = Projections.SqlFunction("month", NHibernateUtil.Int32,
            startDate);

        IProjection datePartDayBirthdate = Projections.SqlFunction("day", NHibernateUtil.Int32,
            startDate);

        IProjection datePartMonthCurrentDate = Projections.SqlFunction("month", NHibernateUtil.Int32,
            Projections.Constant(endDate));

        IProjection datePartDayCurrentDate = Projections.SqlFunction("day", NHibernateUtil.Int32,
            Projections.Constant(endDate));

        IProjection myRealAge = Projections.Conditional(
                                Restrictions.Or(
                                    Restrictions.GtProperty(datePartMonthBirthdate, datePartMonthCurrentDate),
                                    Restrictions.GtProperty(datePartDayBirthdate, datePartDayCurrentDate)
                                    && Restrictions.EqProperty(datePartMonthBirthdate, datePartMonthCurrentDate)),
                                ageMinusOne,
                                myAge);
        return myRealAge;
    }

    private static ISQLFunction GetDateDiffFunction(string datepart)
    {
        ISQLFunction sqlFunction;

        if (!DateDiffFunctionCache.TryGetValue(datepart, out sqlFunction))
        {
            string functionTemplate = string.Format(DateDiffFormat, datepart);
            sqlFunction = new SQLFunctionTemplate(NHibernateUtil.Int32, functionTemplate);

            DateDiffFunctionCache[datepart] = sqlFunction;
        }
        return sqlFunction;
    }
}