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 |
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() |