Using Database Functions

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

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 Stored Procedures and Functions section, you can create domain methods for stored procedures and database functions. You can map these methods to entities that match what the procedure/function returns, to a scalar value, or to a special type, called ComplexType, when the procedure/function returns a unique set of columns. The generated domain method allows you to call the corresponding procedure/function directly from your code.

You can create a domain method for a Table-Valued Function (TVF) or Scalar-Valued Function (SVF). The domain method for a Scalar-Valued Function (SVF) is more specific. It is marked with the MappedFunctionAttribute and it throws 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

You can 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.