In NHibernate how do I add a Replace to an Restrictions.Eq?

Question

I want to add a Restrictions.Eq with a TSQL replace surrounding the column name. How would I do that?

string location = "Columbus OH";

var requestQuery = Session.CreateCriteria<Request>();
requestQuery.Add(Restrictions.Eq("Replace(LocationName,',','')", location);

you will need to register the function `Replace` or using a SQL expression, see here for more details http://stackoverflow.com/questions/1706259/nhibernate-filtering-by-user-defined-function-output — Rippo, Dec 18 '13 at 16:15

score 2 · Accepted Answer · answered Dec 19 '13 at 12:46

One possible way is to go with the SqlProjection. An example:

string location = "Columbus OH";

var session = NHSession.GetCurrent();
var query = session.CreateCriteria<Request>();
query.Add(Restrictions
    .Eq( // SQL Server function call
        Projections.SqlProjection(
            "Replace(LocationName,',','') as Replacement"
            , new[] {"Replacement"}
            , new IType[] {NHibernateUtil.String})
        , location // searched value
    ));
var list = query.List<Request>();

In NHibernate how do I add a Replace to an Restrictions.Eq?

1 Answers1

Linked