Marcos Vinicius Marcos Vinicius - 1 year ago 111
SQL Question

query using function “SUBSTR” of Firebird

I need to write a NHibernate query using the firebird

function with , or . I may do it with or
, but these would be my last options.

Does anyone have any ideas? The following example query would be the SQL resulting from my NHibernate query:

WHERE SUBSTR(LAN_CD_CONTA, 1, 13) = :paramCd_Conta

Answer Source

With Linq, declare a SubStr extension method:

using NHibernate.Linq;


public static class CustomLinqExtensions
    public static string SubStr(this string dummy, int start, int length)
        // No need to implement it in .Net, unless you wish to call it
        // outside IQueryable context too.
        throw new NotImplementedException("This call should be translated " +
            "to SQL and run db side, but it has been run with .Net runtime");

Then use it on your entities:

    .Where(l => l.CdConta.SubStr(1, 13) == cdConta)

Beware, trying to use it without referencing an entity in its usage will cause it to get evaluated with .Net runtime instead of getting it translated to SQL.