nhibernate with a restriction outside of sql

Question

Is it possible to add restriction in nHibernate (version 3.3) that is based on a calculation outside of the database? For example, say someCalculation below calls into some other method in my code and returns a boolean. For the sake of argument, someCalculation() can not be made in the database. Is there a way to get it to work? It's currently throwing and I'm not sure if it's because I am way off or I'm doing something else wrong.

 query.UnderlyingCriteria.Add(Restrictions.Where<MyEntity>(x => someCalculation(x.id));

score 2 · Accepted Answer · edited May 23 '17 at 11:51

The answer is more than to NHibernate related to SQL. Simply, either we will send the result of that computation upfront, before execution - or we will implement such function on DB side. No other mixture of these two is possible.

The first would end up in statement like this

var allowedIds = someCalculation(); // someCalculation(x.id)
query.WhereRestrictionOn(c => c.id).IsIn(allowedIds.ToArray());

In case, that id must be part of calculation, we can firstly load somehow filtered IDs, do the computation, and then execute a second select - similar to above

var ids = session.QueryOver<MyEntity>()
    .Select(c => c.id)
    .List<int>();

var allowedIds = someCalculation(ids); // someCalculation(x.id)

If that is still not effective, the only way is to create a Function on DB side and call it. There is detailed Q & A:

Using SQL CONVERT function through nHibernate Criterion

nhibernate with a restriction outside of sql

1 Answers1