Marcos Vinicius Marcos Vinicius - 6 months ago 23
SQL Question

query using function “SUBSTR” of Firebird

I need to write a NHibernate query using the firebird

SUBSTR
function with , or . I may do it with or
SQL
, 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:

SELECT *
FROM LANCAMENTO
WHERE SUBSTR(LAN_CD_CONTA, 1, 13) = :paramCd_Conta

Answer

With Linq, declare a SubStr extension method:

using NHibernate.Linq;

...

public static class CustomLinqExtensions
{
    [LinqExtensionMethod("SUBSTR")]
    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:

session.Query<Lancamento>()
    .Where(l => l.CdConta.SubStr(1, 13) == cdConta)
    .ToList();

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.

Comments