Given

Following query would produce unexpected SQL (for Firebird):

C#:
var items = session.Query<TestEntity>()
.Where(i => i.Name.Substring(0, 4) == "Item")
.ToArray();

SQL:

select
testentity0_.Id as Id0_,
testentity0_.Name as Name0_
from
TestEntity testentity0_
where
substring(testentity0_.Name from @p0+1 for @p1)=@p2;

@p0 = 0 [Type: Int32 (0)],
@p1 = 4 [Type: Int32 (0)],
@p2 = 'Item' [Type: String (4)]

And an exception occurs ("expression evaluation not supported"):

NHibernate.Exceptions.GenericADOException: could not execute query [ select testentity0_.Id as Id0_, testentity0_.Name as Name0_ from TestEntity testentity0_ where substring(testentity0_.Name from @p0+1 for @p1)=@p2 ] Name1 - Value:0 Name2 - Value:4 Name3 - Value:Item [SQL: select testentity0_.Id as Id0_, testentity0_.Name as Name0_ from TestEntity testentity0_ where substring(testentity0_.Name from @p0+1 for @p1)=@p2] ---> FirebirdSql.Data.FirebirdClient.FbException: expression evaluation not supported ---> FirebirdSql.Data.Common.IscException: expression evaluation not supported
at FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ProcessResponse(IResponse response) in c:\Users\Jiri\Documents\devel\NETProvider\working\trunk\NETProvider\source\FirebirdSql\Data\Client\Managed\Version10\GdsDatabase.cs:line 647
. . .

Expected

Firebird expected query that look like this:

select
testentity0_.Id as Id0_,
testentity0_.Name as Name0_
from
TestEntity testentity0_
where
substring(testentity0_.Name from cast(coalesce(@p0+1, 1) as Integer) for @p1)=@p2;

@p0 = 0 [Type: Int32 (0)],
@p1 = 4 [Type: Int32 (0)],
@p2 = 'Item' [Type: String (4)]

Fix

For example, change FirebirdDialect:

[Serializable]
public class FirebirdSubstringFunction : ISQLFunction
{
public bool HasArguments
{
get
{
return true;
}
}

public bool HasParenthesesIfNoArguments
{
get
{
return true;
}
}

public IType ReturnType(IType columnType, IMapping mapping)
{
return NHibernateUtil.String;
}

public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
if (args.Count < 2 || args.Count > 3)
{
throw new QueryException("substring(): Incorrect number of parameters (expected 2 or 3, got " + args.Count + ")");
}

var sqlStringBuilder = new SqlStringBuilder();

sqlStringBuilder.Add("substring(").AddObject(args[0]).Add(" from cast(coalesce(").AddObject(args[1]).Add(", 1) as Integer)");

if (args.Count > 2)
{
sqlStringBuilder.Add(" for ").AddObject(args[2]);
}

sqlStringBuilder.Add(")");

return sqlStringBuilder.ToSqlString();
}
}

Additionally

• Firebird-support: http://tech.groups.yahoo.com/group/firebird-support/message/121020?var=1&l=1

• Related issue:

• GitHub discussion: https://github.com/nhibernate/nhibernate-core/pull/89