NHibernate 2.0 changes direct sql query adding the sql function ROW_NUMBER()

[Garfield]

Hi,

I have a sql query that I am running using the CreateSQLQuery of
NHibernate. It use to work perfectly when I was on NHibernate 1.2, but
since I have upgraded to 2.0 I am getting the SqlException error
(0x80131904) Ambiguous column name on __hibernate_sort_expr_0__.

On checking the logs I saw the query that NHibernate actually runs has
the sql ROW_NUMBER() function. Now I was able to solve the issue by
specifying the rows explicitly instead of using ".*", but I would not
want every direct sql query run through NHibernate to use Row_Number
().

Does anyone has any idea how I can avoid this, or why this is
happening ?

Thanks,
- AB

Here is a simplified version of the above problem:

SQL Query:
SELECT BDTC.* from BDTC INNER JOIN Drug ON BDTC.DrugId = Drug.DrugId
WHERE BDTC.BookId = 2

This is what NHibernate runs (from its logs):
SELECT TOP 5 * FROM (SELECT ROW_NUMBER() OVER(ORDER BY
__hibernate_sort_expr_0__) as row, query.*,
query.__hibernate_sort_expr_0__ FROM (SELECT BDTC.*, CURRENT_TIMESTAMP
as __hibernate_sort_expr_0__ from BDTC INNER JOIN Drug ON BDTC.DrugId
= Drug.DrugId WHERE BDTC.BookId = 2) query ) page WHERE page.row > 0
ORDER BY __hibernate_sort_expr_0__

which throws this error in sql:
Msg 8156, Level 16, State 1, Line 1
The column '__hibernate_sort_expr_0__' was specified multiple times
for 'page'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name '__hibernate_sort_expr_0__'.

This is how I am calling the CreateSQLQuery:
ISQLQuery sqlQuery = NHibernateSession.CreateSQLQuery(sql).AddEntity
(alias, type);

[Fabio Maulo]

2009/2/11 Garfield <ame...@gmail.com>

This is how I am calling the CreateSQLQuery:
ISQLQuery sqlQuery = NHibernateSession.CreateSQLQuery(sql).AddEntity
(alias, type);

Please say the true. That is not what you are doing... you are setting something else.

Thanks.

Fabio Maulo

[Garfield]

! That is what I am doing.

In the sql query I also had a orderby NEWID(), which was the main
reason for using direct sql, as I did not find a way to do NEWID using
NHibernate. But even after removing that line, the resulting query
that NHibernate generated was the above.

Also in the method where I call the CreateSQLQuery I have:
sqlQuery.SetMaxResults(5);

- AB

[Fabio Maulo]

2009/2/11 Garfield <ame...@gmail.com>

 Also in the method where I call the CreateSQLQuery I have:
sqlQuery.SetMaxResults(5);

As I said, what you wrote in the first post, was not the only thing you are doing...

Ok.

You are using MsSQL2005Dialect and you are setting limits for your query.

The Dialect are managing limits in some way (it has less bugs in trunk).

If you don't like MsSQL2005Dialect you can inherits from it and change it or you can use the MsSQL2000Dialect.

BTW ... in the second post you are talking about NEWID() and in the query you sent in the first post there is not a "NEWID".

If you need help, please, try to be more clear as possible.

Thanks.

Fabio Maulo

[Garfield]

Thanks Fabio for pointing me towards the Dialect. Yes I did change the
dialect from 2000 to 2005 when I upgraded from 1.2 to 2.0. Still don't
understand why you would need Row_Number in SQL 2005 to put limits on
a query. Anyways, my point of using the 2005 Dialect was so that I can
upgrade eventually to SQL 2008 server.

Also, I mentioned New_Id in my second post since I thought New_Id
would probably cause NHibernate to do a Row_Number, but even after
removing that I was still getting Row_Number in NHibernate logs. Hence
I said the "simplified version" in the first post.

Anyways, so this brings up two questions:
1. Can I use 2000Dialect with SQL server 2008 ? Also it would be great
if you can point me towards some documentation on the Dialects.
2. Can I use New_Id without using Direct sql from NHibernate ?

- AB

[Fabio Maulo]

2009/2/11 Garfield <ame...@gmail.com>

Anyways, so this brings up two questions:
1. Can I use 2000Dialect with SQL server 2008 ? Also it would be great
if you can point me towards some documentation on the Dialects.

yes you can. 

2. Can I use New_Id without using Direct sql from NHibernate ?

Your experience is a demonstration that answer is: "It depend".

BTW if you try to check the implementation of the 3 MsSQL dialects on trunk you can easy implement your own.

--
Fabio Maulo

[Garfield]

Great thanks a ton Fabio.

NHibernate is an awesome tool, best ORM I have come across, and just
want to commend on the awesome work that you all have put in.

Regards,
- AB