Entity Framework can't handle a simple table variable?

Question

The last line in the stored procedure: select * from @t
Updated model and it found the stored procedure
Tried to import a new function using the wizard and it said no columns could be found.

Seriously? Someone tell me that it lies.

create procedure WorkIt
as
set nocount on

create table #pivot
(
    Name varchar(30),
    Value decimal,
    Grade varchar(2)
)

insert into #pivot
select 'Repeating Pct', 1, 'K'
union all
select 'Repeating Pct', 2, '1'
union all
select 'Repeating Pct', 3, '2'
union all
select 'Repeating Pct', 4, '3'
union all
select 'Repeating Pct', 5, '4'
union all
select 'Repeating Pct', 6, '5'
union all
select 'Repeating Pct', 7, '6'  
union all
select 'Repeating Pct', 8, '7'
union all
select 'Repeating Pct', 9, '8'
union all
select 'Repeating Pct', 10, '9'
union all
select 'Repeating Pct', 11, '10'
union all
select 'Repeating Pct', 12, '11'
union all
select 'Repeating Pct', 13, '12'
declare @t table
(
    name varchar(30),
    K decimal (15,5) ,
    [1] decimal (15,5),
    [10] decimal (15,5),
    [11] decimal (15,5),
    [12] decimal (15,5),
    [2] decimal (15,5),
    [3] decimal (15,5),
    [4] decimal (15,5),
    [5] decimal (15,5),
    [6] decimal (15,5),
    [7] decimal (15,5),
    [8] decimal (15,5),
    [9] decimal (15,5)
)
insert into @t
exec dbo.CrossTabWithoutSumWithOrderBy #pivot, 'Name', null, 'Grade', 'Value', 
    -- sort repeating pct to bottom
    'case name when ''Repeating Pct'' then 999 else 0 end'

drop table #pivot
select * from @t

Result

name    K   1   10  11  12  2   3   4   5   6   7   8   9
Repeating Pct   2.00000 11.00000    12.00000    13.00000    3.00000 4.00000 5.00000 6.00000 7.00000 8.00000 9.00000 10.00000    1.00000

Maybe use a table valued function instead of a stored procedure — Joe Phillips, May 13 '11 at 19:51
@Joe: Table valued functions are not supported in EF at all. — Ladislav Mrnka, May 13 '11 at 20:11

score 27 · Accepted Answer · edited May 23 '17 at 11:53

27

When entity framework tries to retrieve columns from stored procedure it calls SET FMTONLY ON and after that executes the stored procedure. When FMTONLY is ON execution returns only metadata and it doesn't work with some advanced construction in stored procedures - for example dynamic SQL, temporary tables and also table variables.

You have three choices:

As described in another answer add SET FMTONLY OFF at beginning of your stored procedure. This will cause your stored procedure to really execute so make sure it only reads data - any insert, update or delete will be executed each time you try to retrieve columns!
Manually define complex type
Modify your stored procedure to not use any of this features

edited May 23 '17 at 11:53

Community

1
1

answered May 13 '11 at 20:09

Ladislav Mrnka

360,892
59
660
670

Thanks for your solution. Tried your first bullet and that didn't work. Manually defining a complex type is a big time-suck now and a maintenance nightmare in the future. Number three is unacceptable. I think what you're trying to tell me is that Entity Framework can only handle some of the most basic features supported in SQL Server. Right? – O.O May 13 '11 at 20:18
2

This is not problem of EF. EF must call `SET FMTONLY ON` to ensure that stored procedure accidentally doesn't trigger any data modification and once this is called SQL server behaves differently - that is SQL server "feature". – Ladislav Mrnka May 13 '11 at 20:20
thank you for your patience and for confirming what I feared might be true. I hope in a future version of EF support for something as basic and widely used as a temp table and/or a table variable is added. For now I will avoid using EF except for the most simple of stored procedures. – O.O May 13 '11 at 20:30
@O.O this is obviously an old question, but I wanted to document it here anyway. The reason `SET FMTONLY OFF` isn't working may be because of where you are putting the call. Put the call inside the `BEGIN` of your SP body, not with the `SET QUOTED_IDENTIFIER ON`, etc. calls that SSMS adds by default when doing a 'Modify' as those aren't actually part of the SP. – Matt Baker Dec 11 '13 at 14:14
This answer combined with the answer below worked for me. Thank you. – John Verco Dec 12 '19 at 14:50

score 1 · Answer 2 · edited Dec 19 '19 at 10:02

I had a similar issue with edmx (in my case it was if else statement), there was a work around. Before updating edmx, declare some variables of your return type, select them. Update model, then modify your stored procedure with your real code. I.e:

declare @name varchar(30),
    @K decimal (15,5) ,
    @x1 decimal (15,5),
    @x10 decimal (15,5),
    @x11 decimal (15,5),
    @x12 decimal (15,5),
   @x2 decimal (15,5),
    @x3 decimal (15,5),
    @x4 decimal (15,5),
    @x5 decimal (15,5),
   @x6 decimal (15,5),
    @x7 decimal (15,5),
    @x8 decimal (15,5),
    @x9 decimal (15,5)

Select @name, @k, @x1, @x10, @x11, @x12, @x2, @x3, @x4, @x5, @x6, @x7, @x8, @x9

Of course, you should keep this dummy code commented in your stored procedure and write a comment in it. so that when anyone updates it with edmx should uncomment this dummy code and comment the real code.

This answer combined with the answer above worked for me. Thank you. — John Verco, Dec 12 '19 at 14:50

Entity Framework can't handle a simple table variable?

2 Answers2

Linked