Map complex types of stored procedure - Entity Framework

Jun 03, 2019 BY xtreme

We can map complex types of stored procedure with the Entity Framework Designer (EF Designer) either Auto or Manually 

Step 1 : Create Stored Procedure
	Make sure the last select statement should not be a dynamic statement

Step 2: add your stored procedure to the EDMX file (when you first create it, or later on after update)
	Double click .edmx file in solution explorer
	This open a New Model Browser window
	Right click on Model diagram (right side of window) > Update Model from Database
Step 3: once Uploaded, Click Save above
	This create 2 entires in Model Browser Navigation Tree
	1. YourModel > Complex Type
	2. YourModel > Function Imports 
	2. YourModel.Store > Stored Procedures / Functions

Note: Can also Create Function Import
	In Model Browser window > YourModel.Store > right click on your Stored Procedure > Add Function Import
	This Open up Add Function Import Popup window. you need to 
	(1) define that the stored procedure returns a collection of complex types
	(2) get the column info from that stored procedure to know what columns it will return, then 
	(3) you tell Visual Studio to generate a new complex type based on that column info:

Note: If Corrosponding Entriy of your Stored Procedure not shwoing in Model Browser > YourModel > Complex Type
	Try adding this line to the beginning of your stored procedure, and update Model Browser again
	SET FMTONLY OFF You can remove this after you have finished importing.