Case insenstive contains query

JeroenF · Post by **JeroenF** » Fri 15 Apr 2011 12:02

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?

AndreyR · Post by **AndreyR** » Fri 15 Apr 2011 12:30

Thank you for the suggestion. We will investigate the possibility to add support for PgSqlFunctions.Like (including LINQ to Entities).

AndreyR · Post by **AndreyR** » Fri 10 Jun 2011 11:46

The ILike support is implemented. The new build will be available in a week or so.

AndreyR · Post by **AndreyR** » Thu 16 Jun 2011 11:16

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.

JeroenF · Post by **JeroenF** » Fri 24 Jun 2011 07:48

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?

AndreyR · Post by **AndreyR** » Fri 24 Jun 2011 11:44

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\")

JeroenF · Post by **JeroenF** » Tue 28 Jun 2011 08:26

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.

AndreyR · Post by **AndreyR** » Wed 29 Jun 2011 11:33

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.

JeroenF · Post by **JeroenF** » Thu 30 Jun 2011 09:53

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.

AndreyR · Post by **AndreyR** » Fri 01 Jul 2011 14:42

Dynamic Linq does not support our extensions.
We are investigating the possibility of adding the case-insensitive Like implementation.