System.Data.SQLite

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).

<hr /><i>anonymous claiming to be fastcat@gmail.com added on 2011-07-19 20:04:56 UTC:</i><br />
I believe this is because StartsWith is case sensitive, but in many (most?) database engines, LIKE is case insensitive, and so cannot be used to evaluate the StartsWith condition.

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.

<hr /><i>mistachkin added on 2011-07-24 22:12:33 UTC:</i><br />
Could you provide a specific example that returns incorrect results?