NH-4048 - Support non-deterministic/db-side-only methods in Linq

[nhibernate-bot]

Frédéric Delaporte created an issue — 12th July 2017, 14:53:57:

Some methods are non-deterministic and should not be evaluated in the expression tree, once and for all resulting rows, but instead in the database. This is the case for Guid.NewGuid() and Random.Next().

Others may have distinct resulting values depending on the host on which they execute, and can change the query results depending on whether they are evaluated on db-client side or on db-server side. This is the case for DateTime.Now, DateTime.UtcNow, DateTimeOffset.Now, DateTimeOffset.UtcNow.

Currently they are always evaluated by NHibernate Linq provider before emitting the query with the resulting value as a parameter.

They should be left in the expression tree, and translated to appropriate HQL standard functions, then translated to corresponding database functions.

A user wishing to have them evaluated client side will then have to put their value in a variable then use that variable in its query.

By the way, for supporting this feature, when registering a method/property for the linq provider, we should be able to tell if it is partially evaluatable or not.

---

Frédéric Delaporte added a comment — 13th July 2017, 17:38:52:

If doing that, an option must be provided for reverting back to previous behavior. This change can be a major breaking changes, causing many working Linq queries to cease working.

DateTime.Now.WhateverUnRegisteredMethod() is currently working, due to it being evaluated and replaced by its returned value in the query. If DateTime.Now is no more evaluated but replaced by some sql getDate() function, it will then fail on trying to translate the unregistered method call on it.

linqtohql.legacy_preevaluation setting should be set to false with NH 5.3.x in order to enable this new feature. It will likely default to false in 6.0.

[hazzik]

There are a few possible workarounds to get the previous behaviour back:

1. An option which disables the new behaviour globally.
2. A way to (re-)write a query. The suspect functions need to be turned to a parameter explicitly:

//SQL: select * from AThing where StartDate > getdate()
session.Query<AThing>().Where(t=>t.StartDate > DateTime.Now);

//SQL: select * from AThing where StartDate > @p1
var now = DateTime.Now;
session.Query<AThing>().Where(t=>t.StartDate > now);