Bug in NHibernate Aliasing

Question

I am using NHibernate for my database management. In one class I am calculating a property using this formula:

(SELECT MIN(x.timestamp) FROM (SELECT MAX(r.Timestamp) AS timestamp, r.Meter_Id FROM Reading r, Meter m WHERE r.Meter_Id = m.Id AND m.Store_Id = Id GROUP BY r.Meter_Id) AS x)

The generated query looks like this:

(SELECT MIN(x.timestamp) FROM (SELECT MAX(r.Timestamp) AS timestamp, r.Meter_Id FROM Reading r, Meter m WHERE r.Meter_Id = m.Id AND m.Store_Id = this_.Id GROUP BY r.Meter_Id) AS this_.x)

Obviously the name in the AS statement is renamed to this_.x, which causes the error.

It seems to be a known bug: NHibernate JIRA #NH-2878

Does enyone have a solution for this?

Answer 1

I could be mistaken, but I don't actually see why you need the alias in that particular formula.

In a more general sense, you have a few options:

• Work around the issue. You could use a stored procedure, or load more data and do the calculation in-memory.
• Fix it. NHibernate is open source -- pull down the code, find the cause, isolate it in a test, fix it, and send a pull request.
• Get someone else to fix it. If your company is using NHibernate and this is important to them, they could probably sponsor another NHibernate contributor to implement a fix.

Answer 2

I had this same issue when calling a database function from within a Fluent NHibernate Map.Formula() method. My workaround was to repeat the full function name instead of trying to use an ailas.

For example, where EntityColumn2 is a column that is already being referenced\loaded into the entity.

SELECT My_Db_Function.Column1
FROM My_Db_Function(arg1, arg2, arg3, ...) 
WHERE My_Db_Function.Column2 = EntityColumn2

The result is that the My_Db_Function references stay as is (and are not aliased by NHibernate) and the EntityColumn2 is correctly aliased by NHibernate.