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);
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);
}
}