Using Database Functions

Many databases use stored procedures and functions to perform predefined logic on database tables. Although one of the key features of Telerik Data Access is its ability to automatically build SQL queries based on your LINQ queries, as well as build the SQL queries for inserting, updating, or deleting data, you may want to override these steps and use your own predefined stored procedures and functions.

As it is described in the Executing Stored Procedures section, you can create context methods for stored procedures and database functions. You can map these methods to persistent classes if they match what the procedure/function returns, to a scalar value, or to a special custom type, when the procedure/function returns a unique set of columns. The context methods allow you to call the corresponding procedure/function directly from your code.

You can create methods for Table-Valued Functions (TVF) and Scalar-Valued Functions (SVF) as well. While the code of the methods for Table-Valued Functions (TVF) should have the same structure as the one for the stored procedures, the context methods for Scalar-Valued Functions (SVF) are more specific. Such a method should be marked with the MappedFunctionAttribute and it should throw a new NotImplementedException.

[MappedFunctionAttribute( Name = "[dbo].[GetOrderTotal]", IsDeterministic = false, 
    Backend = Backend.MsSql )]
public static System.Decimal GetOrderTotal( int? orderId )
{
   throw new NotImplementedException();
}

<MappedFunctionAttribute(Name := "[dbo].[GetOrderTotal]", IsDeterministic := False, _
    Backend := Backend.MsSql)>
Public Shared Function GetOrderTotal(ByVal orderId? As Integer) As System.Decimal
 Throw New NotImplementedException()
End Function

The "scalar-valued" methods target LINQ expressions. When you use a "scalar-valued" method in a LINQ query, Telerik Data Access will look for the MappedFunctionAttribute and will call the corresponding database SVF. The actual method will never be invoked.

The following example demonstrates how to use a Scalar-Valued function (SVF) in a LINQ query.

using ( EntityDiagrams dbContext = new EntityDiagrams() )
{
   var query = from order in dbContext.RentalOrders
               let orderTotal = EntityDiagrams.GetOrderTotal( order.RentalOrderID )
               select new
               {
                   order.OrderStatus,
                   Total = orderTotal
               };
}

Using dbContext As New EntityDiagrams()
    Dim query = From order In dbContext.RentalOrders
                Let orderTotal = EntityDiagrams.GetOrderTotal(order.RentalOrderID)
                Select New With {Key order.OrderStatus, Key .Total = orderTotal}
End Using

To create your own methods for calling SVFs:

1. You need a function stub to call the database function from a LINQ query.
2. The function must be static.
3. The method must not execute any code. It must throw NotImplementedException.
4. The method must be marked with the MappedFunctionAttribute.