How do a case-insenstive Where clause in LINQ-to-NHibernate with the Oracle provider?

Question

For example, given the following code, how is it possible to make the string comparison case-insensitive?

        var query = from employeeGroup in _session.Linq<EmployeeGroup>()
                    from employee in employeeGroup.Employee
                    where employee.UserName == username
                    select employeeGroup.EmployeeGroupName;

The NHibernate.Linq.SqlClient.SqlClientExtensions.Upper() method might help but this only seems to be available with SQL Server.

If I use the System.String class to do the comparison, I get an error: "Cannot use subqueries on a criteria without a projection.". I believe this is because there is no direct mapping that NHibernate can do to SQL.

Answer 1

As far as I can tell, NHibernate Linq does not support case insensitive matching. This isn't a problem for SQL Server which is case insensitive but obviously is for Oracle. I think you'll have to use the Criteria API or HQL for this.

Answer 2

Just for reference, here is a Criteria API example:

session.CreateCriteria(typeof(Customer))
    .Add(new Expression.EqExpression("Firstname", "John", true))
    .List<Customer>();

The Boolean parameter at the end of the EqExpression method determines case-insensitivity.