|Title:||CHARINDEX SQL generated for "String.Contains, String.StartsWith" LINQ method calls|
|Last Modified:||2011-09-07 10:32:52|
|Version Found In:||188.8.131.52|
Linq query: t=>t.someString.StartsWith("foo") is translated to CHARINDEX sql instead of expected LIKE 'foo%'. This has several impacts: 1) Performance, 2) It simply doesn't work at least for Cyrillic characters, it returns some random like results that makes no sense.
I don't know in which version it appeared, I remember it since I started using SQLite (more than year ago), than i swtiched to other DB, now i'm back and the bug is still present -)
I don't know why but this is quite popular solution to use CHARINDEX instead of LIKE, I seen such behavior in other RDBMS LINQ providers, the only one provider that worked correctly was Npgsql (Postgre SQL provider).
anonymous claiming to be firstname.lastname@example.org added on 2011-07-19 20:04:56 UTC:
In PostgreSQL, LIKE is case sensitive by default (and it has ILIKE for case insensitive matching), so using LIKE for StartsWith is correct there, but would not be correct for SQLite.
SQLite has a pragma for setting the case sensitivity of LIKE (http://www.sqlite.org/pragma.html#pragma_case_sensitive_like), but it would seem presumptuous of the LINQ provider to assume, much less modify the value of that pragma.
mistachkin added on 2011-07-24 22:12:33 UTC: