I am skinning my knees on Entity Framework 4 and running into a slight problem.
I have some stored procedures that I am pulling into my EDMX. When I create complex types from these procs, EF has no problem getting the column information. Except in one place. After being puzzled for a while, I figure out it was my temporary table getting populated that is causing the problem. Actually it is simply calling the INSERT into the temp table that is causing the problem. I'm not actually populating it with any information.
While I know that I can manually create a complex type then map the function to that type, I would like to be able to just let EF take care of it for me. Does anyone know what I am doing wrong?
Below is a sample proc that doesn't work. Run this in a DB and add the proc to you EDMX. Then try to get the column information in the "Add Function Import" screen. Nothing is returned. Comment out the INSERT to the temp table and get the column information and it works.
Thanks, Steve
CREATE PROCEDURE dbo.TestProc
AS
SET NOCOUNT ON
CREATE TABLE #TempTable(
StartDate datetime
)
INSERT INTO #TempTable
SELECT null
DROP TABLE #TempTable
SELECT 1 AS ReturnValue
SET NOCOUNT OFF
GO
A few things to try.
Try using the following stored proc (untested .. just thinking out loud...)
CREATE PROCEDURE dbo.Foo
AS
SET NOCOUNT ON
DECLARE @ResultTable TABLE (SomeId INTEGER)
INSERT INTO @ResultTable
SELECT DISTINCT Id AS Identity -- Or u can rename this field to anything...
FROM SomeExistingTableWhichHasAnIdentityField
GO
Try that and see if the wizard refreshes, now.
--
Ok .. when the EF designer/wizard/whatever fails to figure out EXACTLY what my stored proc is suppose to be returning, I usually do the following :-
/* /*
comment out EVERYTHING after the procedure definition.eg..
ALTER PROCEDURE dbo.Foo
(
Bar1 INT,
Bar2 TINYINT,
... // whatever u have as your optional input arguments //
)
AS
SET NOCOUNT ON
/*
.... every thing in here is commented out
*/
GO
Now ... 3. Add a forced fake return in the stored proc, which (more or less) just defines the output structure/fields.
eg..
ALTER PROCEDURE dbo.Foo
(
Bar1 INT,
Bar2 TINYINT,
... // whatever u have as your optional input arguments //
)
AS
SET NOCOUNT ON
SELECT 1 AS Id, 1 AS UserId, 1 AS SomeOtherId,
CAST('AAA' AS NVARCHAR(350)) AS Name,
-- etc etc etc..
/*
.... every thing in here is commented out
*/
GO
and then ...
... and now EF is updated and doesn't know we've changed the plumbing of your stored proc.
win :)
does this work for ya?