Search with inequalities on string property

user_472942 0
Viewed 62

We are using NHibernate over SQL Server and SQLite. The database stores records in rows rather than columns -- each row having Path|Value as columns. Path and Value are both string properties.

For certain values of Path we would like to query for inequalities -- greater-than, less-than, etc.

The trouble we are having is that because the properties are strings, the inequalities are using string comparisons -- for example, searching for Value >= 18 returns rows where Value = 5. Unfortunately we are having trouble working around this.

1) This restriction produces incorrect results (saying 18 < 5):

Restrictions.Ge("Value", item.Value);

2) We tried casting the value to an integer, but this code produces a SqlException from NHibernate -- Error converting data type nvarchar to bigint.

Restrictions.Le(Projections.Cast(NHibernateUtil.Int64, Projections.Property("SearchString")), item.Value)

3) We were looking for a way to pad the property value with zeros (so that we would get 018 > 005), but could not find a way to do this in NHibernate.

Does anyone have any advice?
Thank you in advance!

nhibernate
1 Answers

Assuming that you want to compare on integer value, with IQueryOver:

1) This restriction produces incorrect results (saying 18 < 5):

Restrictions.Ge("Value", item.Value);

Restrictions.Ge
    (
    Projections.Cast(NHibernateUtil.Int32, Projections.Property<YourEntity>(x => x.YourProperty))
        , intValue
    )

Convert your datatype accordingly. If your C# datatype (intValue) is already numeric, no need to convert it. If your x.YourProperty is already numeric type, no need to convert it. Adjust above code accordingly.

2) We tried casting the value to an integer, but this code produces a SqlException from NHibernate -- Error converting data type nvarchar to bigint.

Restrictions.Le(Projections.Cast(NHibernateUtil.Int64, Projections.Property("SearchString")), item.Value)

Refer the above and check the datatype of item.Value.

user_57797320
Related