Function mappings

Linq to LLBLGen Pro supports function mappings which allow you to define mappings between .NET methods or properties and database functions / constructs, so they're automatically transformed to the mapped database function call or database constructs. This section describes this feature in depth, how you can define your own mappings with little effort and also how this facility can be used to enable Full text search on for example SQL Server but also other databases which support Full text search like MySql.

Linq to LLBLGen Pro comes with a large set of pre-mapped functions / database constructs out of the box. These mappings are described briefly in this section. Not all constructs could be re-created on all databases. For example due to its very limited SQL language, it wasn't possible to implement a lot of the .NET methods in SQL on Firebird.

Not only class methods can be mapped to a database construct, also properties can be mapped onto database constructs. Per database the pre-defined mappings are created in the DQE of that particular database. All mappings result in a DbFunctionCall element in the final query.

A function mapping is always between a .NET method or property and a database function or construct. This means that if you have a function defined in your database schema and you want to call it inside a Linq query you can do so by defining a mapping between a .NET method and that database function.

If such a .NET method doesn't exist, you have to define one yourself. This method can be empty, as it's not used at runtime. This section contains an example of this where a .NET method is mapped onto a user defined database function.

FunctionMapping and FunctionMappingStore

A mapping between a .NET method or property is defined by creating a FunctionMapping instance and storing it in a FunctionMappingStore instance. Every DQE has a default set of FunctionMapping instances which are stored in the DQE's default FunctionMappingStore.

This FunctionMappingStore is obtained automatically by the Linq to LLBLGen Pro provider so you can write a Linq query and use the pre-defined mappings right away.

You can extend this system by specifying your own FunctionMapping instances in a custom FunctionMappingStore instance. Custom mappings take precedence over the pre-defined mappings in the DQE, so if you want to change the pre-defined mappings, you can easily do so by simply specifying a new mapping in a custom FunctionMappingStore instance.

A FunctionMapping instance has a Key and a Pattern. The Key is composed of the following: Method name|Defining type name|Number of parameters. The Pattern is a pattern which can be used with a DbFunctionCall, e.g.: {0} + {1}. The FunctionMapping constructor makes it easy to define a function mapping.

For example, to define a database mapping for String.Concat, which accepts 2 parameters, and which should result in operand1 + operand2 in SQL Server, one can use the following FunctionMapping construction:

var stringConcatMapping = new FunctionMapping(typeof(string), "Concat", 2, "({0} + {1})");

Dim stringConcatMapping As New FunctionMapping(GetType(String), "Concat", 2, "({0} + {1})")

To fill a FunctionMappingStore, it's simply a matter of calling the Add method on the FunctionMappingStore instance to add a FunctionMapping to the FunctionMappingStore. Once a FunctionMappingStore is filled with new mappings, it can be used in all your Linq queries in a thread-safe way, as the store is used on a read-only basis.

It's recommended that you build your custom FunctionMappingStore at the start of your application, and after that re-use it in your queries.

Property get calls are mapped with a method name similar to: getpropertyname. An operator overload method is mapped with a method name similar to opoperatorMethod. For the number of parameters, specify 0 for a property getter and 1 for a property which is an indexer.

Please see the LLBLGen Pro Runtime Libraries sourcecode for examples of the function mappings defined in the various DQE projects. Function mappings inside DQEs are defined in the DynamicQueryEngine.cs files.

Calling unmapped .NET methods in a query

If you call a .NET method in your query and no FunctionMapping can be found for that method, the method is considered to be in-memory and is threated as a normal in-memory method call, as described in the section Calling an in-memory method in the projection.

Passing a custom FunctionMappingStore to the query

A custom FunctionMappingStore can be passed to the query by passing them to the constructor of the LinqMetaData class which is then used in the query. You can also set the custom FunctionMappingStore by setting the CustomFunctionMappings property of the LinqMetaData instance.

If you want to switch custom function mappings between queries created with the same LinqMetaData instance, you can: the properties called on the LinqMetaData (e.g. metaData.Customer) which produce the Queryable elements, grab the custom FunctionMappingStore instance set at that point.

If you set CustomFunctionMappings to null / Nothing in between the creation of queries using the same LinqMetaData instance, the query created after CustomFunctionMappings was set to null on the LinqMetaData instance won't use the CustomFunctionMappings.

Example of custom FunctionMapping usage

