Using Backslash in filter query

[Dorgaliser]

In our application we allow the users to filter a list of different entities. For example, sites:

Code: Select all

var filteredSites = sites.Where(o => o.Host.StartsWith("\")).ToList();

This results in the following postgreSql query

Code: Select all

SELECT 
"Extent1"."Id" AS "Id", 
"Extent1"."Name" AS "Name", 
"Extent1"."Host" AS "Host", 
FROM public."Sites" AS "Extent1"
WHERE "Extent1"."Host" LIKE '\%'

It works correctly.

But this LINQ queries gives us exceptions:

Code: Select all

var filteredSites = sites.Where(o => o.Host.EndsWith("\")).ToList();
var filteredSites = sites.Where(o => o.Host == "\").ToList();

unterminated quoted string at or near \"'%\\"

Can you solve this issue?

dotConnect for PostgreSQL version: 5.30.165.0

[AndreyR]

Thank you for the report, I have reproduced the problem.
I will let you know about the results of our investigation.

[AndreyR]

There is a peculiarity in the handling of escape symbols in PostgreSQL, it differs from the SQL Standard a bit. First, you can turn on the standard_conforming_strings option on your server, in this case the

Code: Select all

var filteredSites = sites.Where(o => o.Host == "\").ToList();

query will work correctly.
Please note, that this PostgreSQL behaviour will probably be changed for SQL Standard compatibility.
However, this setting will not change the Like behaviour. It is associated with the fact that EF provider works in the disconnected mode, and it doesn't have an opportunity to know server settings before the generated query is executed.
So, we recommend you to use the parameters instead of the literals in your query, like in the following example:

Code: Select all

string literal = "\";
var filteredSites = sites.Where(o => o.Host.EndsWith(literal)).ToList(); 
var filteredSites = sites.Where(o => o.Host == literal).ToList();

[Dorgaliser]

Thanks for the help, this queries works correctly.