NHibernate QueryOver restrict by string length

Question

How do I restrict a query by the length of a string property? eg. something like:

NHSession.QueryOver<Customer>()
    .Where(p => p.RegistryCode.Length == 8)

It would be nice if you add a few more details to your question. — Jost, Aug 28 '13 at 13:05
Don't know what additional detail I could add, it seems a pretty straightforward question to me? — dänjel, Aug 28 '13 at 13:30
yes your question is not too heavy ;-) - but it contains things like `customer` and a constant (e.g. `8`), which are not further explained. Don't worry it's ok as it is AND you got your answer. — Jost, Aug 28 '13 at 13:36

Claudio Redi · Accepted Answer · 2013-08-28T13:32:24.670

5

Something like this may do the trick

NHSession.QueryOver<Customer>()
    .Where(
        Restrictions.Eq(
            Projections.SqlFunction("length", NHibernateUtil.String, 
                Projections.Property<Customer>(x => x.RegistryCode)),
            8
        )
    )

edited Aug 28 '13 at 13:32

answered Aug 28 '13 at 12:50

Claudio Redi

67,454
15
130
155

Thanks, worked, although I had to use "length" instead of "len", as per [this answer](http://stackoverflow.com/questions/9246626/nhibernate-support-the-function-len) – dänjel Aug 28 '13 at 13:29

score 1 · Answer 2 · edited Jul 11 '17 at 10:06

1

Instead of "NHibernateUtil.String" i should use this "NHibernateUtil.Int16" type, because a 'length' parameter should always be a number and not a String.

Something like this:

NHSession.QueryOver<Customer>()
    .Where(
        Restrictions.Eq(
            Projections.SqlFunction("length", NHibernateUtil.Int16, 
                Projections.Property<Customer>(x => x.RegistryCode)),
            8
        )
    )

edited Jul 11 '17 at 10:06

Alexander

2,925
3
33
36

answered Jun 21 '17 at 10:22

Joachim Langezaal

195
1
2
13

Why should this be better? Please explain. – Alexander Jun 21 '17 at 11:00
In my opinion, a length return a int not a string. But it's a nhibernate sql action it does not really matter. – Joachim Langezaal Jul 10 '17 at 12:18

NHibernate QueryOver restrict by string length

2 Answers2