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.


Related