Using Backslash in filter query

Using Backslash in filter query

Postby Dorgaliser » Mon 04 Jul 2011 07:52

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
Last edited by Dorgaliser on Mon 04 Jul 2011 09:16, edited 1 time in total.
Dorgaliser
 
Posts: 2
Joined: Mon 04 Jul 2011 07:35

Postby AndreyR » Mon 04 Jul 2011 09:12

Thank you for the report, I have reproduced the problem.
I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Mon 11 Jul 2011 11:31

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();
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Dorgaliser » Tue 12 Jul 2011 06:05

Thanks for the help, this queries works correctly.
Dorgaliser
 
Posts: 2
Joined: Mon 04 Jul 2011 07:35


Return to dotConnect for PostgreSQL