How to use datediff sql function in nHibernate?

Question

I am developing an application to test execution time SQL queries using different frameworks. I have some problem to write one of the queries using Fluent nHibernate. The query should return all employees that are older than 50 years. In some page I found the DateProjections class which I attach below.

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

        public static IProjection DateDiff(
            string datepart,
            Expression<Func<object>> startDate,
            Expression<Func<object>> endDate)
        {
            // Build the function template based on the date part.
            string functionTemplate = string.Format(DateDiffFormat, datepart);

            return Projections.SqlFunction(
                new SQLFunctionTemplate(NHibernateUtil.Int32, functionTemplate),
                NHibernateUtil.Int32,
                Projections.Property(startDate),
                Projections.Property(endDate));
        }
    }

The function to get employees is the following:

public List<EmployeeAgeViewModel> GetEmployeesOlderThan50()
        {
            Person personAlias = null;
            EmployeeAgeViewModel result = null;

            var temp = _session.QueryOver<Employee>().JoinQueryOver(x => x.Person, () => personAlias).SelectList(
                list => list
                .Select(x => personAlias.FirstName).WithAlias(() => result.FirstName)
                .Select(x => personAlias.LastName).WithAlias(() => result.LastName)
                .Select(x => x.Gender).WithAlias(() => result.Gender)
                .Select(x => x.BirthDate).WithAlias(() => result.BirthDate)
                .Select(x => x.HireDate).WithAlias(() => result.HireDate)
                .Select(DateProjections.DateDiff("yy", () => personAlias.Employee.BirthDate, () => DateTime.Now)).WithAlias(() => result.Age)
                )
                .TransformUsing(Transformers.AliasToBean<EmployeeAgeViewModel>())
                .List<EmployeeAgeViewModel>();

            return temp.ToList();

The problem is probably the way I am passing BirthDate property and DateTime.Now to DateDiff function. In the personAlias variable the Employee property is null - maybe I should assign it somehow before - any help will be appreciated.

Is BirthDate a property of Employee or Person? If it's really a property of Employee I suggest that you use an employeeAlias by doing this: _session.QueryOver<Employee>(() => employeeAlias) and DateProjections.DateDiff("yy", () => employeeAlias.BirthDate — Florian Lim
– Florian Lim, Commented Mar 27, 2019 at 9:06
Yes, perfect answer! The employee alias in parenthesis was missing. I spent a lot of time digging what may be wrong. Thanks a lot! — zari
– zari, Commented Mar 28, 2019 at 17:35
I put my comment into an answer, so that your question doesn't appear as unanswered. — Florian Lim
– Florian Lim, Commented Mar 28, 2019 at 18:27

Florian Lim · Accepted Answer · 2019-03-28 18:27:12Z

Instead of personAlias.Employee.BirthDate you need to use an employeeAlias.

Your code with the necessary changes looks like this:

Person personAlias = null;
Employee employeeAlias = null;
EmployeeAgeViewModel result = null;

var temp = _session.QueryOver<Employee>(() => employeeAlias)
    .JoinQueryOver(x => x.Person, () => personAlias)
    .SelectList(
        list => list
        .Select(x => personAlias.FirstName).WithAlias(() => result.FirstName)
        .Select(x => personAlias.LastName).WithAlias(() => result.LastName)
        .Select(x => x.Gender).WithAlias(() => result.Gender)
        .Select(x => x.BirthDate).WithAlias(() => result.BirthDate)
        .Select(x => x.HireDate).WithAlias(() => result.HireDate)
        .Select(DateProjections.DateDiff("yy", () => employeeAlias.BirthDate, () => DateTime.Now))
            .WithAlias(() => result.Age)
        )
    .TransformUsing(Transformers.AliasToBean<EmployeeAgeViewModel>())
    .List<EmployeeAgeViewModel>();

return temp.ToList();

Collectives™ on Stack Overflow

How to use datediff sql function in nHibernate?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related