Default "query.substitutions" per Dialect
Description
Environment
None
Activity
Show:

Fabio Maulo April 17, 2010 at 10:58 PM
The NHibernate's session-factory configuration is "per-dialect"
Won't Fix
Details
Details
Assignee
Unassigned
UnassignedReporter

Components
Affects versions
Priority
Who's Looking?
Open Who's Looking?
Created July 30, 2008 at 11:07 AM
Updated April 17, 2010 at 10:58 PM
Resolved April 17, 2010 at 10:58 PM
Who's Looking?
Here's my code:
CurrentSession.CreateQuery(
@"FROM Opportunity o WHERE :foo = true")
.SetBoolean("foo", true)
.List<Opportunity>();
Then I run it against an MSSQL2005 database and get an error, here's what the profiler sees:
exec sp_executesql N'select ... from Opportunity opportunit0_ where (@p0=true )',N'@p0 bit',@p0=1
As you can see, 1 = true is the problem, since MSSQL doesn't know about boolean constants. I know that I can work this around if I add the proper substitutions to the configuration, but the problem is that it's hacking and simply doesn't belong there, it's a dialect issue. NHibernate correctly realizes that in MSSQL's dialect, the parameter foo with a true value is correctly transformed into a 1 constant, so the same should apply to the true constant, it's exactly the same issue. My suggestion: when running a query with MSSQL dialect, automatically apply the true=>1 and false=>0 substitution. Without that, there's no way that a query like that would ever succeed, so it would be a safe default. If there are boolean issues in other dialects, then the same applies there too. It should be dealt with at the dialect level, not the main configuration. Now that would be a consistent boolean handling, without the need for the developer to work around dialect issues at all, that's why I use NHibernate in the first place.