R – Should I tell NHibernate/FNH to explicitly use a string data type for a param mapped to a string column

fluent-nhibernatenhibernate

A cohort of mine is building a somewhat long search query based on various input from the user. We've got NHibernate mapped up using Fluent NHibernate, and aside from some noob mistakes, all seems to be going well.

One issue we can't resolve in the case of this search is that for a particular parameter, NHibernate is creating sql that treats the input as int when we explicitly need it to be a string. We have a string property mapped to an nvarchar(255) column which mostly contains integer numbers, excluding some arbitrary inputs like "temporary" or long numbers like 4444444444444444 which is beyond the int limit.

In the course of testing, I've seen a couple things: 1) If I prepend a 0 to the incoming value, NH generates the sql param as a string, appropriately so; 2) If the value can realistically be converted to an int, the resulting sql treats it as so. In case #2, if I run the generated sql directly through sql server, I get an exception when the query comes across an non-integer value (such as the examples I listed above). For some reason, when I just let NH do it's thing, I'm getting appropriate records back, but it doesn't make sense; I would expect it to fail or at least tell me that something is wrong with some records that can't be evaluated by SqlServer.

The mapping is simple, the data store is simple; I would be ok leaving well enough alone if I at least understood why/how NHibernate is making this work when running the same state manually fails… Any thoughts?

Best Answer

Are you running the exact same code directly into SQL Server?

NHibernate parameterises all of its queries, and will in doing so define what value is passed through to SQL in the parameters. Which is probably what you're asking about, the reason SQL my fail, is that by default it will only know the difference if you input:

select * from table_name
   where col_name = 5

in comparison with 

select * from table_name
   where col_name = '5'

If you do not define it as a string with the 's it will search for an int, and try to convert all the varchar's to ints, which will obviously fail in some cases with strings.

Related Topic