The following example shows how to define a custom mapping of a new .NET method onto a custom database function and how to use it in a query. As the .NET method is mapped onto a database element, we can use it inside the query and we're not limited to have it inside the projection. The example uses it inside the projection, but you can use it in a Where clause as well for example, as the method due to the mapping converts to a SQL fragment.

SQL function

-- SQL function stored in the target db. 
-- It calculates the order total of a given order.
ALTER   FUNCTION fn_CalculateOrderTotal(@orderID int, @useDiscounts bit)
RETURNS DECIMAL
AS
BEGIN
    DECLARE @toReturn DECIMAL

    SELECT @toReturn = SUM((UnitPrice-(Discount * @useDiscounts)) * Quantity)
    FROM [Order Details] 
    WHERE OrderID = @orderID
    GROUP BY OrderID

    RETURN @toReturn
END

.NET code using the function

/// Class which is used to specify the call to the database function. We'll map
/// the function inside it, CalculateOrderTotal, to the database function.
public class NorthwindFunctions
{
    public static decimal CalculateOrderTotal(int orderId, bool useDiscounts)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return 0.0M;
    }
}


/// Class which defines the custom mapping between NorthwindFunctions.CalculateOrderTotal and
/// fn_CalculateOrderTotal, the database function.
public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings() : base()
    {
    // define the mapping. SQLServer 2000 needs the schema to be present for the function call, 
    // so we specify that as well.
    this.Add(new FunctionMapping(typeof(NorthwindFunctions), "CalculateOrderTotal", 2, 
                        "fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"));
    }
}

//...

// these constructs are now allowing the developer to actually call fn_CalculateOrderTotal from within 
// the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to 
// the LinqMetaData first:
metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the orders with the total calculated by the function inside the database
var q = from o in metaData.Order
        where o.CustomerId == "CHOPS"
        select new { o.OrderId, OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, true) };

''' Class which is used to specify the call to the database function. We'll map
''' the function inside it, CalculateOrderTotal, to the database function.
Public Class NorthwindFunctions
    Public Shared Function CalculateOrderTotal(orderId As Integer, useDiscounts As Boolean) As Decimal
        ' empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        Return CDec(0.0)
    End Function
End Class

''' Class which defines the custom mapping between NorthwindFunctions.CalculateOrderTotal and
''' fn_CalculateOrderTotal, the database function.
Public Class NorthwindFunctionMappings 
    Inherits FunctionMappingStore

    Public Sub New()
    ' define the mapping. SQLServer 2000 needs the schema to be present for the function call, 
    ' so we specify that as well.
    Me.Add(New FunctionMapping(GetType(NorthwindFunctions), "CalculateOrderTotal", 2, _
                        "fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"))
    End Sub
End Class

' ...

' these constructs are now allowing the developer to actually call fn_CalculateOrderTotal from within 
' the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to 
' the LinqMetaData first:
metaData.CustomFunctionMappings = New NorthwindFunctionMappings()
' fetch the orders with the total calculated by the function inside the database
Dim q = From o In metaData.Order _
        Where o.CustomerId = "CHOPS" _
        Select New With _
        { _
            .OrderId = o.OrderId, _
            .OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, True) _
    }

Mapping a function with no arguments

The Linq provider determines whether a method is an in-memory method (which gets compiled and run on the client) by checking if it depends on a database originating element, like a field that's passed to it. If the method doesn't get a database originating element passed in, the method is seen as in-memory and compiled as such. If it does, it's handled further and a mapping is used, if found.

If a function has to be mapped that doesn't take any arguments, it's not possible to pass a database originating element to it and therefore a mapping of such a function will always be compiled as an in-memory method. To overcome this, decorate the .NET method mapped onto the database function with the DatabaseMappedFunctionAttribute. If a method is used in a linq query and it has this attribute defined on it, the method call wil always be seen as a method which has a mapping.

The attribute isn't necessary for mapped methods which receive one or more arguments which originate in the database.

Example:

// The class which defines the method that's mapped onto the database function 'GETDATE()'. 
public class ParameterLessFunctions
{
    // The .NET method to be used in a linq query.
    [DatabaseMappedFunction]
    public static DateTime GetDate()
    {
        return DateTime.MinValue;
    }
}

// the mapping definition.
public class ParameterLessFunctionMappings: FunctionMappingStore
{
    public ParameterLessFunctionMappings()
        : base()
    {            
        this.Add(new FunctionMapping(typeof(ParameterLessFunctions), "GetDate", 0, "GETDATE"));
    }
}

