CreateCriteria Contains Restriction

Question

I am using Criteria API for querying and want to query on a column that contains Full Text index.

But then in Criteria API I have not been able to successfully find CONTAINS.

Is there a way to use CONTAINS in Criteria? I used CreateQuery and was able to get it working but wanted to know if there is a way to get it done using CreateCriteria.

Any pointers towards the same is highly appreciated as I have been searching for this from quite some time.

Answer 1

We can generate custom SQL parts, with Criterion.Expresssion.Sql. (There is an example how to)

This could be the way in our case

var searchedValue = "searched value";

var session = ... // get session 

// dynamic way to construct some SQL part
var criterion = NHibernate.Criterion.Expression
    .Sql(" CONTAINS ({alias}.LastName, ? ) ",
        new object[] { searchedValue},
        new IType[] { NHibernate.NHibernateUtil.String});

// our criteria
var criteria = session.CreateCriteria<MyEntity>();

// with our FULLTEXT filter
criteria.Add(criterion);

// result..
var list = criteria
    .SetFirstResult(0) // paging
    .SetMaxResults(10)
    .List<MyEntity>();