How to use the DefaultForceCaseInsensitiveForAnySource option with PostgreSQL

Hi Uriah,

Thanks for your efforts in digging out a workaround, I have also been doing that during the weekend and it is really sad that a basic funcationility is so hard to achieve in the Postgres.

Now, If we consider converting CITEXT column type then we would have to change every text column of every table and I believe that is too difficult and hard to maintain as we are using the power of ORM with XAF that takes care of DB updates. Is there any possibilities to manage the column datatype with the DB updater tool when it detects the connection is to Postgres?

Using the FilterController event and overriding may serve as a workaround but then the Column Filters, Filter Builder, etc… will still have the issue.

Hi Farooq,

I found that PostgreSQL requires you to prepare SQL query parameters and specify the database type to correctly filter by a column of the citext type (Using Dapper and Postgresql - citext data type). By default, XPO does not take this specificity into account (citext is not in the Data Types Supported by XPO list) and the filtering will not work property (values will be correctly loaded from the database, though).

To overcome this difficulty, you can implement your own PostgreSqlConnectionProvider descendant (How to create a custom XPO connection provider and then use it in an XAF application) and override its CreateParameter method.

C#
protected override IDataParameter CreateParameter(IDbCommand command, object value, string name, DBColumnType dbType, string dbTypeName, int size) {
    NpgsqlParameter param = (NpgsqlParameter)base.CreateParameter(command, value, name, dbType, dbTypeName, size);
    if(param.DbType == DbType.String && dbTypeName == null)
        param.NpgsqlDbType = NpgsqlDbType.Citext;          
    return param;
}

This solution implies that all text columns in the database use the citex type. To force XPO to always use this type when creating new columns, override the GetSqlCreateColumnTypeForString method in your custom connection provider.

C#
protected override string GetSqlCreateColumnTypeForString(DBTable table, DBColumn column) {  
    return "citext";  
}  

According to the PostgreSQL documentation (8.3. Character Types), there is no performance difference between varchar, varchar(n) , and text types.

>> Is there any possibilities to manage the column datatype with the DB updater tool when it detects the connection is to Postgres?

The DB Updater tool invokes the UpdateDatabaseBeforeUpdateSchema and UpdateDatabaseAfterUpdateSchema methods in the Updater class defined in your module. Technically, you can programmatically determine the connection type (for example, add a custom property to an XafApplication descendant and initialize it in the CreateDefaultObjectSpaceProvider method) and execute SQL queries using the ExecuteNonQueryCommand method (How to: Handle Renamings and Deletions of Business Classes and their Properties). However, this approach is cumbersome and if your application is not intended to work in an unknown environment, it is better to update the legacy database manually.

To avoid problems with searches, I recreated XAF search controller, the idea is to convert the values to compare to uppercase as well as the value from the column by using the function to upper on the server side.

Here you can find an article about my experiences with the search controller in XAF https://www.jocheojeda.com/2018/12/01/xaf-filtercontroller-case-insensitive-search/ feel free to take and/or modify the code as you needed