ToLower() / ToUpper() in where clause (where stu.Name.ToLower().Contains(nameFragment)) produces opposite SQL clause to expected

Description

Using Firebird provider the Following test case fails:

The first List Test fails (using ToLower())- the select sql produced was :
select student0_.id as id4_, student0_."Version" as Version2_4_, student0_."Name" as Name3_4_ from "Student" student0_ where upper(student0_."Name") like ('%'||@p0||'%');@p0 = 'woods'

Comment out the first test and the second list test ToUpper() fails - the select sql produced was :
select student0_.id as id4_, student0_."Version" as Version2_4_, student0_."Name" as Name3_4_ from "Student" student0_ where lower(student0_."Name") like ('%'||@p0||'%');@p0 = 'WOODS'

So it appears that when the LINQ expression uses
stu.Name.ToLower().Contains(nameFragment) => sql uses upper
and when when the LINQ expression uses
stu => stu.Name.ToUpper().Contains(nameFragment) => sql uses lower


[TestMethod]
public void ThenLINQCanRetrieveResultsBasedOnPartialStingsCaseInsensitive()
{
var student = new Student()
{
Name = "Tiger Woods"
};

using (var ses = OpenSession())
{
ses.SaveOrUpdate(student);

ses.Flush();
ses.Clear();

// confirm save - this passes
var readStu = ses.Get<Student>(student.ID);
readStu.Name
.CheckEquals("Tiger Woods");

ses.Evict(readStu);

// Test ToLower
var nameFragment = "woods";

var studentList = (ses.Query<Student>()
.Where(stu => stu.Name.ToLower().Contains(nameFragment)))
.ToList<Student>();

/// Fails here count == 0
studentList.Count
.CheckEquals(1);

// Test ToUpper

nameFragment = "WOODS";

studentList = (ses.Query<Student>()
.Where(stu => stu.Name.ToUpper().Contains(nameFragment)))
.ToList<Student>();

/// Fails here count == 0
studentList.Count
.CheckEquals(1);

}
}

Environment

None

Activity

Show:

Andrew A Ritz 
May 4, 2010 at 7:31 AM

I am having the exact same issue, but I am using the Oracle 9i provider. Very annoying.

Details

Assignee

Reporter

Labels

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created January 4, 2010 at 10:47 PM
Updated September 9, 2014 at 1:05 PM
Who's Looking?