Complex Types in the Entity Framework

In this post I will describe the process you need to go through to get a stored procedure to return a complex type in the Entity Framework.

It is very easy in the Entity Framework to quickly create crud functions that will manipulate data in your tables. However if you want to use stored procedures that return data that is not mapped to an entity from your database, then there are a few more hoops you need to jump through.

Firstly, by examining the stored procedure, you can determine what data types are returned from the stored procedure.

This following method is a quick and dirty one I have used, there may be a better way to do this, and if there is please let me know. There is a tool called Entity Developer from devart which may do the same, but as yet I haven’t had the chance to use it.

1. Switch to your *.edmx file and drop a new entity from the toolbox on to the design surface. You may need to delete the Id property that is automatically created if you don’t need it.
   
2. Then right click on any of the entries in the model browser and choose ‘Update Model From Database’
   
3. Choose any tables that your procedure uses from the Add tab. This will map the tables as entities, allowing you to simply copy and paste entity properties from the tables into your new entity.
4. Add your stored procedure.
5. Right click on ‘Function Imports’ within the Entity Container in the Model Browser and choose ‘Create Function Import’.
   
6. This then gives you the options to Choose the stored procedure, its name and most importantly the Entity that it will be imported to. Choose your new entity you have just created.
   

Now your new entity can be called

   1: ProjectMVCEntities myEntities = new ProjectMVCEntities();

   2:                 

   3: var viewData = myEntities.Job(0, 4).ToList();

   4:         

   5: return View();

This is taken from an MVC project, hence the return value of View(), but I hope you get the idea.

Now the stored procedure will be executed with any required parameters and the Job entity will hold the data.