I'm somewhat new to NHibernate, but I've looked all around and can't figure out what's going wrong. I'm running NHibernate 1.2, and connecting to a MSSQL 2005 db.

I need to inject a custom SQL criterion into an otherwise normal ICriteria.List() call, and provide a parameter value.
It seems like NHibernate.Expression.SQLCriterion is designed for exactly this, but it always gives me a "Incorrect syntax near '?'." exception, as though it's just not parsing out the "?" param, or not creating the command param to go with it. The positional param is being included, because I can see it in the exception log, but SQL doesn't know what to do with that "?"

What's going wrong here, or what alternative method should I be using?

This is all split out into some abstract classes and methods, so in this context I just have a single method to return a list of ICriterions that will be added to another ICriteria.

Here is the code for that method:
The "_sqlText" value is a piece of a WHERE clause from a config setting that does a simple LIKE comparison.


Thanks.

First of all, if its just a simple like why would you "inject" it as pure SQL instead of using ICriteria's functionality for like queries?

And secondly if its because you want to apply some general clauses to a lot of criterias perhabs you should look at filters(parameterized) in nHibernate


But to address your issue you would be better of using:

Expression.Sql(_sqlText, paramValues, paramTypes);

The problem with your approach is that constructing an SqlString object with multiple parts can not be done using the constructor, it accepts a single part. So you should use SqlString.Parse(_sqlText) to get a correct instance of SqlString but the approach above is much more readable.

I'm not able to use the built-in ICriteria functionality because of the way the code calling it is written. There are a few abstract layers that don't know enough about what they're calling, so we had to just go with a SQL criterion in a couple of places.

Thanks for your help. It worked perfectly. I had seen Expression.Sql() in a few places in the documentation, but I was getting it confused with the NHibernate.Expression namespace, so I could never find a NHibernate.Expression.Sql() method.

I looked a little harder after your response and found NHibernate.Expression.Expression.Sql(), and it works exactly the way I need.

Thanks again.

Hi,

I got a question using expression?, if i have a datetime datatype in my DB and a value of 2008-11-23 00:00:00, and wanted to search only for 2008-11-23. I used Expression.eq("DateField", 2008-11-23).

It does'nt return any thing. How do i resolve this?