NHibernate Criterion and formula

Question

I have a setup where I have a table with two fields, FirstName and LastName. To do a search in the name, I have made a formula which contains FirstName + " " + Lastname.

But if I try to do the following statement:

Restrictions.InsensitiveLike("empl.FullNameFormula", "% " + restriction.PersonName + " %")

I receive the error:

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

But if I try to do an equal instead of a like it works:

 Restrictions.Eq("empl.FullNameFormula", "% " + restriction.PersonName + " %")

Is there an error with formula and trying to do an insensitive like?

I am using NHibernate Version 2.1.2.4000.

Update: My stacktrace looks like this:

    [SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1951450
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849003
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2394
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() +12
   NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) +216
   NHibernate.Impl.MultiCriteriaImpl.GetResultsFromDatabase(IList results) +310
   NHibernate.Impl.MultiCriteriaImpl.List() +348
   NHibernate.Impl.FutureCriteriaBatch.GetResultsFrom(IMultiCriteria multiApproach) +10
   NHibernate.Impl.FutureBatch`2.GetResults() +88
   NHibernate.Impl.FutureBatch`2.get_Results() +16
   NHibernate.Impl.FutureBatch`2.GetCurrentResult(Int32 currentIndex) +52
   NHibernate.Impl.<>c__DisplayClass4`1.<GetEnumerator>b__3() +53
   NHibernate.Impl.<get_Enumerable>d__0.MoveNext() +73
   System.Linq.Buffer`1..ctor(IEnumerable`1 source) +259
   System.Linq.Enumerable.ToArray(IEnumerable`1 source) +81
   xxx.DataAccess.PublicationRepository.Search(PublicationQuery restriction) in xxxx :197
   layouts_www_xxx.RunSearch() in xxx:100
   layouts_www_xxx.OnInit(EventArgs e) in xxxx :39
   System.Web.UI.Control.InitRecursive(Control namingContainer) +333
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Control.InitRecursive(Control namingContainer) +210
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +378

Answer 1

I would see what the actual SQL is being generated, you mention that this is part of a much larger search...

So based on this I would either:-

a) Fire up SQL Profiler and see the SQL that is being generated and run this in SQL Management Studio

b) Download NHProf and grab the SQL and run in SQL Management Studio

c) Use Log4Net to output the generated SQL and run in SQL Management Studio

I am assuming this is a MsSQL server, if not use MySql Workbench.

This really does smell of a SQL database problem and not a NHibernate problem. Without first checking the SQL you could be barking up the wrong tree.

HTH

Answer 2

You probably have an index on FirstName that is helping the first query, while the second one is doing an expensive table scan.