Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Changing the standard query behaviour for strings in PostgreSql
 

Pages: 1
LLBLGen Pro Runtime Framework
Changing the standard query behaviour for strings in PostgreSql
Page:1/1 

  Print all messages in this thread  
Poster Message
MarcelP
User



Location:

Joined on:
06-Feb-2019 16:11:28
Posted:
3 posts
# Posted on: 06-Feb-2019 16:23:23.  
I am using the LlblGenPro Runtime Framework version 5.5 with a PostgreSql database.

This database has indexes on string fields using the "lower" function.


The following query will use the index:

Code:

Select * from person where lower(name) = 'john'


If I use the following Linq query:

Code:

var qry = from p in db.Person select p where p.name == 'John';


I would like LlblGen to create the query as mentioned above.

How can I achieve this?





  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14424 posts
# Posted on: 06-Feb-2019 18:40:05.  
Did you try the string ToLower() method?

Please read Function mappings


  Top
MarcelP
User



Location:

Joined on:
06-Feb-2019 16:11:28
Posted:
3 posts
# Posted on: 07-Feb-2019 08:34:35.  
To clarify, I am using the same code to support multiple databases.

For SqlServer, that has case-insensitive querying, the following query would suffice:

Code:
var qry = from p in db.Person select p where p.name == 'John';


However, for PostgreSql, I would have to write

Code:
var qry = from p in db.Person select p where p.name.ToLower() == 'John'.ToLower();


I would end up with this rather ugly code:

Code:

if (postgres)
{
qry = from p in db.Person select p where p.name.ToLower() == 'John'.ToLower();
}
else
{
qry = from p in db.Person select p where p.name == 'John';
}


I am looking for a solution where I do not change the query, but it will generate different Sql, depending on the database dialect.


I looked at FuncionMappings, but I couldn't find out how to create a function mapping for operators, such as == > < <= >=



  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37196 posts
# Posted on: 07-Feb-2019 16:25:18.  
There's no built-in way to make string compares 'case insensitive' based on the collation default of the database, so you always have to specify it in the query, simply because it's not possible to determine what to use. Some databases offer collations which are case sensitive (sqlserver does for instance, so it does support case sensitivity at the value level) and some databases offer case insensitive datatypes (like postgresql does, with the citext type).

Our system does support case insensitive comparisons, which are basically using UPPER() and in Linq you have to use ToLower() or ToUpper() or similar to get the same equivalent. The thing is that case insensitive comparisons aren't possible in a case sensitive collation unless you use either a specific type or specific functions which mitigate casing.

the downside of the functions is that it might be it misses indexes. For postgresql, you might want to look at the citext type for fields for which you might want to do case insensitive searches.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
MarcelP
User



Location:

Joined on:
06-Feb-2019 16:11:28
Posted:
3 posts
# Posted on: 07-Feb-2019 16:43:14.  
Thanks for the info. Not the answer I was hoping for, because now I will have to rewrite all my queries...Sad

It would be nice if the SD.LlblGen.Pro.DQE.PostgreSql could be extended somehow (maybe OpenSource????), so I could change the behaviour just a tiny bit.

In another project, I have used ServiceStack.OrmLite, and I was able to extend the PostgreSql dialect provider to generate the desired functionality.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37196 posts
# Posted on: 08-Feb-2019 15:28:55.  
MarcelP wrote:
Thanks for the info. Not the answer I was hoping for, because now I will have to rewrite all my queries...Sad

If it's a group of fields, why not switch to citext ?

Quote:

It would be nice if the SD.LlblGen.Pro.DQE.PostgreSql could be extended somehow (maybe OpenSource????), so I could change the behaviour just a tiny bit.

In another project, I have used ServiceStack.OrmLite, and I was able to extend the PostgreSql dialect provider to generate the desired functionality.

The sourcecode for the PostgresSQL DQE is available to you (Website: My Account -> Downloads -> v5.5 -> Extras -> Source code).

However what you want is that if a comparison operation is issued, it's automatically case insensitive. The DQE won't help there, as the comparison operations are emitted by the predicate classes (FieldCompareValue predicate for instance). The FieldCompareValuePredicate class for instance handles the field==value comparison (either in linq, queryspec or directly).

So what you want is alter the FieldCompareValuePredicate class (in ORMSupportClasses) to always have its CaseSensitiveCollation property set to true. This has the downside that it also uses UPPER() on sqlserver, so it's not a great solution.

IMHO the best way is to alter the types in postgresql to use citext. This gives the least problems: you can query with the same query, you don't bleed a lot of strings (due to the upper/lower call) and it uses indexes properly.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.