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:

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

If I use the following Linq query:

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?

Did you try the string ToLower() method?

Please read Function mappings

To clarify, I am using the same code to support multiple databases.

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

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

However, for PostgreSql, I would have to write

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

I would end up with this rather ugly 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 == > < <= >=

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.

Thanks for the info. Not the answer I was hoping for, because now I will have to rewrite all my queries...

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.

MarcelP wrote:

Thanks for the info. Not the answer I was hoping for, because now I will have to rewrite all my queries...

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

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.