How to: Map a Domain Method Result to a Persistent Type

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.

The How to: Map a Domain Method Result to a Single Scalar Value topic showed you how to map a stored procedure result to a single scalar value. In some scenarios, it will be useful to translate (materialize) the stored procedure result to a collection of persistent types. You could achieve this by selecting the Persistent Type result type in the Domain Method Editor.

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

Mapping a Domain Method Result to a Collection of Persistent Types

Suppose, you have the following stored procedure (see the code-snippet below). It is based on the SofiaCarRental database. The procedure takes no parameters and returns all records from the Categories table.

CREATE PROCEDURE LoadCategories
AS
BEGIN
  SELECT * FROM Categories
END

If you have already generated a domain model, you could include the LoadCategories stored procedure by using the Update From Database Wizard.

Follow the same steps as in the How to: Create a Domain Method for a Stored Procedure topic. In the Domain Method Editor, select the Persistent Type option. From the available drop-down, you need to select the specific return type. In this demo, you are going to load categories from the database. The Category entity already exists in the domain model, it is mapped to the Categories table.

What Just Happened ?

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

public IEnumerable<StoredProceduresDemo.Category> LoadCategories()
{
}

Public Function LoadCategories() As IEnumerable(Of StoredProceduresDemoVB.Category)
End Function

How It Works ?

The generated domain method internally uses the generic ExecuteQuery<T> method exposed by the OpenAccessContext. The ExecuteQuery<T> method executes the specified stored procedure and materializes the results to instances of T. For more information, please refer to How the Generated Methods Retrieve the Data.

public IEnumerable<StoredProceduresDemo.Category> LoadCategories()
{
   IEnumerable<StoredProceduresDemo.Category> queryResult = 
        this.ExecuteQuery<StoredProceduresDemo.Category>("LoadCategories", 
            CommandType.StoredProcedure);

   return queryResult;
}

Public Function LoadCategories() As IEnumerable(Of StoredProceduresDemoVB.Category)
 Dim queryResult As IEnumerable(Of StoredProceduresDemoVB.Category) = 
        Me.ExecuteQuery(Of StoredProceduresDemoVB.Category)("LoadCategories", 
            CommandType.StoredProcedure)

 Return queryResult
End Function

The generated context method allows you to call the corresponding stored procedure from your code. Materialized persistent types are automatically attached to the context. Namely, if you modify the materialized Categories objects and call the SaveChanges method of the context, then all changes will be committed to the database.

using (EntitiesModel dbContext = new EntitiesModel())
{
   IEnumerable<Category> categories = dbContext.LoadCategories();

   // Get the last category and make some modifications.
   Category lastCategory = categories.Last();
   lastCategory.CategoryName = lastCategory.CategoryName + "_Updated";

   // Submit the changes to the database.
   dbContext.SaveChanges();
}

Using dbContext As New EntitiesModel()
 Dim categories As IEnumerable(Of Category) = dbContext.LoadCategories()

 ' Get the last category and make some modifications.
 Dim lastCategory As Category = categories.Last()
 lastCategory.CategoryName = lastCategory.CategoryName & "_Updated"

 ' Submit the changes to the database.
 dbContext.SaveChanges()
End Using

Next Steps

Sometimes the shape of the result returned by the stored procedure may not map to any of the available persistent types in your domain model. In this case, you would need to define a custom CLR type that can hold the result. For more information about mapping non-persistent types to stored procedures, check out the How to: Map a Domain Method Result to a Complex Type topic.