How to: Create a Domain Method for a Database Function

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.

Telerik Data Access supports database functions, i.e. you can include user defined Table-valued and Scalar-valued functions in your domain model. If you already use database functions, the advantages of having Telerik Data Access support are obvious. You will now be able to use your functions as the source for materializing entity types and complex types. This topic demonstrates how to use the Domain Method Editor to create domain methods for calling database functions. The following scenarios will be demonstrated:

Creating Domain Method for a Table-Valued Function. Read more
Creating Domain Method for a Scalar-Valued Function. Read more

To complete this walkthrough, you will need to create a new domain model based on the SofiaCarRental database.

Creating Domain Method for a Table-Valued Function

Table-Valued Functions (TVFs) are user defined functions that exist on the target server and whose return type is a table. The TVFs are very similar to Views and Stored Procedures. However, there are key advantages that make TVFs more suitable in certain scenarios. The major differences between TVFs and Views are, first, that Views can only have one SELECT statement, whereas TVFs can have multiple statements as well as procedural code. This allows you to create TVFs that execute more complex logic than Views. Additionally, TVFs can take parameters, whereas Views can't. Stored procedures also may contain procedural code. One advantage of stored procedures over TVFs is that stored procedures are capable of returning multiple result sets, whereas TVFs are not.

To create a domain method for a TVF:

From the Model Schema Explorer, expand the Stored Procedures and Functions node.
Right-click a TVF and select Create Domain Method.

In this demo, a TVF named GetDetailsForOrder is used. It is based on the SofiaCarRental database. The function takes a single @OrderId parameter and returns order details.
```
CREATE FUNCTION [dbo].[GetDetailsForOrder]
       (@orderId INT)
RETURNS TABLE
RETURN
SELECT ro.RentalOrderID,
      ro.DateProcessed,
      ro.OrderTotal,
      ro.CarID,
      c.Make,
      c.Model
FROM RentalOrders ro
      JOIN Cars c ON ro.CarID = c.CarID
WHERE ro.RentalOrderID = @orderId
```
If you have already generated a domain model, you could include the GetDetailsForOrder function by using the Update From Database Wizard.
The Domain Method Editor appears. Select the Complex Type option.
Click the Retrieve Result Shape button to retrieve the schema information about the result returned by the function.
Once the schema is available, click on Create Complex Type to create a compatible CLR type.
Telerik Data Access will create a new CLR type with default name 'GetDetailsForOrderResultSet0'. When you click Create Complex Type, the Complex Type drop-down automatically goes in edit mode. You could rename the generated class or use the default name. In this example, the default name of the generated class is GetDetailsForOrderResultSet0 and it is renamed to OrderDetails.
Click OK to close the editor and create a method for the function. The new domain method will be added to your model. You can see all generated methods under the Domain Methods nodes in the Model Object Explorer.

What Just Happened?

When you click OK, the new GetDetailsForOrder method will be added to your context class. The signature of the generated method is:

public IEnumerable<OpenAccessModel.OrderDetails> GetDetailsForOrder(int? orderId)
{
}

Public Function GetDetailsForOrder(ByVal orderId? As Integer) As IEnumerable(Of OpenAccessModel.OrderDetails)
End Function

Additionally, a new CLR type will be generated. The generated domain method internally uses the generic ExecuteQuery<T> method exposed by the OpenAccessContext. The ExecuteQuery<T> method executes the specified TVFs and materializes the results to instances of T. For more information, please refer to How the Generated Methods Retrieve the Data.

The generated context method allows you to call the corresponding function from your code.

using (EntityDiagrams dbContext = new EntityDiagrams())
{
   dbContext.GetDetailsForOrder(1);
}

Using dbContext As New EntityDiagrams()
 dbContext.GetDetailsForOrder(1)
End Using

Creating Domain Method for a Scalar-Valued Function

A scalar-valued function (SVF) returns a single value, such as a string, integer, decimal, etc.

In this demo, a SVF named GetOrderTotal is used. It is based on the SofiaCarRental database.

CREATE FUNCTION [dbo].[GetOrderTotal](@OrderId [INT])
RETURNS [money]
AS
BEGIN
DECLARE @Total money;
SELECT @Total = ro.[Days] * ro.RateApplied
FROM RentalOrders ro
RETURN @Total;
END;

If you have already generated a domain model, you could include the GetDetailsForOrder function by using the Update From Database Wizard.

The procedure for creating a domain method is absolutely the same as with TVFs. Except this time, you need to select the Scalar result type rather than a Complex Type. The Scalar option indicates that the result set contains only a single unit of data. After you select the Scalar option, the Scalar drop-down will be enabled so that you can choose the type of the return value. Select System.Decimal from the drop-down.

What Just Happened?

The domain method for the scalar-valued function 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

You should not write any code for the domain method. You will use this method in LINQ queries and Telerik Data Access will look for the attribute. The actual method will never be invoked. The scalar function can also be used as a normal function, if the code is implemented manually based on the ADO API code used in the other kinds of functions. The default behavior is generating a function targeting LINQ expressions. For more information, read here.