Marcos Vinicius Marcos Vinicius - 1 year ago 135
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download