Add support for SqlFunction restrictions

[hazzik]

I would prefer more generic solution which allows for a sql-function restriction. Something like Restrictions.SqlFunction("xxx", "a", "b", "c").

Originally posted by @hazzik in https://github.com/nhibernate/nhibernate-core/issues/2573#issuecomment-715140433

[bahusoid]

I would prefer more generic solution which allows for a sql-function restriction

And I would prefer more "more generic solution" which allows for any projection restriction. Something like Restrictions.Projection(Projections.SqlFunction(...

[hazzik]

Not sure that I understand.

[hazzik]

Ok, it seems I understood... Unfortunately it cannot be done easily. It is a related problem, but not a solution for the original issue. The problem is that there are two types of booleans in SQL: predicates (TRUE/FALSE) and data types (BIT, for ex). There are well known conversions between them

• from predicate to data type -- using case statement
• from data type to predicate -- using comparison.

So, Restrictions.Projection would need to be translated into some comparison expression: Projections.SqlFunction("a", "b", "c") = 1. But it does not solve the author's original problem because CONTAINS (SQL Server) and match (SQLite) are predicative functions and are not comparable. Then we need some sort of logic to determine what kind of function we have.

See your own explanation: https://github.com/nhibernate/nhibernate-core/pull/2573#discussion_r497613458

[bahusoid]

would need to be translated into some comparison expression

No. I imagine Restrictions.Projection should simply generate bare projection in WHERE statement. It's up to projection to be proper in this context.

See your own explanation: https://github.com/nhibernate/nhibernate-core/pull/2573#discussion_r497613458

Restrictions.Projection is just another name for Restricions.Bool from mentioned explanation.

[hazzik]

It's up to projection to be proper in this context.

Ok, lets get this simple example:

session.CreateCriteria<Product>()
  .Add(
    Restrictions.Projection(
      Projections.Property("IsDeleted")))
  .List<Product>()

What would be the result SQL? And which part of the criteria query translates which part of the SQL?

[hazzik]

Or are you saying that this "BoolCriterion" would be implemented just like following?

public class BoolCriterion : AbstractCriterion
{
	private readonly IProjection _projection;

	public BoolCriterion(IProjection projection) =>
		_projection = projection;

	public override string ToString() =>
		_projection.ToString();

	public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery) =>
		CriterionUtil.GetColumnNamesUsingProjection(_projection, criteriaQuery, criteria).Single();

	public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery) =>
		new TypedValue[0];

	public override IProjection[] GetProjections() =>
		new[] { _projection };
}

[bahusoid]

would be implemented just like following

Yeah something like this.. Simply projection generated to SQL without any further modifications.

What would be the result SQL

SELECT * FROM PRODUCT _this WHERE _this.IsDeleted

That's actually something I needed sometime ago for optimizataion purposes as for some reasons SQL Server didn't use index for query generated by default with parameter _this.IsDeleted = @p1 (bool column was part of index)

[hazzik]

SELECT * FROM PRODUCT _this WHERE _this.IsDeleted

But this is not a valid SQL.

[gliljas]

That's actually something I needed sometime ago for optimizataion purposes as for some reasons SQL Server didn't use index for query generated by default with parameter _this.IsDeleted = @p1 (bool column was part of index)

There may be several reasons for that. A very clear scenario is if you're using filtered indexes. They don't work with parameters.

We had that particular scenario in our application, which is using 99% LINQ. A custom rewriter replaced x.IsDeleted == false with a "mock" function, IsFalse(x.IsDeleted). NHibernate's custom LINQ support then replaced that with a solid, nonparametrized _this.IsDeleted = 0. A better way would be something like x.IsDeleted == false.NonParametrized()

[bahusoid]

But this is not a valid SQL.

Indeed it doesn't work with SQL Server but does work with PostgreSQL. It was long time ago - I had to generate non parametrized comparison instead (as suggested by @gliljas) . But it really depends on DB and underlying column type for boolean type. Anyway valid or not it's the SQL I expect to be generated for provided example:

Restrictions.Projection(Projections.Property("IsDeleted"))