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:
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
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.