如何使用QueryOver使Nhibernate区分大小写查询? [英] How to make Nhibernate Case sensitive query using QueryOver?

问题描述

我有一个带有用户表的简单数据库，它有一个简单的admin用户，

I have a simple database with users table, it have simple admin user with

UserName ="Admin"
密码="admin"

UserName= "Admin"
Password="admin"

我正在使用NHibernate查询此表以登录表单. 假设登录表单都以大写形式插入了UserName="ADMIN"和password="ADMIN".

I am using NHibernate to query over this table to login form. Suppose the login form inserted UserName="ADMIN" and password="ADMIN" both in upper case.

系统不应允许登录.但是当我使用这样的查询

The system should not allow login. However when I use the query like this

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 the case sensitivity and selects the user. So how can I make case sensitive query?

推荐答案

我们可以直接指定COLLATE作为SQL列评估的一部分

We can specify COLLATE directly as a part of 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()
    ;

以上语句将使用Latin1_General_CS_AS，其中CS表示:区分大小写，AS表示重音

The above statement will use Latin1_General_CS_AS where CS means: Case sensitive and AS means Accent sensitive

此外，有一些自定义 LikeExpression 的草稿，可以将COLLATE字符串用作const或从设置中使用:

Also, there is some draft of a custom LikeExpression, which could consume the COLLATE string as a const or from setting:

• 没有硬编码列名的Nhibernate QueryOver排序规则

这篇关于如何使用QueryOver使Nhibernate区分大小写查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！