Hi All,

I'm exploring upgrading from 1.04 to 1.2RC1 I've got most things working but I am having a few issues with the following code:

ICriteria c = sesion.CreateCriteria(typeof(Child));
c.CreateAlias("Parent", "p");
string sql = " p.Name like 'Name' escape '\\'";
c.Add(Expression.Sql(sql));
Assert.AreEqual(1, c.List().Count);

This gives the error:
System.Data.SqlClient.SqlException: The multi-part identifier "p.Name" could not be bound.

However, the following code works fine:
ICriteria c = session.CreateCriteria(typeof(Child));
c.CreateAlias("Parent", "p");
c.Add(Expression.Like("p.Name", "Name"));
Assert.AreEqual(1, c.List().Count);

I do need escape char features tho.

Does anyone know what I can do to fix this?


Thanks in advance

Guido Tapia


Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
1.2 CR1
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
NHibernate.ADOException: could not execute query
[ SELECT this_.[RiskAssessmentTypeID] as RiskAsse1_16_1_, this_.[ChangedDate] as ChangedD2_16_1_, this_.[CompanyID] as CompanyID3_16_1_, this_.[RiskID] as RiskID4_16_1_, this_.[AssessmentTypeID] as Assessme5_16_1_, this_.[ProbabilityWithExistingID] as Probabil6_16_1_, this_.[ConsequenceWithExistingID] as Conseque7_16_1_, this_.[ProbabilityWithRecommendedID] as Probabil8_16_1_, this_.[ConsequenceWithRecommendedID] as Conseque9_16_1_, alias0x1_.[RiskID] as RiskID1_10_0_, alias0x1_.[DateCreated] as DateCrea2_10_0_, alias0x1_.[CreatedByName] as CreatedB3_10_0_, alias0x1_.[IdentifiedBy] as Identifi4_10_0_, alias0x1_.[RiskName] as RiskName5_10_0_, alias0x1_.[RiskDescription] as RiskDesc6_10_0_, alias0x1_.[Frequency] as Frequency7_10_0_, alias0x1_.[TimesPer] as TimesPer8_10_0_, alias0x1_.[FrequencyExplanation] as Frequenc9_10_0_, alias0x1_.[Outcome] as Outcome10_10_0_, alias0x1_.[OutcomeExplanation] as Outcome11_10_0_, alias0x1_.[OutcomeCost] as Outcome12_10_0_, alias0x1_.[RiskStatusChangedDate] as RiskSta13_10_0_, alias0x1_.[MgmtComments] as MgmtCom14_10_0_, alias0x1_.[AssessmentContext] as Assessm15_10_0_, alias0x1_.[DateLastUpdated] as DateLas16_10_0_, alias0x1_.[CompanyID] as CompanyID17_10_0_, alias0x1_.[CompanyLayerID] as Company18_10_0_, alias0x1_.[StepID] as StepID19_10_0_, alias0x1_.[RiskStatusID] as RiskSta20_10_0_ FROM [RegisterRiskAssessmentType] this_ inner join [RegisterRisk] alias0x1_ on this_.[RiskID]=alias0x1_.[RiskID] WHERE (this_.[CompanyID] is not null and this_.[CompanyID] = ?) and (alias0x1_.[Outcome] is not null and alias0.Outcome like '%_Outcome%' escape '\') ]
Positional parameters: 0 Company:753 (_Test Company)
[SQL: SELECT this_.[RiskAssessmentTypeID] as RiskAsse1_16_1_, this_.[ChangedDate] as ChangedD2_16_1_, this_.[CompanyID] as CompanyID3_16_1_, this_.[RiskID] as RiskID4_16_1_, this_.[AssessmentTypeID] as Assessme5_16_1_, this_.[ProbabilityWithExistingID] as Probabil6_16_1_, this_.[ConsequenceWithExistingID] as Conseque7_16_1_, this_.[ProbabilityWithRecommendedID] as Probabil8_16_1_, this_.[ConsequenceWithRecommendedID] as Conseque9_16_1_, alias0x1_.[RiskID] as RiskID1_10_0_, alias0x1_.[DateCreated] as DateCrea2_10_0_, alias0x1_.[CreatedByName] as CreatedB3_10_0_, alias0x1_.[IdentifiedBy] as Identifi4_10_0_, alias0x1_.[RiskName] as RiskName5_10_0_, alias0x1_.[RiskDescription] as RiskDesc6_10_0_, alias0x1_.[Frequency] as Frequency7_10_0_, alias0x1_.[TimesPer] as TimesPer8_10_0_, alias0x1_.[FrequencyExplanation] as Frequenc9_10_0_, alias0x1_.[Outcome] as Outcome10_10_0_, alias0x1_.[OutcomeExplanation] as Outcome11_10_0_, alias0x1_.[OutcomeCost] as Outcome12_10_0_, alias0x1_.[RiskStatusChangedDate] as RiskSta13_10_0_, alias0x1_.[MgmtComments] as MgmtCom14_10_0_, alias0x1_.[AssessmentContext] as Assessm15_10_0_, alias0x1_.[DateLastUpdated] as DateLas16_10_0_, alias0x1_.[CompanyID] as CompanyID17_10_0_, alias0x1_.[CompanyLayerID] as Company18_10_0_, alias0x1_.[StepID] as StepID19_10_0_, alias0x1_.[RiskStatusID] as RiskSta20_10_0_ FROM [RegisterRiskAssessmentType] this_ inner join [RegisterRisk] alias0x1_ on this_.[RiskID]=alias0x1_.[RiskID] WHERE (this_.[CompanyID] is not null and this_.[CompanyID] = ?) and (alias0x1_.[Outcome] is not null and alias0.Outcome like '%_Outcome%' escape '\')] ---> System.Data.SqlClient.SqlException: The multi-part identifier "alias0.Outcome" could not be bound.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListUsingQueryCache(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria, IList results)
at NHibernate.Impl.CriteriaImpl.List()
at PicNet.DAL.FindAllHelper.FindAllImpl(FinderParameters fp, ISession session) in C:\dev\Projects\RiskShield\RiskShield42\PicNetDAL\PicNet\DAL\FindAllHelper.cs:line 64
at PicNet.DAL.DataFacade.FindAllImpl(FinderParameters fp) in C:\dev\Projects\RiskShield\RiskShield42\PicNetDAL\PicNet\DAL\DataFacade.cs:line 405

Name and version of the database you are using:
SQL Server 2005

The generated SQL (show_sql=true):
SELECT this_.[RiskAssessmentTypeID] as RiskAsse1_16_1_, this_.[ChangedDate] as ChangedD2_16_1_, this_.[CompanyID] as CompanyID3_16_1_, this_.[RiskID] as RiskID4_16_1_, this_.[AssessmentTypeID] as Assessme5_16_1_, this_.[ProbabilityWithExistingID] as Probabil6_16_1_, this_.[ConsequenceWithExistingID] as Conseque7_16_1_, this_.[ProbabilityWithRecommendedID] as Probabil8_16_1_, this_.[ConsequenceWithRecommendedID] as Conseque9_16_1_, alias0x1_.[RiskID] as RiskID1_10_0_, alias0x1_.[DateCreated] as DateCrea2_10_0_, alias0x1_.[CreatedByName] as CreatedB3_10_0_, alias0x1_.[IdentifiedBy] as Identifi4_10_0_, alias0x1_.[RiskName] as RiskName5_10_0_, alias0x1_.[RiskDescription] as RiskDesc6_10_0_, alias0x1_.[Frequency] as Frequency7_10_0_, alias0x1_.[TimesPer] as TimesPer8_10_0_, alias0x1_.[FrequencyExplanation] as Frequenc9_10_0_, alias0x1_.[Outcome] as Outcome10_10_0_, alias0x1_.[OutcomeExplanation] as Outcome11_10_0_, alias0x1_.[OutcomeCost] as Outcome12_10_0_, alias0x1_.[RiskStatusChangedDate] as RiskSta13_10_0_, alias0x1_.[MgmtComments] as MgmtCom14_10_0_, alias0x1_.[AssessmentContext] as Assessm15_10_0_, alias0x1_.[DateLastUpdated] as DateLas16_10_0_, alias0x1_.[CompanyID] as CompanyID17_10_0_, alias0x1_.[CompanyLayerID] as Company18_10_0_, alias0x1_.[StepID] as StepID19_10_0_, alias0x1_.[RiskStatusID] as RiskSta20_10_0_ FROM [RegisterRiskAssessmentType] this_ inner join [RegisterRisk] alias0x1_ on this_.[RiskID]=alias0x1_.[RiskID] WHERE (this_.[CompanyID] is not null and this_.[CompanyID] = ?) and (alias0x1_.[Outcome] is not null and alias0.Outcome like '%_Outcome%' escape '\')

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

Pure SQL expressions aren't parsed, that's what they're for. If "Name" is an unambiguous column name, you can use "Name" without the leading "p.". Otherwise you can rephrase your query so that the main criteria is a Parent. When Name is ambiguous in an SQL block like the one you've written, hibernate will assume that it's from the main table you're querying, and prefix the column name with the corrent alias.

The only other solution is to manually escape the strings in your app, before putting them into the criteria.

Thanks for the reply.

Your first suggestion is very hard for me to implement because unfortunatelly the code I pasted is actually part of a 'query generator' and hence quite complex to rearrange queries at run time depending on aliases and so forth.

Ideally I would like to use your second suggestion The only other solution is to manually escape the strings in your app, before putting them into the criteria.

However, is there a way to do this in a database independent fashion? The beuaty of specifying the escape character is that it is db independent. I have not being able to find a way of specifying this character using Expression.Like.

PS: Why did this use to work in 1.04? Were Sql expressions being parsed?

Guido Tapia

Don't know why this used to work for you.

I have no idea how easy the NHibernate implementation is to extend, but this is what I'd do in Hibernate. Maybe it's feasible for you.

Create a class that extends org.hibernate.criterion.SimpleExpression. called EscapedLikeExpression. You can copy (most of) the code from IlikeExpression into it. You only have to change the toSqlString method, which now puts the "escape '\\'" bit in the appropriate place. You can now use this class whereever you used to use Restrictions.like().

Done,

Code below for anyone interested. No performance degradation when compared to 'InsensitiveLikeExpression' (small 100 iteration test on SQL Server).

Thanks for the suggestion tenwit.

Guido Tapia

public class EscapedInsensativeLikeExpression : InsensitiveLikeExpression
{
// May want to pass the escape char in the constructor?
public EscapedInsensativeLikeExpression( string propertyName, object value ) : base (propertyName, value) {}
public EscapedInsensativeLikeExpression(string propertyName, string value, MatchMode matchMode) : base (propertyName, value, matchMode) {}

public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary enabledFilters)
{
SqlString ss = base.ToSqlString(criteria, criteriaQuery, enabledFilters);
SqlStringBuilder sqlBuilder = new SqlStringBuilder(ss);
sqlBuilder.Add(" escape '\\'");
return sqlBuilder.ToSqlString();
}
}