Enhancement: Introduce the formatters for the QueryField class

[mikependon]

Describe the enhancement

The object QueryField is being used as a class for the conditional query (i.e.: WHERE [Column] = 'Value'). The current limitation to this object is it cannot use the existing underlying provided RDMBS functions (i.e.: LEFT, RIGHT, UPPER, LOWER, TRIM, etc).

This enhancement would at least enable us to extend the QueryField object to be able the user to customize the string generations with the proper usage of the database functions.

Currently, if you use the QueryField object like below.

var where = new LenQueryField("ColumnName", "VALUE");

It will generate the string below.

WHERE ([ColumnName] = @ColumnName);

The target is to override that string generation. Let us say, we have implemented a new object named LenQueryField that uses the LEN function of the underlying RDBMS (see a sample code below).

var where = new LenQueryField("ColumnName", 3);
var connection = connection.Query<Entity>(where);

Then it should be able to generate the string below.

WHERE (LEN([ColumnName]) = @ColumnName);

And below is for the UPPER function.

var where = new UpperQueryField("ColumnName", "VALUE");
var connection = connection.Query<Entity>(where);

Which would generate the string below.

WHERE (UPPER([ColumnName]) = @ColumnName);

Additional Context

It is also very good if we can extend this logic for the users point of view. They might be interested on creating their own logic on top of it, of course with the proper formatting on the function.

Let us say we have this base class within RepoDB library.

public class FunctionalQueryField
{
     public TrimQueryField(string fieldName, string value, string formattedFunction)
     {
          ...
     }
}

Then the user should be able to inherit to provide their own logic. Below is a sample implementation.

public class TrimQueryField : FunctionalQueryField
{
     public TrimQueryField(string fieldName, string value)
     { }
     : base(fieldName, value, "TRIM{0}")
}

And they can use their own custom code.

var where = new TrimQueryField("ColumnName", "VALUE");
var connection = connection.Query<Entity>(where);

With this, the users can also create a much more advance/complex string generations.

public class ConvertQueryField : FunctionalQueryField
{
     public TrimQueryField(string fieldName, string value, Type targetType)
     { }
     : base(fieldName, value, "CONVERT({(new ClientTypeToDbTypeConverter().Resolve(targetType))}, {0})")
}

[mikependon]

This is now available on release v1.12.8.