CODEWITHSUNDEEP
c#ormlite-servicestack
David Klempfner
David Klempfner

Reputation: 9940

SqlExpression Creating Upper SQL Query

I have the following code:

SqlExpression<Postcodes> sqlExpression = db.From<Postcodes>()
                    .Where(x => x.CityId.StartsWith(searchString))
                    .OrderBy(x => x.CityId)
                    .ThenBy(x => x.ZipCode)
                    .Take(take);

When I look in debug, I can see that sqlExpression.BodyExpression has:

FROM "search"."Postcodes"
WHERE upper("CityId") like @0

Why does it generate "upper" even though I didn't use x.CityId.ToUpper()?

Update: When I use "nort" as the input, I can see this in the debug output:

2018-11-23 07:37:22,781 [21] DEBUG ServiceStack.OrmLite.OrmLiteReadCommandExtensions [(null)] - SQL: SELECT TOP 100 "ZipCode", "CityId", "StateId" 
FROM "search"."Postcodes"
WHERE upper("CityId") like @0
ORDER BY "CityId", "ZipCode"
PARAMS: @0=NORT%

Looks like by default it's using case insensitive matching.

Why is the default case insensitive and how could I force it to do a case sensitive match?

Update: As Gabitu pointed out, it just makes the column name upper case and doesn't affect the query. What is the point in making the column name upper case?

Upvotes: 0

Views: 216

Answers (1)

Gabitu
Gabitu

Reputation: 179

I think it is doing Upper to your column name, so it wouldn't affect your query at all.

The query will be something like the following:

SELECT * 
FROM "search"."Postcodes" 
WHERE CITYID like @0
ORDER BY "CityId"

Probably is making it uppercase because your column is case insensitive. Check whether your column is sensitive or insensitive with the following command:

SELECT COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
   TABLE_NAME = 'Postcodes' AND 
   CHARACTER_SET_NAME IS NOT NULL AND 
   TABLE_SCHEMA = 'search'

Upvotes: 1

Related Questions