Case insenstive contains query

Case insenstive contains query

Postby 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?
JeroenF
 
Posts: 5
Joined: Thu 14 Apr 2011 14:38

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

Postby AndreyR » Fri 10 Jun 2011 11:46

The ILike support is implemented. The new build will be available in a week or so.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

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

Postby 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?
JeroenF
 
Posts: 5
Joined: Thu 14 Apr 2011 14:38

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

Postby 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.
JeroenF
 
Posts: 5
Joined: Thu 14 Apr 2011 14:38

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

Postby 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.
JeroenF
 
Posts: 5
Joined: Thu 14 Apr 2011 14:38

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


Return to dotConnect for PostgreSQL