Case insensitive like/startswith using Lower by default

[rondelward-pf]

This isn't a big issue per se but I'm curious why Lower is used by default when the basic string filters are not case sensitive in Sql Server

[Fact]
        public void EscapedWhereLike()
        {
            var q = new Query("Table1")
                .WhereLike("Column1", @"TestString\%", false, @"\");
            var c = Compile(q);

            Assert.Equal(@"SELECT * FROM [Table1] WHERE LOWER([Column1]) like 'teststring\%' ESCAPE '\'", c[EngineCodes.SqlServer]);
        }

[Fact]
        public void EscapedHavingStarts()
        {
            var q = new Query("Table1")
                .HavingStarts("Column1", @"TestString\%", false, @"\");
            var c = Compile(q);

            Assert.Equal(@"SELECT * FROM [Table1] HAVING LOWER([Column1]) like 'teststring\%%' ESCAPE '\'", c[EngineCodes.SqlServer]);
        }

The Lower function causes some unnecessary overhead and in some cases even results in a SQL server needing to do an index scan versus an index seek which can hurt performance. I know I can use WhereRaw instead to write the query exactly as I want it but I'm curious if there is an easier way to disable this additional functions in the where clause.

Let me know what you think.
Thanks!

[rondelward-pf]

I started looking into this again. It looks like setting case sensitive to True prevents the LOWER function from being applied in SQL server.

Case Sensitive: true
var query = new Query("Users").WhereContains("FirstName", "test", true)
generates
SELECT *FROM [Users] WHERE [FirstName] like '%test%'

It seems like the case sensitivity aspect is dependent on the default collation used in the database. If the collation used in the db is case sensitive like SQL_Latin1_General_CP1_CS_AS then I believe the compilation works for SQL server.