Nhibernate + QueryOver: filter with Where ignoring sensitive

Question

I am trying to build a simple query in nHibernate with QueryOver but I want it to convert everything lower-case or ignore sensitive:

Domain.User User = Session.QueryOver<Domain.User>()
       .Where(x=>x.Login=="username")
       .SingleOrDefault();

How can I achieve this?

UPDATE:

Someone suggested that the problem could be with the colletion of the DB but I've never had any kind of problem with that and this script works:

Domain.User User = Session
    .CreateCriteria<Domain.User>() 
    .Add(Expression.Eq("Login", "username")) 
    .UniqueResult<Domain.User>();

What is your database server and default database collation? — Darius Kucinskas, Mar 09 '11 at 12:36

score 17 · Accepted Answer · answered Mar 09 '11 at 12:05

17

In QueryOver you can use following:

Domain.User User = Session.QueryOver<Domain.User>()
       .WhereRestrictionOn(x=>x.Login).IsInsensitiveLike("username")
       .SingleOrDefault();

answered Mar 09 '11 at 12:05

Sly

15,046
12
60
89

how could you explain the fact that the CreateCriteria works properly? – LeftyX Mar 10 '11 at 08:47
By default SQL server makes case insensitive strings comparison, but can be configured differently. ICriteria doesn't add anything in SQL it will just generate where Login = 'username'. Can't say it about QueryOver. You should check what SQL it produces. Insensitive like will cause ToLower call in SQL – Sly Mar 10 '11 at 10:52
I just ran into a problem with solution. In this case, the username had an underscore character in it, and Oracle treats the underscore as a wildcard. So I had both `bob.marley` and `bob_marley` in the database and the `SingleOrDefault` crashed. – Greg Oct 06 '14 at 15:28
6

Careful - allowing user input to be a LIKE expression is very prone to being a big security hole. NB, the OP did *not* ask for a Like, but for equality (which would make sense) – Ruben Bartelink Jul 14 '15 at 09:45
You should probably read question carefully before putting "valuable" comments – Sly Jul 16 '15 at 08:41
This logic is not the same as the question requires. Be careful with that "like". – Th3B0Y Feb 07 '17 at 15:58

score 6 · Answer 2 · edited Apr 11 '13 at 08:42

6

my workaround for this is using a expression.eq combined with a projection, so a case insensitive equals without any magic strings can be done with queryover

query.Where(Expression.Eq(Projections.Property(Of MyType)
                (Function(x) x.Name), "something").IgnoreCase)

edited Apr 11 '13 at 08:42

bonCodigo

14,268
1
48
91

answered Apr 11 '13 at 08:20

Epicycle

71
1
7

1

The exact syntax above didn't work. Here is what worked for me: ```query.Where(Restrictions.Eq(nameof(MyClass.Property), value).IgnoreCase());``` – katrash Sep 17 '19 at 22:34

score 2 · Answer 3 · answered Mar 09 '11 at 11:33

2

Better way is to change collation of your database to case insensitive one. If you can change database.

answered Mar 09 '11 at 11:33

Darius Kucinskas

10,193
12
57
79

Darius, I don't know if it is a problem with the collection cause CreateCriteria works properly:Domain.User User = Session.CreateCriteria() .Add(Expression.Eq("Login", "username")) .UniqueResult(); – LeftyX Mar 09 '11 at 12:05
1

@LeftyX - If your collation is case insensitive you don't have to specify case sensitivity in your query (all queries will be case insensitive by default). – Darius Kucinskas Mar 09 '11 at 12:08
You're right Darius but changing collation in Oracle is not as easy as in Sql Server and I can't do it cause I am not the owner of the DB. – LeftyX Mar 09 '11 at 14:02
np ;) - as I wrote 'If you can change database...' – Darius Kucinskas Mar 09 '11 at 14:25

score 1 · Answer 4 · edited Jul 17 '18 at 15:57

public static class QueryOverExtension
{
    /// <summary>
    /// This method is used in cases where the root type is required
    /// Example: .WhereEqualInsensitive(t => t.Property, stringValue)
    /// </summary>
    public static IQueryOver<T, TU> WhereEqualInsensitive<T, TU>(this IQueryOver<T, TU> queryOver, Expression<Func<T, object>> path, string value)
    {
        return queryOver.Where(Restrictions.Eq(Projections.SqlFunction("upper", NHibernateUtil.String, Projections.Property(path)), value.ToUpper()));
    }

    /// <summary>
    /// This method is used in cases where the root type is NOT required
    /// Example: .WhereEqualInsensitive(() => addressAlias.DefaultEmail, contactEmailAddress)
    /// </summary>
    public static IQueryOver<T, TU> WhereEqualInsensitive<T,TU>(this IQueryOver<T, TU> queryOver, Expression<Func<object>> path, string value)
    {
        return queryOver.Where(Restrictions.Eq(Projections.SqlFunction("upper", NHibernateUtil.String, Projections.Property(path)), value.ToUpper()));
    }
}

Usages:

Session.QueryOver<DTO>()
           .WhereEqualInsensitive(t => t.Property, value)

ChildDTO childAlias = null;
Session.QueryOver<DTO>()
           .JoinAlias(t => t.ChildDTO, () => childAlias)
           .WhereEqualInsensitive(() => myAlias.Property, value)

Sanja Melnichuk · Answer 5 · 2011-03-09T11:10:15.547

0

NH 3.0 has a linq provider so you can use

Session.Query<Domain.User>()
           .Where(x=>x.Login.ToLower() =="username")
           .SingleOrDefault();

edited Mar 09 '11 at 11:10

answered Mar 09 '11 at 10:42

Sanja Melnichuk

3,465
3
25
46

I've already tried that. It doesn't work. I get this error: {"Unrecognised method call in epression x.Login.ToLower()"} . could it be cause I am using oracle? – LeftyX Mar 09 '11 at 10:47
@LeftyX what version nhibernate you have? – Sanja Melnichuk Mar 09 '11 at 10:47
I've checked and the behavior is the same in Sql Server 2008: Unrecognised method call in epression x.Login.ToLower() – LeftyX Mar 09 '11 at 11:00
@LeftyX Confirm not working with QueryOver but working with linq to nhibernate. All i can to recomend you its use linq for nhibernate. its all ok with linq in version 3.0 – Sanja Melnichuk Mar 09 '11 at 11:07
It's strange cause if I create a critiria is case insensitive (and works): Domain.User User = Session.CreateCriteria() .Add(Expression.Eq("Login", "username")) .UniqueResult(); – LeftyX Mar 09 '11 at 11:45

Nhibernate + QueryOver: filter with Where ignoring sensitive

5 Answers5

Linked