Charles Wyandt - 184 - Jan 31, 2015

Hello, I’m having an issue with the documentation of OrmLite. How can I create a case-insensitive select for a string without using Contains, StartsWith, or EndsWith?

Fredrick Lackey:

Wouldn’t this be dependent on the database? For example, if you’re making calls against a SQL Server default collection, the collection is generally care insensitive. I’m assuming ORM Lite is simply going to send whatever you give it.

Fredrick Lackey:

On a side note, I originally found some oddities using the Equals keyword in some queries and reverted to simply ==.

It should already be case-insensitive by default? You can revert to case-sensitive of underlying RDBMS with: OrmLiteConfig.StripUpperInLike=true

Contains/StartsWith/EndsWith is the typed API to perform LIKES queries with the typed SqlExpression. If you don’t want to use it you can append your own custom SQL:

q.Where(“Name LIKE {0}”, “value”);

Or use any of OrmLite API’s that allow executing custom SQL.

Charles Wyandt:

I am pretty sure you may have already answered this part- but I am referring to an example such as db.Select(e => e.Name == “bob”), I want to allow it to also select “Bob” in that example. (No “Like” involved, just a case-insensitive comparison)

An == expression generates a case sensitive query, i.e:

WHERE (“Name” = ‘bob’)

If it’s returning case-insensitive results then it’s due to the collation configured on your DB/Table, see:

Charles Wyandt:

I’m asking how to make a case-insensitive query without reverting to custom SQL, so refactoring can be used.

You can change the collation on your Table/Column. Or just use toUpper/toLower

q.Where(x => x.Name.ToUpper() == “bob”.ToUpper())

Charles Wyandt:

Ah, that works. I wasn’t sure if ToUpper would carry through to the expression.