NHibernate createQuery with colons from method cal

Question

I get a Antlr.Runtime.NoViableAltException thrown when trying to run the following query in NHibernate.

IQuery query = session.CreateQuery("from <table> where 1 in (select <column>.STIntersects(geography::STGeomFromText('POINT(:longitude :latitude)', 4326)))");

I'm setting longitude and latitude in setQuery.

my assumption is the :: in calling geography::STGeomFromText... because it's thinking it's a query parameter. Is there a way to escape the :?

This query works in my SQL Manager Studio.

Thanks!

Answer 1

See this post for some more info: http://stackoverflow.com/questions/238050/using-sql-server-2008-geography-types-with-nhibernates-createsqlquery/650451#650451

Answer 2

Does NHIbernate support the STIntersects method ?

What you could do, is let NHibernate execute a (native) SQL query, like this:

ISQLQuery query = session.CreateSQLQuery ("your sql statement goes here");
query.AddEntity (typeof(TheEntityTypeThatYouWant));

var result = query.List<TheEntityTypeThatYouWant>();

Answer 3

Just to share what I ended up doing was taking the ADO connection out of the NHibernate session, performing a SqlCommand directly and manually built my Model objects from the results. It sucks, but it works. The connection still managed by NHibernate correctly.