Function mappings and function calls

In QuerySpec, similar to Linq, developers can define function mappings to call either DB functions or construct a SQL fragment based on input from the QuerySpec query. The Dynamic Query Engines (DQEs) of each database contain default function mappings between often used .NET methods and properties and DB constructs. Function mappings are stored in FunctionMapping instances.

Custom FunctionMappingStore instances and function mappings

To override pre-defined FunctionMapping definitions from the DQE used, the developer can define a custom FunctionMappingStore with own FunctionMapping instances, which are new or override the ones in the DQE's FunctionMappingStore.

To define a custom FunctionMappingStore, follow the same procedure as with Linq. To assign a custom FunctionMappingStore to a query, set the query's CustomFunctionMappingStore property to the instance of the custom FunctionMappingStore.

Example

In the example below the method NorthwindFunctionsQS.CalculateOrderTotal() is mapped onto a function in the database using the following code: It's important to return a FunctionMappingExpression, so the QuerySpec engine knows it has to handle the object further.

internal class NorthwindFunctionsQS
{
    public static FunctionMappingExpression CalculateOrderTotal(IEntityFieldCore orderId, bool useDiscounts)
    {
        return new FunctionMappingExpression(typeof(NorthwindFunctionsQS), "CalculateOrderTotal", 2, orderId, useDiscounts);
    }

The actual mapping is defined in the custom mapping class:

internal class NorthwindFunctionMappingsQS : FunctionMappingStore
{
    /// <summary>
    /// Initializes a new instance of the <see cref="NorthwindFunctionMappings"/> class.
    /// </summary>
    public NorthwindFunctionMappings()
        : base()
    {
        // define the mapping. 
        this.Add(new FunctionMapping(typeof(NorthwindFunctionsQS), "CalculateOrderTotal", 2, "fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"));
    }
}

To be able to use it, set the CustomFunctionMappingStore property on the query:

var qf = new QueryFactory();
var q = qf.Order.Where(OrderFields.CustomerId == "CHOPS")
                .Select(() => new
                {
                    OrderId = OrderFields.OrderId.ToValue<int>(),
                    OrderTotal = NorthwindFunctionsQS.CalculateOrderTotal(OrderFields.OrderId, true).ToValue<decimal>()
                });
q.CustomFunctionMappingStore = new NorthwindFunctionMappingsQS();
var results = adapter.FetchQuery(q);

Defining a custom function mapping

To define a custom function mapping, a .NET method has to be written which creates and returns a FunctionMappingExpression instance with the information to find the mapping in the custom FunctionMappingStore. The .NET method can be an extension method or other method, that's not important.

Pre-defined function mappings

QuerySpec comes with predefined function mappings. All function mappings are available through function mapping classes, one for each type the functions are for. These classes define per mapping one static method. The generic functions are defined on the Functions class as a static method. All function mappings re-use the DQE function mappings already available to Linq.

Some function mappings are defined as extension method on IExpression or IEntityFieldCore as well. This is done to make using them more convenient. Not every function mapping is implemented with an extension method, as it would pollute the interface a lot otherwise. Not every function mapping defined in the DQEs is available in QuerySpec, as some are tied to Linq specific constructs.

Usage example

To use the function mappings, you can specify the function call as follows:

var qf = new QueryFactory();
var q = qf.Order
            .Where(DateTimeFunctions.Year(OrderFields.OrderDate)
                    .Equal(1996));

In this example the YEAR() function is used on the Order.OrderDate field in a predicate, where the YEAR([Orders].[OrderDate]) result is compared with the value 1996. You can use the function calls like this in predicates, expressions (which can be used in predicates, order by and projections) and projections.

Array functions

The following array functions are defined for array based fields.

All databases

.NET Function Sql fragment
ArrayFunctions.Length(ArrayField) DATALENGTH(ArrayField) or equivalents

PostgreSQL specific

.NET Function Sql fragment
ArrayFunctions.GetValue(ArrayField, index) ArrayField[index+1]
ArrayFunctions.Contains(ArrayField, value) value = ANY(ArrayField)
ArrayFunctions.SequenceEqual(arrayValue) arrayValue = ArrayField
PostgreSqlFunctions.AnyGreaterThan(ArrayField, value) value < ANY (ArrayField)
PostgreSqlFunctions.AnyGreaterEqualThan(ArrayField,value) value <= ANY (ArrayField)
PostgreSqlFunctions.AnyLesserThan(ArrayField, value) value > ANY (ArrayField)
PostgreSqlFunctions.AnyLesserEqualThan(ArrayField,value) value >= ANY (ArrayField)
PostgreSqlFunctions.AnyLike(StringField, array) StringField LIKE ANY (array)
PostgreSqlFunctions.AllLike(StringField, array) StringField LIKE ALL (array)
PostgreSqlFunctions.AnyILike(StringField, array) StringField ILIKE ANY (array)
PostgreSqlFunctions.AllILike(StringField, array) StringField ILIKE ALL (array)
PostgreSqlFunctions.IsContainedBy(ArrayField, array) ArrayField <@ array
PostgreSqlFunctions.IsOverlappedBy(ArrayField, array) ArrayField && array

Boolean functions

Boolean functions are defined on the class BooleanFunctions and the following functions are available:

BooleanFunctions method .NET / C# equivalent Sql fragment
ToString(op1) op1.ToString() CASE statement resulting in 'True' or 'False'

Convert functions

Convert functions are defined on the class ConvertFunctions and the following functions are available:

ConvertFunctions method .NET / C# equivalent Sql fragment
ToBoolean(op1) Convert.ToBoolean(op1) CONVERT/CAST
ToByte(op1) Convert.ToByte(op1) CONVERT/CAST
ToChar(op1) Convert.ToChar(op1) CONVERT/CAST
ToDateTime(op1) Convert.ToDateTime(op1) CONVERT/CAST
ToDecimal(op1) Convert.ToDecimal(op1) CONVERT/CAST
ToDouble(op1) Convert.ToDouble(op1) CONVERT/CAST
ToInt16(op1) Convert.ToInt16(op1) CONVERT/CAST
ToInt32(op1) Convert.ToInt32(op1) CONVERT/CAST
ToInt64(op1) Convert.ToInt64(op1) CONVERT/CAST
ToSingle(op1) Convert.ToSingle(op1) CONVERT/CAST
ToString(op1) Convert.ToString(op1) CONVERT/CAST
Important!

The Convert.ToDecimal doesn't apply any precision/scale value as the .NET Decimal type doesn't have a scale/precision setting as well. If you require a specific precision/scale specification with the conversion in SQL, please create a custom function mapping for Convert.ToDecimal() and map it to the proper CONVERT statement compatible to your DB of choice. See the custom function mapping procedure for Linq, which is equal to the one in QuerySpec, how to create your own function mappings.

DateTime functions

DateTime functions are defined on the class DateTimeFunctions and the following functions are available:

DateTimeFunctions method NET / C# equivalent Sql fragment
AddDays(op1, op2) op1.AddDays(op2) DATEADD()
AddHours(op1, op2) op1.AddHours(op2) DATEADD()
AddMinutes(op1, op2) op1.AddMinutes(op2) DATEADD()
AddMilliseconds(op1, op2) op1.AddMilliseconds(op2) DATEADD()
AddMonths(op1, op2) op1.AddMonths(op2) DATEADD()
AddSeconds(op1, op2) op1.AddSeconds(op2) DATEADD()
Add.Years(op1, op2) op1.AddYears(op2) DATEADD()
Date(op1) op1.Date DATEADD(DATEPART exp)
Day(op1) op1.Day DAY(op1)
DayOfWeek(op1) op1.DayOfWeek DATEPART(…)
DayOfYear(op1) op1.DayOfYear DATEPART()
Hour(op1) op1.Hour DATEPART()
Millisecond(op1) op1.Millisecond DATEPART()
Month(op1) op1.Month MONTH()
Second(op1) op1.Second DATEPART()
Year(op1) op1.Year YEAR()

Decimal functions

Decimal functions are defined on the class DecimalFunctions and the following functions are available:

DecimalFunctions method .NET / C# equivalent Sql fragment
Ceiling(op1) Decimal.Ceiling(op1) CEILING()
Floor(op1) Decimal.Floor(op1) FLOOR()
Remainder(op1, op2) Decimal.Remainder(op1, op2) expression
Round(op1) Decimal.Round(op1) ROUND(op1, 0)
Round(op1, op2) Decimal.Round(op1, op2) ROUND(op1, op2)
Truncate(op1) Decimal.Truncate(op1) ROUND(op1, 0, 1)

Math functions

Math functions are defined on the class MathFunctions and the following functions are available:

MathFunctions method .NET / C# equivalent Sql fragment Remarks
Abs(op) Math.Abs(op) ABS(op)
Acos(op) Math.Acos(op) ACOS(op) Not on MS Access
Asin(op) Math.Asin(op) ASIN(op) Not on MS Access
Atan(op) Math.Atan(op) ATAN(op)
Atan2(op) Math.Atan2(op) ATN2(op) Not on MS Access
Ceiling(op) Math.Ceiling(op) CEILING(op) Not on MS Access
Cos(op) Math.Cos(op) COS(op)
Exp(op) Math.Exp(op) EXP(op)
Floor(op) Math.Floor(op) FLOOR(op)
Log(op1, newBase) Math.Log(op1, newBase) LOG(op1, newBase) Not on MS Access.
Log10(op) Math.Log10(op) LOG10(op) Not on MS Access
Power(op1, op2) Math.Pow(op1, op2) POWER(op1, op2)
Round(op) Math.Round(op) ROUND(op, 0)
Round(op1, op2) Math.Round(op1, op2) ROUND(op1, op2)
Sign(op) Math.Sign(op) SIGN(op)
Sqrt(op) Math.Sqrt(op) SQRT(op)
Tan(op) Math.Tan(op) TAN(op)
Truncate(op) Math.Truncate(op) TRUNCATE(op, 0, 1)

The shown Sql fragments are for SQL Server, and can differ for your database.

String functions

String functions are defined on the class StringFunctions and the following functions are available:

StringFunctions method .NET / C# equivalent Sql fragment
Char(op1, op2) op1.Chars[op2] SUBSTRING(op1, op2+1, 1)
Concat(op1, op2) op1 + op2 op1 + op2
IndexOf(op1, op2) op1.IndexOf(op2) CHARINDEX expression
IndexOf(op1, op2, op3) op1.IndexOf(op2, op3) CHARINDEX expression
LastIndexOf(op1, op2) op1.LastIndexOf(op2) CASE with CHAR index expr
LastIndexOf(op1, op2, op3) op1.LastIndexOf(op2, op3) CASE with CHAR index expr
Length(op1) op1.Length LEN(op1)
PadLeft(op1, op2) op1.PadLeft(op2) CASE with SPACE expression
PadLeft(op1, op2, op3) op1.PadLeft(op2, op3) CASE with REPLICATE expr
PadRight(op1, op2) op1.PadRight(op2) CASE with SPACE expr
PadRight(op1, op2, op3) op1.PadRight(op2, op3) CASE with REPLICATE expr
Remove(op1, op2) op1.Remove(op2) LEFT(op1, op2)
Remove(op1, op2, op3) op1.Remove(op2, op3) STUFF(op1, …)
Replace(op1, op2, op3) op1.Replace(op2, op3) REPLACE(op1, op2, op3)
Substring(op1, op2) op1.Substring(op2) SUBSTRING()
Substring(op1, op2, op3) op1.Substring(op2, op3) SUBSTRING()
ToLower(op1) op1.ToLower() LOWER(op1)
ToUnicode(op1) none UNICODE(op1) or equivalents
ToUpper(op1) op1.ToUpper() UPPER(op1)
Trim(op1) op1.Trim() RTRIM(LTRIM(op1))

Generic functions

Generic functions are defined on the Functions class and the following functions are available:

Functions method .NET / C# equivalent Sql fragment
Coalesce(op1, op2) op1 ?? op2 COALESCE(op1, op2)
Compare(op1, op2) op1.Compare(op2) CASE statement returning -1, 0 or 1
IIF(op1, op2, op3) op1 ? op2 : op3 CASE WHEN op1=1 THEN op2 ELSE op3 END or equivalents
Not(op1) !op1 NOT(op1)
ShiftLeft(op1, op2) none op1 * POWER(2, op2)
ShiftRight(op1, op2) none op1 / POWER(2, op2)

Extension methods on Field/Expression calling a function mapping method

A subset of the complete mapping methods have been implemented as well as extension methods IEntityFieldCore and Expression. Only a subset has been implemented to keep the API compact. The extension methods are also strongly typed, while the *Functions methods are all accepting object typed parameters.

A developer can always define custom extension methods which call into the *Functions methods to make using them easier for a particular project (e.g. when a method is used a lot).

The following methods have been defined for IExpression and IEntityFieldCore.

DateTime functions

DateTimeFunctions method Extension method
AddDays(op1, op2) AddDays(op1)
AddHours(op1, op2) AddHours(op1)
AddMinutes(op1, op2) AddMinutes(op1)
AddMilliseconds(op1, op2) AddMilliseconds(op1)
AddMonths(op1, op2) AddMonths(op1)
AddSeconds(op1, op2) AddSeconds(op1)
Add.Years(op1, op2) AddYears(op1)
Date(op1) Date()
Day(op1) Day()
Hour(op1) Hour()
Millisecond(op1) Millisecond()
Month(op1) Month()
Second(op1) Second()
Year(op1) Year()

String functions

StringFunctions method Extension method
Char(op1, op2) Char(op1)
IndexOf(op1, op2) IndexOf(op1)
Length(op1) Length()
Replace(op1, op2, op3) Replace(op1, op2)
Substring(op1, op2) Substring(op1)
Substring(op1, op2, op3) Substring(op1, op2)
ToLower(op1) ToLower()
ToUnicode(op1) ToUnicode()
ToUpper(op1) ToUpper()
Trim(op1) Trim()