Page 1 of 1
Case insenstive contains query
Posted: Fri 15 Apr 2011 12:02
by JeroenF
In our application we allow the users to filter a list of Relations on part of the relations name. To do this we use the contains functionality of Linq to Entities:
entities.Relations.Where(R => R.Name.Contains("ust"))
This results in the following postgreSql query
Code: Select all
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Created] AS [Created],
[Extent1].[LastModified] AS [LastModified],
[Extent1].[Blocked] AS [Blocked],
[Extent1].[Deleted] AS [Deleted],
[Extent1].[CreatedBy_Id] AS [CreatedBy_Id],
[Extent1].[LastModifiedBy_Id] AS [LastModifiedBy_Id],
1 AS [C1]
FROM [dbo].[Relations] AS [Extent1]
WHERE [Extent1].[Name] LIKE N'%ust%'
PostgreSqls LIKE funcionality is case sensitive, is their a way to have the Devart query generator use the ILIKE functionality, which is case insensitive?
Posted: Fri 15 Apr 2011 12:30
by AndreyR
Thank you for the suggestion. We will investigate the possibility to add support for PgSqlFunctions.Like (including LINQ to Entities).
Posted: Fri 10 Jun 2011 11:46
by AndreyR
The ILike support is implemented. The new build will be available in a week or so.
Posted: Thu 16 Jun 2011 11:16
by AndreyR
The new 5.30.172 build of dotConnect for PostgreSQL can be dowloaded from
here (the trial version) or from
Registered Users' Area (for users with active subscription only):
For the detailed information about the improvements and fixes available in dotConnect for PostgreSQL 5.30.172, please refer to
this announcement.
Posted: Fri 24 Jun 2011 07:48
by JeroenF
Thanks AndreyR.
The PgSqlFunctions.ILike scalar-valued function indeed does its job. But we still got a problem using it.
We are using Telerik for our front end GUI and it gives us dynamic linq statements which are then converted. So far I don't know how I could alter the dynamic linq library so it can either understand usage of dynamic PgSqlFunctions.ILike or to safely convert it.
Basicly the Telerik controls spits out the following dynamic sql:
"(iif(Name == null, \"\", Name).ToString().Contains(\"breed\"))"
Since we are using the entity framework, we have to remove the ".ToString()" part and are then left with:
"(iif(Name == null, \"\", Name).Contains(\"breed\"))"
This is then converted to the normal string.contains method.
Is there a way so that I can tell the PgSqlDataProvider to always convert those statements to ILike instead of Like sql. Or do you know of a way how I can handle this case through dynamic sql?
Posted: Fri 24 Jun 2011 11:44
by AndreyR
Try to convert the IIF statement to the Entity SQL
case statement, and use the ILike function as it is described in the following example:
Code: Select all
Devart.Data.PostgreSql.ILike(case when Name is null then '' else Name end, \"breed\")
Posted: Tue 28 Jun 2011 08:26
by JeroenF
It is not possible or very hard to try and even create your suggested syntax, let alone that you cant just use the case statement in c# code.
Converting the string statement to lambda is also not an option, since our query is on a 'random' column for the function.
All that we need is to tell devart to create ILike in their sql syntax instead of Like.
Posted: Wed 29 Jun 2011 11:33
by AndreyR
I recommend you to try something like this Regex.Replace:
Code: Select all
string test = "iif(Name == null, \"\", Name).ToString().Contains(\"breed\")";
test = Regex.Replace(test, "iif[(]([a-zA-z0-9]+) == null, \"\", Name[)].ToString[(][)].Contains[(]\"(.[^\"]*)\"[)]", "Devart.Data.PostgreSql.ILike(case when $1 is null then '' else $1 end, \"$2\"\")");
It worked for me.
I recommend you to write a suggestion at our
Entity Famework Support UserVoice about the case-insensitive Like implementation as a provider configuration option.
Posted: Thu 30 Jun 2011 09:53
by JeroenF
Hi AndreyR,
That regular expression does seem to work, but now the dynamic linq has a problem with it.
Exception Details: System.Linq.Dynamic.ParseException: No property or field 'Devart' exists in type 'Relation'
Guess I'll suggest the case-insensitive Like implementation as a provider configuration option.
Posted: Fri 01 Jul 2011 14:42
by AndreyR
Dynamic Linq does not support our extensions.
We are investigating the possibility of adding the case-insensitive Like implementation.