Problem with Entity Framework 4, Complex Types, StoredProcs, and temp tables

Question

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

Add SET FMTONLY OFF to your sp. http://stackoverflow.com/questions/3583933/creating-entities-from-stored-procedures-which-have-dynamic-sql/3583971#3583971 — Puzzled, Nov 01 '11 at 05:41

Pure.Krome · Answer 1 · 2010-08-14T04:44:11.097

A few things to try.

Use Variable Tables instead -> maybe the import wizard prefers that?
Name your return fields.

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.

--

Attempt #2 :)

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 :-

Make sure the stored procedure doesn't exist at all in the EF designer/context, etc. (You have a clean starting point)
Open up your stored procedure and /* /* 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 ...

Add this stored proc to your EF designer/wizard/etc... Now the correct fields should be 'determined' by the designer. AWESOME. Yes .. the values are all hardcoded .. but that's ok (so far).
Once your happy that EF is now updated right, go back to your stored proc, and remove all hardcoded SELECT (which we did in the above step). Now we remove the comments which we commented out the entire real code. So you should have your original stored proc, back.

... and now EF is updated and doesn't know we've changed the plumbing of your stored proc.

win :)

does this work for ya?

Thanks Pure. We needed to use the temp tables because of some dynamic SQL that follows the INSERT. The example I gave was a stripped down version that still displays the problem. So table variables aren't an option. I appreciate the response though. — Repellr, Aug 13 '10 at 13:24
I ran into this as well. Far better than either of these 2 suggestions, which require you to effectively break your SPROC to update the model or use underperformant table variables (at least in my case), simply create an explicit data contract at the top of your SPROC definition inside an expression that will never evaluate, as explained here http://stackoverflow.com/a/16594665/237723 — JoeBrockhaus, May 16 '13 at 18:27
Option 1, changing "create table #temp_table" to "declare @temp_table{...}" worked for me — Vicki, Jul 10 '19 at 10:17

score 1 · Answer 2 · edited May 23 '17 at 11:53

Here is a variation of Pure.Krome's excellent answer. Rather than commenting out your sproc code, create a new view that consists of only the "fake" select statement described by Pure. The view will be used to create an entity. The view entity then becomes the container for the stored procedure results.

Create View dbo.FooWrapperView as 
  Select IsNull(MyPrimaryID,-999) as IntFieldName, --IsNull disallows nulls so EF designer will make this the primary key.
  NullIf(CAST('AAA' as VarChar(20)), '') as VarChar20FieldName, --NullIf allows null so EF designer will NOT make this part of the primary key.
  NullIf(CAST('AAA' as VarChar(42)), '') as VarChar42FieldName,
  NullIf(CAST(1.1 as DECIMAL(8, 5)), '') as Decimal85FieldName

In the entity designer right-click and choose "Update Model From Database" then select your wrapper view (and the sproc if you haven't done so already). This will create the entity mapped to the bogus wrapper view. The designer picks the primary key based on the view's IsNull and NullIf statements (details here). Find the sproc in the model browser. Right-click it and select "Add Function Import...". Under "Returns a collection of" select Entities. Choose your view entity and click OK. Now when your stored procedure is called it will dump the results into your view entity.

MyProject.MyEntities myContext = new MyProject.MyEntities();
var myQuery = myContext.usp_FOO(myRecordID);
FooWrapperViewEntity myFooEntity = new FooWrapperViewEntity();
myFooEntity = myQuery.FirstOrDefault();

I ran into this as well. And even better than wrapping it in a view, simply put a data contract into the top of the SPROC inside a condition that will never evaluate true, as provided here: http://stackoverflow.com/a/16594665/237723 — JoeBrockhaus, May 16 '13 at 18:25

score 0 · Answer 3 · edited May 23 '17 at 12:24

0

At first you have to create a normal store procedure without using temp table. this store procedure will contain all the column name (normal table+temp table). Now will be able to create the complex type in your EDMX

For more see this

edited May 23 '17 at 12:24

Community

1
1

answered May 28 '13 at 06:24

atik sarker

4,290
3
37
40

Problem with Entity Framework 4, Complex Types, StoredProcs, and temp tables

3 Answers3

Attempt #2 :)

Linked