var metaData = new LinqMetaData(adapter);
metaData.CustomFunctionMappings = new ParameterLessFunctionMappings();

var q = from o in metaData.Order
        where o.OrderDate.Value.Year < ParameterLessFunctions.GetDate().Year
        select o.OrderId;

SELECT [LPLA_1].[OrderID] AS [OrderId] 
FROM [Northwind].[dbo].[Orders] [LPLA_1] 
WHERE ( ( ( ( ( YEAR([LPLA_1].[OrderDate]) < YEAR(GETDATE()))))))

If the attribute is omitted, the method is seen as an independent method (it doesn't depend on any database originating element) and is therefore ran in-memory before the query is converted to SQL: the value returned is simply passed as a value (in this case '1') to the predicate.

Full-text search

Using custom FunctionMapping instances, it's possible to define the Full-text search methods required in the query. We opted for this route instead of adding hard-coded codepaths for Full-text search, as for every database full text search is implemented differently.

On SqlServer, we can define a simple method in .NET code to be able to specify the method call, and a mapping to CONTAINS(). We then can use the .NET method to specify a full text search:

/// Class which is used to specify the call to the CONTAINS construct. 
public class NorthwindFunctions
{
    public static bool FullTextSearch(string fieldToSearch, string toFind)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return true;
    }
}

/// Class which defines the custom mapping between NorthwindFunctions.FullTextSearch and CONTAINS()
public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings() : base()
    {
         // FullTextSearch(2) on 1 field
         this.Add(new FunctionMapping(typeof(NorthwindFunctions), "FullTextSearch", 2, "CONTAINS({0}, {1})"));
    }
}

//...

// these constructs are now allowing the developer to actually call CONTAINS from within 
// the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to 
// the LinqMetaData first:
metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the employees which have 'BA' in their Notes field which is Full text search enabled.
var q = from e in metaData.Employee
        where NorthwindFunctions.FullTextSearch(e.Notes, "BA")
        select e;

''' Class which is used to specify the call to the Contains construct
Public Class NorthwindFunctions
    Public Shared Function FullTextSearch(fieldToSearch As String, toFind As String) As Boolean
        ' empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        Return True
    End Function
End Class

''' Class which defines the custom mapping between NorthwindFunctions.FullTextSearch and CONTAINS
Public Class NorthwindFunctionMappings 
    Inherits FunctionMappingStore

    Public Sub New()
         ' FullTextSearch(2) on 1 field
         Me.Add(New FunctionMapping(GetType(NorthwindFunctions), "FullTextSearch", 2, "CONTAINS({0}, {1})"))
    End Sub
End Class

' ...

' these constructs are now allowing the developer to actually call CONTAINS from within 
' the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to 
' the LinqMetaData first:
metaData.CustomFunctionMappings = New NorthwindFunctionMappings()
// fetch the employees which have 'BA' in their Notes field which is Full text search enabled.
Dim q = From e In metaData.Employee _
        Where NorthwindFunctions.FullTextSearch(e.Notes, "BA") _
        Select e

Supported default method / property mappings to functions

The following method/property mappings are supported in SQL. They're grouped per .NET type which defines the method/property and then the list of methods/properties which are supported. If there's no mapping defined for a given database for a given method/property, it's specified with the method or property.

The classes mentioned have more methods / properties than the list shows: if a method or property isn't mentioned in the list, there's no mapping of that method or property to a database construct in any supported database. Most of the time, this is because there's no database function equivalent, it needs a .NET object to function properly (e.g. a comparer) etc.

With 'supported' is meant: usable in all areas of a Linq query. A method or property which isn't mentioned in the list is still supported as an in-memory method/property, but solely in the last projection as it otherwise has to be ran on the database.

All returned indexes are floored to 0-based indexes. So if a database function starts with 1 for the first element in a set, 0 is returned if the function returns as index 1. The types of the arguments of the overload supported are given between brackets.

Array methods / properties defined by System.Array

All databases

The following Array method / properties are supported for all databases

• Length

PostgreSQL specific

The following array oriented methods are supported for PostgreSQL on array typed fields. The functions with Yes in the column for DQE Specific are defined in the class SD.LLBLGen.Pro.DQE.PostgreSql.PostgreSqlFunctions and require the namespace SD.LLBLGen.Pro.DQE.PostgreSql to be present in your code file before they're available to you.

Boolean methods / properties defined by System.Boolean

