LINQ to SQL & SQLite - Searching in UTF-8 string

[habipoguz]

I am programming an "Articles Archieve" contains articles about law. For this, I am using Linq to SQL and SQLite. And authors' names contain UTF-8 characters. When I search "oğuz" term in the search textbox, it returns KARABULUT, Ayşe/EMİR, Oğuz - PİLOT, Oğuz/GÜNAŞTI, Emin - AYDOST, Oğuz Ali - İNAL, Oğuz Mehmet. But it does not find and does not return OĞUZ, Habip - OĞUZOĞLU, Ali - OĞUZ, Kemal - OĞUZMAN, Cemallettin.

In the same way, when I search "OĞUZ" term in the search textbox, it returns same datas as "oğuz" term. Here is my code:

readonly LtoSQliteDataContext _db = new LtoSQliteDataContext();
IQueryable<article> results = (from m in _db.articles
where m.author.ToLower().Contains(textBox1.Text.ToLower())
select m).Distinct();

And this is SQL "article" table information:

CREATE TABLE [article] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [title] VARCHAR(300), [author] VARCHAR(100) COLLATE NOCASE, [info] VARCHAR(300), [file] VARCHAR(50), [lesson] VARCHAR(50), [created] DATETIME, [updated] DATETIME);

[MariiaI]

If we understood you correctly, you are expecting to get all record with the 'oğuz' occurrence (case-insensitive).
Most likely, the issue is related to this aspect( https://www.sqlite.org/lang_corefunc.html ):
lower(X) The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU (https://www.sqlite.org/src/artifact?ci= ... README.txt) extension.

[habipoguz]

Yeah, exactly. Thank you very much for your reply. Here is my solution:

Code: Select all

List<article> temp= (from m in _db.articles
                       join k in _db.keywordss on m.id equals k.aid                                   
                       select m).Distinct().ToList();

  List<article> result= temp.Where(l => CultureInfo.CurrentCulture.CompareInfo.IndexOf(l.author, textBox1.Text) >= 0) ).ToList();

[MariiaI]

Thank you for the reply and sharing your solution for such case.
If you'll encounter any futher issues with dotConnect for SQLite/LinqConnect, feel free to contact us.