实体框架无法处理简单的表变量？ [英] Entity Framework can't handle a simple table variable?

问题描述

• 存储过程中的最后一行： select * from @t


• 更新了模型和它发现存储过程


• 尝试使用向导导入新功能，并表示没有找到任何列。

认真吗？有人告诉我说谎吧。

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

结果

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

推荐答案

当实体框架尝试检索列从存储过程中，它调用 SET FMTONLY ON ，然后执行存储过程。当$ code> FMTONLY 是 ON 执行只返回元数据，并且它不适用于存储过程中的一些高级构造 - 例如动态SQL，临时表和表变量。

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.

您有三个选择：

• 如另一个答案中所述 SET FMTONLY OFF 开始存储过程。这将导致您的存储过程真的执行，所以确保它只读取数据 - 每次尝试检索列时，将执行任何插入，更新或删除！


• 手动定义复杂类型


• 修改您的存储过程以不使用任何此功能

• 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

这篇关于实体框架无法处理简单的表变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！