Using SQL Convert Function Through Nhibernate Criterion

Using in-built sql Convert function in nhibernate criteria

If you could accept the CAST instead of CONVERT (And I am almost sure that you can), then there is more straightforward solution.

Instead of calling "SQL Server related" function, let's call the abstraction, which should be working on most DB Servers (based on supported NHibernate dilacts)

Projections.Cast(NHibernateUtil.String, Projections.Property(searchCol))

So the Restriction used in a WHERE clause could look like this:

Restrictions
    .Like (
        Projections.Cast(NHibernateUtil.String, Projections.Property(searchCol))
        , "2009"
        , MatchMode.Anywhere
    )

And the result generated by NHibernate, using the SQL Server dialect would be:

WHERE cast( this_.theColumn as NVARCHAR(255)) like @p1 ... @p1=N'%2009%'

Using SQL CONVERT function through nHibernate Criterion

If the setting 104 is not essential, we can get a quick solution: use CAST instead of CONVERT. This SQL Function is built-in in the NHibernate dialects:

Projections.Cast(NHibernateUtil.DateTime
               ,Projections.Property(propNames[orderByColumn]))

If the setting 104 is important we can create our own Dialect, register the CONVERT function, and use it from then ... forever

Here Andrew Whitaker nicely shows how to

• Nhibernate count distinct (based on multiple columns)

Need help converting SQL to criteria API

this might do the job (NH 1.2):

var crit = nhSes.CreateCriteria(typeof(Revision), "r")
 .SetProjection(
  Projections.SqlProjection(@"r.RevisionId as rid,
    COALESCE(lp1.Title, lp2.Title, lp3.Title) as Title,
    COALESCE(lp1.Description, lp2.Description, lp3.Description) as Description", new[] {"rid", "Title", "Description"}, new[] {NHibernateUtil.Guid, NHibernateUtil.String,NHibernateUtil.String})
 );

crit.CreateCriteria("LocalizedProperty", "lp1", JoinType.InnerJoin);
crit.CreateCriteria("LocalizedProperty", "lp2", JoinType.InnerJoin);
crit.CreateCriteria("LocalizedProperty", "lp3", JoinType.InnerJoin);

crit.Add(Expression.Eq("lp1.LanguageId", langId));
crit.Add(Expression.Sql("lp2.LanguageId = LEFT(:LangId, 2)", langId, NHibernateUtil.String));
crit.Add(Expression.EqProperty("lp3.LanguageId", "r.DefaultPropertiesLanguage"));

note however that this does not generate ANSI syntax join but places the constraints in a WHERE clause. I don't really think that's a problem, you are doing an inner join and not an outer join.

Also i don't remember now the proper parameter notation on Expression.Sql.
I've defined it as :LangId although it may also be @LangId

sidenote: as you can see although this is a criteria query its just a bunch of sql-statements split so to fit the criteria API; are you sure that this is what you need?

NHibernate select column with SqlFunction, group by same criteria

You can nest the dateGroupBy value with another Projections.Group method.

var dateGroupBy = Projections.Group(Projections.SqlFunction("date", NHibernateUtil.Date,
Projections.Group<Domain.Locate>(g => g.WorkToBeginDate)));

That should give you the desired results. :)

NHibernate Query that simulates SQL Replace Function

I ended up using the Criteria query with a SQL Restriction. It was something like this

Session.CreateCriteria<EntityName>()
.Add(Restrictions.Sql(
"Replace('ColumnToSearch','PatternToFind', 'ReplaceWithValue')"))

The Sql Restriction lets you run the exact same sql statement as you give it.

---

Related Topics