Using SQLite I'm trying to do a case-insensitive search on a string column, however it's only returning results when the case matches the column exactly.
The connection is setup as:
new SQLiteAsyncConnection(_dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache));
I'm not using any pragma's to configure LIKE case sensitivity
The table is defined as:
[Table($"{nameof(Customer)}s")]
public class Customer
{
[PrimaryKey, AutoIncrement]
public long CustomerId { get; set; }
[Collation("NOCASE")]
public string? BusinessName { get; set; }
[Collation("NOCASE")]
public string? Suburb { get; set; }
// ... etc.
}
(After adding the Collation attribute, I dropped and recreated the table).
There are multiple issues with this.
First, my data has about 9 customers in "BRUNSWICK HEADS" (all uppercase thanks to upstream data out of my control).
- Using Linq
var search = "BRUNSWICK";
List<Customer> entities = await _conn.Table<Customer>()
.Where(c =>
c.BusinessName.Contains(search) ||
c.Suburb.Contains(search) ||
c.AddressLine1.Contains(search) ||
c.AddressLine2.Contains(search)
).ToListAsync();
This returns 9 results, but search term and columns are all uppercase.
- Mixed case search-terms. These all return 0 results, indicating that the search is case-sensitive.
search = "BrUNSWICK"; // note lower-case 'r'
entities = await _conn.Table<Customer>()
.Where(c =>
c.BusinessName.Contains(search) ||
c.Suburb.Contains(search) ||
c.AddressLine1.Contains(search) ||
c.AddressLine2.Contains(search)
).ToListAsync();
entities = await _conn.Table<Customer>()
.Where(c =>
c.Suburb.Contains(search)
).ToListAsync();
search = "BrUNSWICK HeADS";
entities = await _conn.Table<Customer>()
.Where(c =>
c.BusinessName.Contains(search) ||
c.Suburb.Contains(search) ||
c.AddressLine1.Contains(search) ||
c.AddressLine2.Contains(search)
).ToListAsync();
entities = await _conn.Table<Customer>()
.Where(c =>
c.Suburb.Contains(search)
).ToListAsync();
- Perhaps it's just a problem with Linq queries... Let's try SQL expressions:
string query = "SELECT * FROM Customers WHERE Suburb = '?'";
entities = await _conn.QueryAsync<Customer>(query, "BrUNSWICK HeADS");
returns 0 results! (It's a problem with the parameter, not = being case-sensitive)
4.
query = "SELECT * FROM Customers WHERE Suburb LIKE '%?%'";
entities = await _conn.QueryAsync<Customer>(query, "BRUNSWICK");
returns 0 results! (It's a problem with the parameter, not LIKE being case-sensitive)
5.
query = "SELECT * FROM Customers WHERE Suburb = '?'";
entities = await _conn.QueryAsync<Customer>(query, "BRUNSWICK HEADS");
An exact match returns no results...
6.
query = "SELECT * FROM Customers WHERE Suburb = 'BRUNSWICK HEADS'";
entities = await _conn.QueryAsync<Customer>(query);
Returns 9 results - so = is working, but parameters aren't?
7.
query = "SELECT * FROM Customers WHERE Suburb LIKE '%BRUNSWICK%'";
entities = await _conn.QueryAsync<Customer>(query);
Returns 9 results - so LIKE is working, but parameters aren't?
The SQL string statements I can live without, so that's not a focus of this question. The only way to get this working is the following:
search = "BrUNSWICK HeADS".ToLower(); // actually from user input!
entities = await _conn.Table<Customer>()
.Where(c =>
c.BusinessName.ToLower().Contains(search) ||
c.Suburb.ToLower().Contains(search) ||
c.AddressLine1.ToLower().Contains(search) ||
c.AddressLine2.ToLower().Contains(search)
).ToListAsync();
This returns 9 results, but now we can't use column indexes.
Can someone please enlighten me?
There is no mention of COLLATE on TwinCoders site (the SQLiteNetExtensions nuget package website)
There is a unit test for sqlite-net-pcl which seems to indicate it's working.
