Stored Procedures with Entity Framework 4 and RIA Services

I’ve been using WCF RIA-Services to pass data retrieved via Stored Procedure/EF 4 to a Silverlight 4 application. The system is based on an existing system and a lot of the legwork is already done in Stored Procedures so I was required to re-use them.

To start with, I imported my stored procedures to my Entity Framework model, added a Function Import and created a Complex Type for each stored procedure, as per this MSDN Article.

I had 4 issues that I had to solve when using this approach.

1. Get Output parameter from Stored Procedure
2. Add properties to Complex Types.
3. Add methods to Complex Types to be available on client side.
4. Store nested Complex Types within other Complex Types.

Defining Key for Entity

Before returning any complex types from RIA-Services you must define a Key for each. This is normally taken from the database but when using Stored Procedures you have to manually add them. If you don’t you’ll get the following error:

The entity ‘Employee’ in DomainService ‘MyDomainService’ does not have a key defined. Entities exposed by DomainService operations must have at least one public property marked with the KeyAttribute

Fortunately they are easy to add using DataAnnotations. Create a new partial class for the element that you want to add the key to and insert something similar to:

using System.ComponentModel.DataAnnotations;

[MetadataType(typeof(EmployeeMetadata))]
public partial class Employee
{
  internal sealed class EmployeeMetadata
  {
    public EmployeeMetadata() { }

    [Key]
    public int EmployeeClassID { get; set; }
  }
}

Get Output parameter from Stored Procedure

The first problem that I had to figure out was how to retrieve an output parameter from a stored procedure. Take the following stored procedure:

CREATE PROCEDURE SelectWeeklyHours
  @EmployeeID INT
, @TotalHours INT OUTPUT
AS

SELECT
  @TotalHours = SUM(Hours)
FROM
  Hours
WHERE
  EmployeeID = @EmployeeID

To get the value of @TotalHours in my DomainService I use the following code:

using System.Data.Objects;

private int GetTotalHours(int employeeID)
{
  // Declare ObjectParameter object to store output param
  ObjectParameter total = new ObjectParameter("TotalHours", typeof(int));

  // Call stored procedure, passing in ObjectParameter
  this.ObjectContext.SelectWeeklyHours(employeeID, total);
  
  // ObjectParameter will have output param value
  return Convert.ToInt32(total.Value);
}

Add property to Complex Types.

The second issue that I had to solve was that I wanted to add a new property to a complex type. At first I tried adding this to the Complex Type using the Model Browser, with the thought that I would add the appropriate value after the Stored Procedure has been called. However this throws the following exception at run time:

The data reader is incompatible with the specified ‘MyModel.Employee’. A member of the type, ‘Manager’, does not have a corresponding column in the data reader with the same name.

The easiest way to do this is create a new partial class and add the required property and decorate it with the DataMemberAttribute to specify that the property is part of the data contract and is to be serialized. For example, using the above Employee example you wanted to add an extra property, Manager, to the class you would do:

using System.Runtime.Serialization;

public partial class Employee
{
  [DataMember]
  public string Manager { get; set; }
}

Add methods to Complex Types to be available on client side

I also had a requirement where I wanted to add a couple of methods to the server side generated class for the complex type and have that available on client side. This is done by naming your class using the shared notation. E.g. if you had a complex type named Employee you would add a new class file and name it Employee.shared.cs, this file will now be copied to the client side and you will be able to access any methods there.

public partial class Employee
{
  public override ToString()
  {
    return EmployeeID.ToString();
  }

  public string FullName
  {
    return string.Format("{0} {1}", Forename, Surname);
  }
}

You will now be able to access Employee.FullName or Employee.ToString() from the client application.

Store nested Complex Types within other Complex Types

The last requirement that I had to fill was the ability to store a complex type returned from one stored procedure within the complex type of another stored procedure. At first I tried to add this into the complex type definition in the Model Browser, however this throws an error the same was as trying to add a scalar value did.

The solution to this again involves using a partial class to include the property, but rather than use the DataMemberAttribute, like above, you need to use the IncludeAttribute and AssociationAttribute. The AssociationAttribute represents the relationship between the two complex types and the IncludeAttribute specifies that the relationship should be included when the code is generated on the client. This can be used to add single complex types or collections of complex types:

using System.ServiceModel.DomainServices.Server;
using System.ComponentModel.DataAnnotations;

public partial class Employee
{
  [Include]
  [Association("Employee_Address", "EmployeeID", "EmployeeID")]
  public IEnumerable<Address> Addresses { get; set; }

  [Include]
  [Association("Employee_Position", "PositionID", "PositionID")]
  public Position Position { get; set; }
}

The AssociationAttribute takes the name of the relationship and the two key fields, if either of these key are wrong you will get an error similar to:

Association named ‘Employee_Address’ defined on entity type ‘myProject.Model.Employee’ is invalid: ThisKey property named ‘EmployeesID’ cannot be found.