SetMaxResults in DetachedCriteria Subquery

[Tyler Burd]

I’m using the NHibernate trunk, along with PostgreSQL 8.3.  I’m having an issue when querying via DetachedCriteria with SetMaxResults applied to both the DetachedCriteria and a subquery within the criteria.

 

Here’s an example:

----

//this is the main criteria

var crit1 = DetachedCriteria.For<Organization>();

crit1.SetMaxResults(1);

 

//this is the sub criteria, used in a subquery

var subc = DetachedCriteria.For<Organization>();

subc.SetProjection(Projections.Property("Name"));

subc.AddOrder(Order.Desc("Name"));

subc.SetMaxResults(1);

 

//add the sub-criteria as a subquery to the main criteria

crit1.Add(Subqueries.Eq("Some Organization Name", subc));

----

This generates sql like this:

SELECT this_.OrganizationId as Organiza1_1_0_, this_."name" as name2_1_0_ FROM Organization this_

WHERE :p0 = (SELECT this_0_."name" as y0_ FROM Organization this_0_ ORDER BY this_0_."name" desc limit :p1) limit :p2

 

Notice the two limit clauses (one in the subquery and one in the parent query). However, when this query is executed I receive the following error:

 

Npgsql.NpgsqlException: ERROR: 42601: syntax error at or near ":"

 

I can give you much more information, but this is already a long email.  The problem surfaced when I tried to use Ayende’s Rhino.Security with Postgres.  Specifically, the AddPermissionsToQuery method on the AuthorizationService crashes in this manner, and I tracked it down to the subquery issue.

 

Thanks,

 

-tyler

[Tuna Toksöz]

What would be the correct SQL? I am not a postgresql guy but may have a hand to help.

--
Tuna Toksöz

Typos included to enhance the readers attention!

[Fabio Maulo]

Change the implementation of the dialect with this

public virtual bool SupportsVariableLimit

{

get { return false; }

}

public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) 
{ 
SqlStringBuilder pagingBuilder = new SqlStringBuilder(); 
pagingBuilder.Add(querySqlString); 
pagingBuilder.Add(" limit " + limit); 

if (offset > 0) 
{ 
pagingBuilder.Add(" offset " + offset); 
} 

return pagingBuilder.ToSqlString(); 
} 

It is a temporary workaround for NH2.0.1

2008/11/11 Tyler Burd <tb...@cudc.org>

--
Fabio Maulo

[Sean Fuhrmann]

My DBA wants to have SQL Server default “Set NoCount On”, which does not allow the NHibernate expectations of inserts\updates to work.

 

Is there a way to have NHiberante call “Set NoCount Off”, or to have it disable the expectation stuff?

 

I am using NH 1.2.1

 

Thanks everyone!

 

-Sean

[Ayende Rahien]

You can provide your own IConnectionProvider, which will do that.

[Sean Fuhrmann]

Awesome, thanks!

[Sean Fuhrmann]

Also, I’m assuming by “will do that”, is to call “Set NoCount Off” ?