Entities with complex Type properties cannot be returned from Stored Procedure

Complex Types have been around since version 1 of Entity Framework. However the designer had no support for creating, modifying and returning entities with complex type. If you used complex type by editing the xml manually, you were not able to open the edmx file in design view. Although version 4 of Entity Framework made remarkable improvements in this area and working with complex type in designer is seamless, there is still a feature for complex type that did not make it into this release. If you have an entity that has a complex type property and you are returning that entity from a stored procedure, you will get a runtime InvalidOperationException. Although the designer would be happy to let you map this feature, at runtime you will see this exception.

Let’s walk through an example to better understand the issue.

Suppose we have a customer table and a stored procedure that returns all customers from the customer table.

Create a model and import the customer table and GetCustomers stored procedure. To refactor FirstName and LastName into a complex type, select both properties on the customer entity on the design surface, right click and refactor into complex type. Rename the complex type as Name and also rename the ComplexType1 property to Name as well. Figure below shows the screen shot for complex type and Customer entity.

So far we have only imported the stored procedure inside the storage model. To consume the stored procedure from the object context, we need to import the stored procedure into the conceptual model which would make it available as a method call on our derived object context. Open up the model browser, right click on the stored procedure in the the store model and choose Add function import. On the dialog, set the return type to Customer entity and click Ok. Figure below shows the values set on the dialog.

   At this point, Entity Framework does two things. It creates a method on the ObjectContext called GetCustomers that calls our stored procedure. Second it creates a mapping that maps the stored procedure results to the Customer entity. The mapping can be seen from the screen shot.

You can see the problem from the above screen shot. Entity Framework does not support returning entities with complex type from a stored procedure. However Entity Framework can return complex types by itself from a stored procedure but just not when it is returned as part of an entity. If you fail to see that small message on the mapping window, when you call the stored procedure from your code, you will get an InvalidException. Code below throws an exception.

var db = new PracticeEntities();
var custs = db.GetCustomers();
foreach (var cust in custs)
{
    Console.WriteLine(cust.Name.FirstName);
}