I'm trying to use the SQL function CONSTAINS
to filter some data on QueryOver API.
The main issue is i can't use SqlFunction
in where clause, it does not compile, because a ICriterion
is needed.
var result = Session.QueryOver<Individual>()
.Where(Projections.SqlFunction(
"FullTextContains", NHibernateUtil.Boolean,
Projections.Property<Individual>(x => x.LastName),
Projections.Constant("something")))
.List();
I tried to match it to a TRUE
constant, but when the query is executed it generates syntax error, because CONSTAINS
function can't be used with equals operator.
var result = Session.QueryOver<Individual>()
.Where(Restrictions.Eq(Projections.SqlFunction(
"FullTextContains", NHibernateUtil.Boolean,
Projections.Property<Individual>(p => p.LastName),
Projections.Constant("something")), true))
.List();
How can i use a boolean sql function directly in where expression on QueryOver API?
This is my finding for letting QueryOver
support it:
var projection = Projections.SqlFunction("FullTextContains",
NHibernateUtil.Boolean,
Projections.Property<Individual>(x => x.LastName),
Projections.Constant("something"));
var result = Session.QueryOver<Individual>()
.Where(new ProjectionAsCriterion(projection))
.List();
To use a IProjection
as a ICriterion
I created my own implementation based on SimpleExpression
class from NHibernate project.
public class ProjectionAsCriterion : AbstractCriterion
{
private readonly IProjection _projection;
public ProjectionAsCriterion(IProjection projection)
{
_projection = projection;
}
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery,
IDictionary<string, IFilter> enabledFilters)
{
var columnNames = CriterionUtil.GetColumnNamesForSimpleExpression(
null, _projection, criteriaQuery, criteria, enabledFilters, this, string.Empty);
var sqlBuilder = new SqlStringBuilder(4 * columnNames.Length);
for (int i = 0; i < columnNames.Length; i++)
{
if (i > 0)
{
sqlBuilder.Add(" and ");
}
sqlBuilder.Add(columnNames[i]);
}
return sqlBuilder.ToSqlString();
}
public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery)
{
var typedValues = new List<TypedValue>();
if (_projection != null)
{
typedValues.AddRange(_projection.GetTypedValues(criteria, criteriaQuery));
}
typedValues.Add(GetParameterTypedValue(criteria, criteriaQuery));
return typedValues.ToArray();
}
private TypedValue GetParameterTypedValue(ICriteria criteria, ICriteriaQuery criteriaQuery)
{
return CriterionUtil.GetTypedValues(criteriaQuery, criteria, _projection, null).Single();
}
public override IProjection[] GetProjections()
{
return new[] { _projection };
}
public override string ToString()
{
return _projection.ToString();
}
}