SQLite like operator doesn't work with special symbols

[dvladimirovich]

I have an app which works with SQLite as data store. There is an issue when I'm using context search. If I use simple phrases BLToolkit converts it in LIKE '%<search string>%' and everything works fine. But I want to use special characters as _ to have an ability do search if I've forgotten what char must be here (e.g. when name is written in different languages). And when I print string like: _ванов in my search field BLToolkit generates next query:
LastQuery.txt
And it uses ESCAPE~ for _ symbol. As a result it can't find anything...
How could I resolve this problem?

[ili]

This by design, by default string.Contains() escapes special symbols, use Sql.Like() extension, it should not escape

[dvladimirovich]

There is a little problem. I'm using layered approach in my app, so UI layer knows nothing about others...
And I pass Expression tree predicate not simple sql

[dvladimirovich]

Is there some flag to turn off escaping? Or could you give me some advice on how to resolve this problem?

[ili]

@dvladimirovich heh... really have no other idea :(

[jogibear9988]

You could rewrite your Expression Tree befor handing over to linq2db. And replace every contains with Sql.like

[dvladimirovich]

Oh, I see...I suspected that I would have to do just that. And I did not want to do this, but I had to...

[jogibear9988]

i think with expressionvisitor it should not be a big task, see https://stackoverflow.com/questions/17728188/rewrite-expression-to-replace-list-contains-with-custom-method

[dvladimirovich]

Yeah, I've already had such experience in rewriting Expression trees using expression visitor=)