Adding stored procedures complex types in Entity Framework

OK - here's the step-by-step way of doing this:

(1) add your stored procedure to the EDMX file (when you first create it, or later on by using Update model from database and picking that stored procedure)

(2) once you have the stored procedure in your model - use the Model Browser to add a Function Import :

(3) the next dialog that pops up is vitally important - you need to (1) define that the stored procedure returns a collection of complex types, then you need to (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:

(4) once you've done that - you should now see the stored procedure in your conceptual model section in the Model Browser, and the newly generated complex type should show up there, too:

As Sandeep said, EF doesn't support importing stored procedures which build result set from Dynamic queries or Temporary tables.

But you don't have to rewrite your whole SP.

Just write another one, with the same name, that returns the correct row format without using dynamic SQL or a temp table. Then use the EF SP adding function, which will now auto generate the complex type.

Edit: It's actually easier to make a comment at the top of the SP that immediately selects the desired row with all the data types specified with CASTS. When you need to import the SP into EF, just uncomment the code.

e.g.

CREATE PROCEDURE myProc ()
AS
BEGIN
-- uncomment the following row to import:
-- SELECT CAST( 0 AS int) AS column1Name, CAST( 'a' AS varchar(50)) AS clumn2name

-- comment out the SP content when you want to import it.
< proper SP content >
END

Then drop the stored proc and create the original.
Save this temporary importing SP you have made in case you need it again, though.

If this is still unresolved, after you Add the Function Import, go to the Solution Explorer, right click your {name}.Context.tt file and do "Run Custom Tool". The method will now show up in your derived Context class.

This seems like a bug in Visual Studio 2012, which is what I am using, I haven't applied Update 1, I will try to see if that fixes it.

This is for Ross Brigoli

Try adding this line to the beginning of your stored procedure:

SET FMTONLY OFF You can remove this after you have finished importing.

Source:- Why can't Entity Framework see my Stored Procedure's column information?