How to: Map a Domain Method Result to a Complex 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 Persistent Type topic demonstrated how to map a stored procedure result to a collection of persistent types. Sometimes the shape of the result returned by the stored procedure or database function 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. This topic shows you how to map a domain method result to a collection of complex types.

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 Complex Types

Suppose, you have the following stored procedure (see the code-snippet below). It is based on the SofiaCarRental database. The procedure takes a single @CarMake parameter and aggregates data from both the Categories and Cars tables.

CREATE PROCEDURE GetCarsDetails(@CarMake varchar(50))
AS
BEGIN
  SELECT car.CarID, car.Make, car.Model, category.CategoryName
  FROM Cars car
  JOIN Categories category ON car.CategoryID = category.CategoryID
  WHERE car.Make = @CarMake
END

If you have already generated a domain model, you could include the GetCarsDetails 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. Except this time:

1. In the Domain Method Editor, select the Complex Type option.
2. Click the Retrieve Result Shape button to retrieve the schema information about the result returned by the stored procedure.
3. Once the schema is available, click on Create Complex Type to create a compatible CLR type.

4. Telerik Data Access will create a new CLR type with default name 'StoredProcedureNameResultSet0'. 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 GetCarsDetailsResultSet0 and it is renamed to CarDetails.

   If the generated function already returns a complex type and you have changed the schema definition of the corresponding stored procedure in the database, you could automatically update the complex type it returns by clicking the Update Complex Type. For more information, see How to: Edit a Domain Method.

What Just Happened ?

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

public IEnumerable<StoredProceduresDemo.CarDetails> GetCarsDetails(string carMake)
{
}

Public Function GetCarsDetails(ByVal carMake As String) As IEnumerable(Of StoredProceduresDemoVB.CarDetails)
End Function

Additionally, a new CLR type will be generated:

public partial class CarDetails
{
   public virtual int CarID { get; set; }
   public virtual string Make { get; set; }
   public virtual string Model { get; set; }
   public virtual string CategoryName { get; set; }
}

Partial Public Class CarDetails
    Public Overridable Property CarID() As Integer
    Public Overridable Property Make() As String
    Public Overridable Property Model() As String
    Public Overridable Property CategoryName() As String
End Class

The generated CLR type should have the following characteristics:

• It should provide a parameterless constructor, so that Telerik Data Access can create instances of that type.
• The property names should match the column names that are returned by the query. The case is ignored. In case a corresponding property for a column is not found the value of this column is ignored.
• The property types should be compatible with the column types.

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.CarDetails> GetCarsDetails(string carMake)
{
   OAParameter parameterCarMake = new OAParameter();
   parameterCarMake.ParameterName = "CarMake";
   parameterCarMake.Value = carMake;
   IEnumerable<StoredProceduresDemo.CarDetails> queryResult = 
        this.ExecuteQuery<StoredProceduresDemo.CarDetails>("GetCarsDetails", 
            CommandType.StoredProcedure, parameterCarMake);

   return queryResult;
}

Public Function GetCarsDetails(ByVal carMake As String) As _
    IEnumerable(Of StoredProceduresDemoVB.CarDetails)
 Dim parameterCarMake As New Telerik.OpenAccess.Data.Common.OAParameter
 parameterCarMake.ParameterName = "CarMake"
 parameterCarMake.Value = carMake
 Dim queryResult As IEnumerable(Of StoredProceduresDemoVB.CarDetails) = 
    Me.ExecuteQuery(Of StoredProceduresDemoVB.CarDetails)("GetCarsDetails", 
        CommandType.StoredProcedure, parameterCarMake)

 Return queryResult
End Function

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

using (EntitiesModel dbContext = new EntitiesModel())
{
   IEnumerable<CarDetails> carDetauls = dbContext.GetCarsDetails("BMW");
}

Using dbContext As New EntitiesModel()
 Dim carDetauls As IEnumerable(Of CarDetails) = dbContext.GetCarsDetails("BMW")
End Using