Boolean doesn't really have any of its methods/properties mapped, though it has a special method mapped which is actually an operator: Negate. This mapping creates a NOT on the boolean.

Char methods / properties defined by System.Char

Char doesn't really have any of its methods/properties mapped, though it has a special method mapped which is used to convert the char inside the database to a unicode number: ToUnicode. The usage of this method is to convert a char to an int.

This is required as char constants are stored as integers inside .NET programs and comparing a char value in the database with a constant will result in a compare with an integer, which is then passed to the database as-is, thus as an integer. To avoid that the compare fails, the char value is converted to unicode inside the database using the function mapped as Char.ToUnicode(1).

The integer value isn't converted to char, because the expression tree contains a convert from char to int, so the only way to handle this in general is to convert that conversion at that spot with the parameters at hand.

Not on: Firebird

Convert methods / properties defined by System.Convert

The Convert class is a class which can convert elements from one type to the other. The following methods and properties are supported. Only the overloads which accept a single value are supported. If one needs specific formatting, please create a custom mapping.

Conversions to Char use the database default's length. This could lead to trailing spaces. Conversions to String use the maximum length of unicode variable strings, e.g. nvarchar(4000).

• ToBoolean.
• ToByte. Not on: IBM DB2, Firebird
• ToChar
• ToDateTime
• ToDecimal
• ToDouble.
• ToInt16
• ToInt32
• ToInt64. Not on: MS Access
• ToSingle
• ToString

DateTime methods/properties, defined by System.DateTime

The following DateTime functions are supported.  The fraction part in the double typed value passed into the Add* methods is discarded in most databases. Passing 1.4 for example will add a single day when calling AddDays(1.4).

The property Now isn't mapped, although it  could be mapped onto a database function easily. The problem is that with Now, it's unclear if the client date is meant or the server date. If the property would be mapped onto a server function, the server date/time would be used in the query, while if it wouldn't get a mapping, the client date/time would be used.

If one wants a server date compare, create an extension method for DateTime and map that method in a custom mapping to the database function to retrieve the current date, e.g. GETDATE() on SQL Server.

• AddDays(double).
• AddHours(double).
• AddMilliseconds(double). Not on: MS Access, Oracle
• AddMinutes(double).
• AddMonths(double).
• AddYears(double).
• AddSeconds(double).
• Compare(DateTime, DateTime)

The following properties are supported

• Date
• Day.
• DayOfWeek.
• DayOfYear.
• Hour.
• Millisecond. Not on: MS Access, Oracle, PostgreSql
• Minute.
• Month.
• Second.
• Year.

Decimal methods/properties, defined by System.Decimal

The following Decimal methods/properties are supported. The various Totype methods aren't supported, though one could use the Convert.method mappings instead.

• Add
• Ceiling. Not on: Firebird, Google Cloud Spanner
• Compare
• Divide
• Floor. Not on: Firebird, Google Cloud Spanner
• Multiply
• Negate
• Remainder. Not on: MS Access, Google Cloud Spanner
• Round(Decimal) and Round(Decimal, Int32) . Not on: Firebird, Google Cloud Spanner
• Substract
• Truncate. Not on: Firebird, Google Cloud Spanner

String methods/properties, defined by System.String

The following string methods/properties are supported. Most of them don't work on ntext/text/clob fields due to the use of LEN/REVERSE etc. functions.  Often, the database equivalents use a function which determines the length of the string. This is often done by LEN() or similar function. These functions in general don't report trailing blanks, so LEN('Foo  ') will be 3, not 5.

• Concat(string, string), or +/& operators.
• Compare(string, string)
• IndexOf(char) Not on Firebird, Google Cloud Spanner
• IndexOf(string) Not on Firebird
• IndexOf(char, int) Not on Firebird, PostgreSql, Google Cloud Spanner
• IndexOf(string, int) Not on Firebird, PostgreSql, Google Cloud Spanner
• LastIndexOf(char) Not on: IBM DB2, Firebird, Google Cloud Spanner
• LastIndexOf(string) Not on: IBM DB2, Firebird, Google Cloud Spanner
• LastIndexOf(char, int) Not on: IBM DB2, Firebird, Oracle, Google Cloud Spanner
• LastIndexOf(string, int) Not on: IBM DB2, Firebird, Oracle, Google Cloud Spanner
• PadLeft(int) Not on Firebird
• PadRight(int) Not on Firebird
• Remove(int) Not on Firebird
• Remove(int, int) Not on Firebird
• Replace(string, string) Not on Firebird
• Replace(char, char) Not on Firebird, Google Cloud Spanner
• Substring(int) Not on Firebird
• Substring(int, int) Not on Firebird
• ToLower()
• ToUpper()
• Trim

