How to make a Nhibernate Case query using QueryOver?

Question

I have a simple database with a user table, it has a simple administrator with

UserName = "Admin"
Password = "admins"

I am using NHibernate to query this table in the login form. Suppose that the login form is inserted with UserName="ADMIN" and password="ADMIN" as in uppercase.

The system should not allow login. However, when I use such a query,

 using (var session = NhibernateHelper.OpenSession()) { return new List<User> (session.QueryOver<User>() .Where(u => u.UserName == userName) .And(u => u.Password == password) .Future()); }} 

The system ignores case sensitivity and selects the user. So how can I make the request sensitive <? >

Answer 1

We can directly specify COLLATE as part of the SQL column evaluation

 session .QueryOver<User>() // expecting that user name could be any case // if not, we can use the same as for password below .Where(u => u.UserName == userName) // instead of this //.And(u => u.Password == password) .And(Expression.Sql(" Password = ? COLLATE Latin1_General_CS_AS" , password, NHibernateUtil.String)); .Future() ; 

In the above statement, Latin1_General_CS_AS will be used, where CS means: case sensitive and AS code. Emphasis sensitive

In addition, there is some project of the custom expression LikeExpression, which can consume the COLLATE string as a constant or from an installation:

• Nhibernate QueryOver sort without hard coded column name

Answer 2

Another approach, not QueryOver, but LINQ:

 session.Query<User>().Where(u => SqlMethods.Like(u.Username, "something")).ToList(); 

Answer 3

Or, with the criteria:

 session.CreateCriteria(typeof(User), "u").Add(Restrictions.Like(Projections.Property("u.Username"), "something")).List<Username>(); 

Answer 4

Finally, QueryOver:

 session.QueryOver<User>().Where(Expression.Sql("Username LIKE ?", "something", NHibernateUtil.String)).List<User>()