Pachu Pachu - 2 months ago 50x
C# Question

NHibernate - Converting DateTime to String inside select throws exception

I'm using NHibernate and I have a query in which im trying to select the datetime and convert it to string:

DateDTO dateDto = null;
CultureInfo ci = CultureInfo.CreateSpecificCulture("he-IL");

var dates = _session.QueryOver<Date>()
.Where(x => x.Client.Id == clientId)
.Where(x => x.IsVisible != 0)
.SelectList(lst => lst
.Select(x => x.Id).WithAlias(() => dateDto.Id)
.Select(x => x.DateTime.ToString("dddd dd MMMM yyyy",ci)).WithAlias(() => dateDto.Date))

Which throws the following exception:

variable 'x' of type 'Form2.Entities.Date' referenced from scope '', but it is not defined

What am I doing wrong?


TL;DR: NHibernate translates QueryOver queries into SQL. It does not know how to convert the following expression into SQL:

Select(x => x.DateTime.ToString("dddd dd MMMM yyyy",ci))

Which is why you get the error. You have a few options:

  1. Do some post-processing on the results to get the desired date format. This is the easiest fix. You could write something like this:

    var dates = session.QueryOver<Date>()
        .Where(x => x.IsVisible != 0)
        .SelectList(lst => lst
            .Select(x => x.Id)
            .Select(x => x.DateTime))
        .Select(o => new DateDTO 
            Id = (int)o[0],
            Date = ((DateTime)o[1]).ToString("dddd dd MMMM yyyy", ci) 
  2. Write a custom SQL function to format the date on the database side. This is a little more work, but the end result might look a little cleaner. This implementation will depend on your SQL dialect. This example uses the FORMAT function, available on SQL Server 2014:

    ISQLFunction formatFunction =
        new SQLFunctionTemplate(NHibernateUtil.String, "FORMAT(?1, 'dddd dd MMM yyy', 'he-IL')");
    DateDTO dateDto = null;
        .Where(x => x.IsVisible != 0)
        .SelectList(lst => lst
            .Select(x => x.Id).WithAlias(() => dateDto.Id)
                Projections.Property<Date>(x => x.DateTime))
            ).WithAlias(() => dateDto.Date))

    This generates the following SQL:

        this_.Id as y0_,
        FORMAT(this_.DateTime, 'dddd dd MMM yyy', 'he-IL') as y1_ 
        Date this_ 
        not (this_.IsVisible = @p0);

    You may not even need to create a custom function if the NHibernate dialect you're using already supports it.

    I have a blog post about using SQL functions inside of your queries, if you're interested in going that route. (Full disclosure: this is my personal blog).