How to get results that begin with a number with NHibernate?

Question

I have a table with a Name field containing data similar to the following:

a1
a2
b1
c1
1a
1b
2a
9b

I'm trying to select only the rows values that start with a number.

I'm trying to do this with the following query, but it doesn't work:

var numberGroups = _session
    .CreateCriteria<CompanyGroupInfo>()
    .Add(SqlExpression.Between<CompanyGroupInfo>(g => int.Parse(g.Name.Substring(0, 1)), 0, 9))
    .List<CompanyGroupInfo>();

It's throwing the error:

Unrecognised method call in epression Parse(g.Name.Substring(0, 1)) note the 'expression' typo - that's NHibernate, not me :-)

Can somebody suggest how I can achieve the result I'm looking for? Thanks

edit: I tried Jon's suggestions below, as well as SqlExpression.Not<CompanyGroupInfo>(g => !char.IsLetter(g.Name.Substring(0, 1).ToCharArray()[0])) but they all throw similar errors to what I've posted above.

Answer 1

I'm not an NHibernate guru by any means, but have you tried:

SqlExpression.Between<CompanyGroupInfo>(g => g.Name.Substring(0, 1), "0", "9")

or

SqlExpression.Between<CompanyGroupInfo>(g => g.Name[0], '0', '9')

?

Answer 2

It turns out this works:

var numberGroups = _session
    .CreateCriteria<CompanyGroupInfo>()
    .Add(SqlExpression.Not<CompanyGroupInfo>(/* where other criteria doesn't apply*/)
    .List<CompanyGroupInfo>()
    .Where(xx => char.IsDigit(xx.Name.Substring(0, 1).ToCharArray()[0]));

But I'm afraid it might be inefficient because I get the full result set into a list, and then filter the list. I'm open to other suggestions, if they're available.

Answer 3

Can you get away with a naive solution?

_session.CreateQuery("from CompanyGroupInfo where Name < 'a'")
    .List<CompanyGroupInfo>();

The results depend on the database collation order and the Name data containing only letters and numbers.

Answer 4

IMO, this is a better fit for HQL than Criteria.

A variation on Jamie's answer:

_session.CreateQuery(
        "from CompanyGroupInfo where substring(Name, 1, 1) between '0' and '9'")
        .List<CompanyGroupInfo>();

Anyway, if you really, really want to use Criteria... here it goes:

_session.CreateCriteria<CompanyGroupInfo>()
        .Add(Restrictions.Between(
             Projections.SqlFunction("substring",
                                     NHibernateUtil.String,
                                     Projections.Property("Name"),
                                     Projections.Constant(1),
                                     Projections.Constant(1)),
             "0", "9"))
        .List<CompanyGroupInfo>();