Contains/StartWith error when using MS SQL SERVER CE (Visual Studio database for developers)
Description
I'm creating unit tests with NHibernete using MS SQL SERVER CE for Visual Studio 2010.
The code below works for SQL SERVER 2008 R2 var test = (from u in Session.Query<User>() where u.UserName.Contains("kow") select u);
But when I switch to SQL SERVER CE i get an error:
NHibernate.SQL: 11:11:42,699 DEBUG SQL:0 - select user0_.IDUser as IDUser7_, user0_.UserName as UserName7_, user0_.Email as Email7_, user0_.Password as Password7_, user0_.CreationDate as Creation5_7_, user0_.LastLoginDate as LastLogi6_7_, user0_.LastPasswordChangedDate as LastPass7_7_, user0_.IsActive as IsActive7_, user0_.IsLockedOut as IsLocked9_7_, user0_.LastLockedOutDate as LastLoc10_7_, user0_.FailedPasswordAttemptCount as FailedP11_7_, user0_.FailedPasswordAttemptStartDate as FailedP12_7_, user0_.IsChangePasswordRequire as IsChang13_7_, user0_.IDApplication as IDAppli14_7_ from Users user0_ where user0_.UserName like (@p0||'%');@p0 = 'kow' [Type: String (0)] NHibernate.AdoNet.AbstractBatcher: 11:11:42,774 ERROR AbstractBatcher:0 - Could not execute query: select user0_.IDUser as IDUser7_, user0_.UserName as UserName7_, user0_.Email as Email7_, user0_.Password as Password7_, user0_.CreationDate as Creation5_7_, user0_.LastLoginDate as LastLogi6_7_, user0_.LastPasswordChangedDate as LastPass7_7_, user0_.IsActive as IsActive7_, user0_.IsLockedOut as IsLocked9_7_, user0_.LastLockedOutDate as LastLoc10_7_, user0_.FailedPasswordAttemptCount as FailedP11_7_, user0_.FailedPasswordAttemptStartDate as FailedP12_7_, user0_.IsChangePasswordRequire as IsChang13_7_, user0_.IDApplication as IDAppli14_7_ from Users user0_ where user0_.UserName like (@p0||'%') There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ] NHibernate.Util.ADOExceptionReporter: 11:11:42,816 WARN ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ] NHibernate.Util.ADOExceptionReporter: 11:11:42,819 ERROR ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ] NHibernate.Util.ADOExceptionReporter: 11:11:42,860 WARN ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ] NHibernate.Util.ADOExceptionReporter: 11:11:42,862 ERROR ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ] NHibernate.Util.ADOExceptionReporter: 11:11:42,867 WARN ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ] NHibernate.Util.ADOExceptionReporter: 11:11:42,869 ERROR ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ]
Environment
None
Activity
Show:
Christian
February 6, 2011 at 12:35 PM
I came across the same issue when using NH 3.0.0 with MSSQL CE 3.5/4.0. The problem is that a wrong string concatenation function is used ('||' operator) but MSSQL CE only understands the '+' operator.
Solution: Override function registration in Dialect class constructor by adding the following line to MsSqlCeDialect constructor:
RegisterFunction("concat", new VarArgsSQLFunction(NHibernateUtil.String, "(", "+", ")"));
Major
I'm creating unit tests with NHibernete using MS SQL SERVER CE for Visual Studio 2010.
The code below works for SQL SERVER 2008 R2
var test = (from u in Session.Query<User>()
where u.UserName.Contains("kow")
select u);
But when I switch to SQL SERVER CE i get an error:
NHibernate.SQL: 11:11:42,699 DEBUG SQL:0 - select user0_.IDUser as IDUser7_, user0_.UserName as UserName7_, user0_.Email as Email7_, user0_.Password as Password7_, user0_.CreationDate as Creation5_7_, user0_.LastLoginDate as LastLogi6_7_, user0_.LastPasswordChangedDate as LastPass7_7_, user0_.IsActive as IsActive7_, user0_.IsLockedOut as IsLocked9_7_, user0_.LastLockedOutDate as LastLoc10_7_, user0_.FailedPasswordAttemptCount as FailedP11_7_, user0_.FailedPasswordAttemptStartDate as FailedP12_7_, user0_.IsChangePasswordRequire as IsChang13_7_, user0_.IDApplication as IDAppli14_7_ from Users user0_ where user0_.UserName like (@p0||'%');@p0 = 'kow' [Type: String (0)]
NHibernate.AdoNet.AbstractBatcher: 11:11:42,774 ERROR AbstractBatcher:0 - Could not execute query: select user0_.IDUser as IDUser7_, user0_.UserName as UserName7_, user0_.Email as Email7_, user0_.Password as Password7_, user0_.CreationDate as Creation5_7_, user0_.LastLoginDate as LastLogi6_7_, user0_.LastPasswordChangedDate as LastPass7_7_, user0_.IsActive as IsActive7_, user0_.IsLockedOut as IsLocked9_7_, user0_.LastLockedOutDate as LastLoc10_7_, user0_.FailedPasswordAttemptCount as FailedP11_7_, user0_.FailedPasswordAttemptStartDate as FailedP12_7_, user0_.IsChangePasswordRequire as IsChang13_7_, user0_.IDApplication as IDAppli14_7_ from Users user0_ where user0_.UserName like (@p0||'%')
There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ]
NHibernate.Util.ADOExceptionReporter: 11:11:42,816 WARN ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ]
NHibernate.Util.ADOExceptionReporter: 11:11:42,819 ERROR ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ]
NHibernate.Util.ADOExceptionReporter: 11:11:42,860 WARN ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ]
NHibernate.Util.ADOExceptionReporter: 11:11:42,862 ERROR ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ]
NHibernate.Util.ADOExceptionReporter: 11:11:42,867 WARN ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ]
NHibernate.Util.ADOExceptionReporter: 11:11:42,869 ERROR ADOExceptionReporter:0 - There was an error parsing the query. [ Token line number = 1,Token line offset = 595,Token in error = | ]