The following properties are supported

• Length
• Chars ( Indexer). Not on: Firebird

Object method/properties, defined by System.Object

The Object properties/methods aren't really defined as methods/properties of object. For example 'ToString()' is overloaded in a lot of classes. ToString is converted in all classes as a call to Convert.ToString(value).

The following artificial methods are mapped on Object. These methods are used by the framework to produce a proper operation. If you want to overrule these functions, you have to add mappings of these functions to object in your custom mappings.

• IIF. This method mapping is used to produce the CASE statement for IIF / ?: statements in the projection or elsewhere.
• IIF_Bool. Similar to IIF, but it now works with boolean values. This requires a comparison with the same values, so the result is indeed a boolean.
• LeftShift. This method mapping is used to perform a leftshift of an operand a by the # of bits specified by operand b. Not on Firebird.
• RightShift. This method mapping is used to perform a rightshift of an operand a by the # of bits specified by operand b. Not on Firebird
• BooleanInProjectionWrapper. This method mapping is used to wrap a boolean resulting expression in a projection via a DbFunctionCall. This is done in general through a CASE block. The convention is that this block has to produce a positive integer value  if it represents a true value, 0 otherwise.

Math method / properties, defined by System.Math

The following Math methods are supported.

• Abs. Only on Google Cloud Spanner
• Ceiling. Only on Google Cloud Spanner
• Exp. Only on Google Cloud Spanner
• Floor. Only on Google Cloud Spanner
• IEEERemainder. Only on Google Cloud Spanner
• Log(). Only on Google Cloud Spanner
• Log(double). Only on Google Cloud Spanner
• Log10. Only on Google Cloud Spanner
• Round(double). Only on Google Cloud Spanner
• Round(double, int). Only on Google Cloud Spanner
• Sign. Only on Google Cloud Spanner
• Sqrt. Only on Google Cloud Spanner
• Trunc. Only on Google Cloud Spanner
• Pow. Not on Firebird

Numeric method / properties

The following methods / properties are supported.

System.Int16 / System.Int32 / System.Byte / System.SByte / System.UInt16 / System.UInt32 / System.Int64 / System.UInt64:

• ToString

Remarks and limitations

The following remarks and limitations have to be taken into account when using function mappings.

• Functions only work on the .NET type's lookalike type in the database. This means that if you use a string function in the Linq query on a field which is of type string but in the database it's an int field and you use a type converter to convert back/forth to int from the string, the function will fail as the provider will assume the type in the DB can work with a string function. In this case, it's advised to write a custom function mapping.
• If you use your own referenced typed type in the query with an operator you've overloaded in your type, the query will likely fail due to the fact that there's no mapping for the op_operatorMethod. If you run into this error, provide a custom mapping for this function. This will mean the function will run in the database.
• We tried to add as much useful function mappings as possible, however it might be that the system function you want to use isn't mapped. In that case, add a custom mapping. With thousands of system functions defined in systems like Oracle or DB2, it's undoable to ship a mapping for every one of them.
• It might be that not all mappings work on your database system. E.g. a mapping to a function in SQL Server 2005 might not work on SQL Server 7. This should be rare, but nevertheless it sometimes happens. If you experience an exception at runtime, enable DQE tracing and examine the query to see if the mapping you're using is indeed supported on the version of your database system.
• Mixing method calls to methods which run in-memory with database elements only works if the method or operator (e.g. +) is run in memory and the operands are database elements. So mixing method calls which have a database mapping with methods which don't have a mapping isn't going to work, as the method which has a database mapping requires an operand which value isn't known till the query has run as it relies on in-memory method calls.

• If a method doesn't have a mapping, it still can be used in a linq query, though only in the last projection. This means that if you use a method which doesn't have a mapping in a Where clause or in a projection which will be wrapped in another query, the method won't be able to be translated to SQL and therefore will fail. See the example below: DaysInMonth  isn't a mapped function.

  // query which will work
  var q = from o in metaData.Order
          select DateTime.DaysInMonth(o.OrderDate.Year, o.OrderDate.Month);
  
  // query which fails, because the projection is wrapped inside another query
  var q = (from o in metaData.Order
           select DateTime.DaysInMonth(o.OrderDate.Year, o.OrderDate.Month)).Count();