（附加）EF不能从#temp表从存储过程中选择的推断回报模式 [英] (Additional) EF can't infer return schema from stored procedure selecting from a #temp table

问题描述

我有一个类似的问题在

描述

的 EF不能从存储过程返回推断架构从#TEMP表中选择

和我创建了我的存储过程的解决方案基于上述的解决方案，但我仍然得到了类似的错误EF，我真的不知道为什么还是明白我怎么能修好它。

类型'的rowNum的一员，不具有相同名称的数据读取相应的列。

我的具体错误：

数据读取器与指定的Te​​stModel.sp_SoInfoDocs_Result不兼容。类型'的rowNum的成员，不具备与同名的数据读取相应的列。

我的存储过程：

  ALTER PROCEDURE [DBO]。[sp_SoInfoDocs] 
 @searchText为nvarchar（200），
 @PageNumber INT，
 @PageSize INT 
 AS 
 BEGIN 
 IF 1 = 2 
 BEGIN 
选择
投（null作为INT）作为的rowNum 
，投（空文本）作为服务器名
，投（空文本）作为作业名
，投（null作为DATETIME）作为大馆
，投（空为int）作为runCount 
，投（null作为为nvarchar（10））作为的orderID 
，投（空文本）作为applicationName的
，投（空文本）作为memberName 
，投（空文本）作为的nodeID 
，投（null作为为nvarchar（10））作为endStatus 
，投（空为int）作为RETURNCODE 
，投（null作为DATETIME）作为startTime时
，投（null作为DATETIME）的结束时间
，投（null作为为nvarchar（50 ））作为状态
，投（空文本）作为所有者
，投（null作为位）作为existsNote 
，其中
 1 = 2 
端
 
 DECLARE @LowerLimit INT; 
设置@LowerLimit =（@PageNumber  -  1）* @PageSize; 
 DECLARE @UpperLimit INT; 
设置@UpperLimit = @PageNumber * @PageSize; 
 
打印CAST（@LowerLimit为varchar）
打印CAST（@UpperLimit为varchar）
 
选择ROW_NUMBER（）OVER（ORDER BY表达式1）为的rowNum，* 
到#TEMP 
从（
 
选择dbo.SOInfo.jobName，dbo.SOInfo.no​​deID，dbo.SOInfo.no​​deGroup，dbo.SOInfo.endStatus，dbo.SOInfo。 RETURNCODE，dbo.SOInfo.startTime，dbo.SOInfo.endTime，
 dbo.SOInfo.oDate，dbo.SOInfo.orderID，dbo.SOInfo.status，dbo.SOInfo.runCount，dbo.SOInfo.owner，DBO。 SOInfo.cyclic，dbo.SOInfo.soInfoID，dbo.SOInfo.docInfoID，
 dbo.SOInfo.existsNote，dbo.SOInfo.no​​Sysout，dbo.serverInfo.serverName，dbo.Groups.label AS的applicationName，Groups_1.label AS memberName，
 Groups_2.label AS组名，Groups_3.label AS scheduleTableName，dbo.SOInfo.serverInfoID，dbo.SOInfo.applicationID，dbo.SOInfo.groupID，
 dbo.SOInfo.memberID，dbo.SOInfo。 scheduleTableID，dbo.docFile.docFileID，dbo.docInfo.docInfoID AS表达式1，dbo.docFile.docFileObject 
从dbo.SOInfo INNER JOIN 
 dbo.serverInfo ON dbo.SOInfo.serverInfoID = dbo.serverInfo.serverInfoID INNER JOIN 
 dbo.docInfo ON dbo.SOInfo.docInfoID = dbo.docInfo.docInfoID INNER JOIN 
 dbo.docFile ON dbo.docInfo.docFileID = dbo.docFile.docFileID LEFT OUTER JOIN 
 DBO .Groups AS Groups_3 ON dbo.SOInfo.scheduleTableID = Groups_3.ID LEFT OUTER JOIN 
 dbo.Groups AS Groups_1 ON dbo.SOInfo.memberID = Groups_1.ID LEFT OUTER JOIN 
 dbo.Groups AS Groups_2 ON DBO .SOInfo.groupID = Groups_2.ID LEFT OUTER JOIN 
 dbo.Groups ON dbo.SOInfo.applicationID = dbo.Groups.ID 
 
 WHERE CONTAINS（docfileObject，@ SEARCHTEXT）
 ）TBL 
 
 SELECT COUNT（1）从#TEMP 
 
选择的rowNum，服务器名，作业名称，大馆，runCount，单编号，的applicationName，memberName，的nodeID，endStatus，返回码，的startTime ，结束时间，状态，所有者，existsNote 
从#TEMP WHERE的rowNum> @LowerLimit及的rowNum< = @UpperLimit 
端
  

我的总体目标是：

1. 通过聚集索引表（文档信息）的搜索和查找包含特定搜索字符串值的所有行




2. 从其他表与每个文档信息对象相关联的同时捕获的元数据




3. 的行动结果（1） （2）上述被写入到一个#temp表，而我再添加一个的rowNum列，让我介绍分页即




4. 有关数量介绍分页可在任一时间被退回，根据提供的PageNumber和每页变量元数据的结果。

什么确实工作

1. 我能够成功地创建存储过程。




2. 在SSMS我能够成功地执行存储过程，并提供我所期望的结果，这里是一个例子。

   
   
   

   




3. 在EF我已经能够更新并从更新导入存储过程数据库

   
   
   

   




4. 在EF我再能够看到函数导入，并可以查看映射

   
   
   

   的

   
   
   

   




5. 在ED我就能够看到生成的复杂类型

   
   
   

   




6. 我用下面的代码来调用过程

    使用（TestEntities上下文=新TestEntities（））
    {
   名单，LT; sp_SoInfoDocs_Result> LST = context.sp_SoInfoDocs（SEARCHTEXT，1，10）.ToList（）; 
   } 
     




7. 我编译和运行我的解决方案，并从EF以下错误

   
   
   

   
   

   System.Data.Entity.Core.EntityCommandExecutionException'发生在EntityFramework.SqlServer.dll

   
   
   

   附加信息：数据读取器是指定的SysviewModel.sp_SoInfoDocs_Result不兼容。该类型的成员，'的rowNum，不具有相同名称的数据读取相应的列。

   
   

我是当它涉及很多新手/基本用户既SSMS / SQL和EF，这是据我所知我伸/能去，我真的不知道在哪里转向下一个，以便解决这一问题。

我已经通过如此广泛搜查，可以看到别人谁也有类似的问题，并试图解决方案建议，但似乎没有为我工作。

我真的会非常非常感激的人谁可以帮助我了解

1. 它是什么，是错的/我做错了？




2. 有没有更好的方式来实现我需要什么？




3. 想法，我怎么能解决这个问题。

在此先感谢

解决方案

我已经找到解决的办法是使用临时表，然后使我使用添加通过我最后的SQL SELECT语句暴露表列，然后使用它们作为元数据在EF代替使用SQL临时变量函数导入功能。

下面是我的成功合作SP的例子。

 使用[TESTDB] 
 GO 
 / ******对象：StoredProcedure的[DBO] [sp_SoInfoDocs_Archive]脚本日期：2015年9月7日十时35分43秒****** / 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 ALTER PROCEDURE [DBO]。[sp_SoInfoDocs_Archive] 
 @searchText为nvarchar（200），
 @PageNumber INT，
 @PageSize INT，
 @out INT = 0输出
 
 AS 
 BEGIN 
 
 DECLARE @LowerLimit INT; 
设置@LowerLimit =（@PageNumber  -  1）* @PageSize; 
 DECLARE @UpperLimit INT; 
设置@UpperLimit = @PageNumber * @PageSize; 
 
  - 创建临时列变量
 
声明@temp表
（
的rowNum INT，
作业名文字，
的nodeID为nvarchar（50），
节点组文字，
 endStatus为nvarchar（10），
 RETURNCODE INT，
的startTime日期时间，
结束时间日期时间，
大馆SMALLDATETIME， 
的orderID为nvarchar（10），
状态为nvarchar（50），
 runCount整型，
雇主文本，
环状文本，
 soInfoID整型，
 docInfoID INT，
 existsNote位，
 noSysout位，
服务器名称为varchar（256），
的applicationName为nvarchar（255），
 memberName为nvarchar（255），
组名称为nvarchar（255），
 scheduleTableName为nvarchar（255），
 serverInfoID INT，
的applicationID INT，
 GROUPID INT，
 MEMBERID INT，
 scheduleTableID INT，
 docFileID INT，
表达式1 INT，
 docFileObject VARBINARY（MAX）
）
 
 INSERT INTO @temp 
选择ROW_NUMBER （）以上（按表达式1顺序）的rowNum，* 
从（
 
选择dbo.SOInfoArchive.jobName，
 dbo.SOInfoArchive.nodeID，
 dbo.SOInfoArchive .nodeGroup，
 dbo.SOInfoArchive.endStatus，
 dbo.SOInfoArchive.returnCode，
 dbo.SOInfoArchive.startTime，
 dbo.SOInfoArchive.endTime，
 dbo.SOInfoArchive .oDate，
 dbo.SOInfoArchive.orderID，
 dbo.SOInfoArchive.status，
 dbo.SOInfoArchive.runCount，
 dbo.SOInfoArchive.owner，
 dbo.SOInfoArchive .cyclic，
 dbo.SOInfoArchive.soInfoID，
 dbo.SOInfoArchive.docInfoID，
 dbo.SOInfoArchive.existsNote，
 dbo.SOInfoArchive.noSysout，
 dbo.serverInfo .serverName，
 dbo.Groups.label AS的applicationName，
 Groups_1.label AS memberName，
 Groups_2.label AS组名，
 Groups_3.label AS scheduleTableName，
 DBO .SOInfoArchive.serverInfoID，
 dbo.SOInfoArchive.applicationID，
 dbo.SOInfoArchive.groupID，
 dbo.SOInfoArchive.memberID，
 dbo.SOInfoArchive.scheduleTableID，
 DBO .docFile.docFileID，
 dbo.docInfo.docInfoID AS表达式1，
 dbo.docFile.docFileObject 
从dbo.SOInfoArchive INNER JOIN 
 dbo.serverInfo ON dbo.SOInfoArchive.serverInfoID = dbo.serverInfo.serverInfoID INNER JOIN 
 dbo.docInfo ON dbo.SOInfoArchive.docInfoID = dbo.docInfo.docInfoID INNER JOIN 
 dbo.docFile ON dbo.docInfo.docFileID = dbo.docFile.docFileID LEFT OUTER JOIN 
 dbo.Groups AS Groups_3 ON dbo.SOInfoArchive.scheduleTableID = Groups_3.ID LEFT OUTER JOIN 
 dbo.Groups AS Groups_1 ON dbo.SOInfoArchive.memberID = Groups_1.ID LEFT OUTER JOIN 
 DBO。组作为Groups_2 ON dbo.SOInfoArchive.groupID = Groups_2.ID LEFT OUTER JOIN 
 dbo.Groups ON dbo.SOInfoArchive.applicationID = dbo.Groups.ID 
 
 WHERE CONTAINS（docfileObject，@ SEARCHTEXT ）
）TBL 
 
  - 选择使我消耗下面列在EF 
 
元数据选择的rowNum，
服务器名，
作业名，
大馆，
 runCount，
的orderID，
的applicationName，
 memberName，
的nodeID，
 endStatus，
 RETURNCODE ，
的startTime，
结束时间，
状态，
老板，
 existsNote，
 docFileID 
从@temp WHERE的rowNum> @LowerLimit及的rowNum< = @UpperLimit 
 
 
 
端
  

因此，要回顾一下，我现在可以

• 1）导入存储过程在我的EDMX。




• 2）中的添加函数导入成功创建

一）sp_SoInfoDocs函数导入

二）sp_SoInfoDocs复杂类型

我现在可以成功地叫我的存储过程由于使用（TestEntities上下文=新TestEntities（））
{
串SEARCHTEXT =REM如下

   
 ObjectParameter总=新ObjectParameter（走出去的typeof（INT））; 
 
名单，LT; sp_SoInfoDocs_Result> LST = context.sp_SoInfoDocs（SEARCHTEXT，1，10，总）.ToList（）; 
 
的foreach（LST中VAR项）
 {
 Console.WriteLine（item.jobName ++ item.oDate ++ item.serverName ++项目.startTime ++ item.endTime）; 
 
} 
到Console.ReadLine（）; 
} 
  

和的结果的一个例子返回。

我现在成功地利用此基础上流程导入和在我看来显示动态创建的HTML表格的元数据。

如果任何人遇到类似的问题，我已经忘了充分解释为什么我采用了这种解决方案，我如何做它的工作〜然后请随时P.M.我，我会尽我所能解释的。

I have a similar problem as described in

EF can't infer return schema from Stored Procedure selecting from a #temp table

and I have created my stored procedure solution based on the solution described above BUT I am still getting a similar EF error and I really don't know why or understand how I can fix it.

A member of the type, 'rowNum', does not have a corresponding column in the data reader with the same name.

My specific error:

The data reader is incompatible with the specified 'TestModel.sp_SoInfoDocs_Result'. A member of the type, 'rowNum', does not have a corresponding column in the data reader with the same name.

My stored procedure:

ALTER PROCEDURE [dbo].[sp_SoInfoDocs] 
    @searchText nvarchar(200),
    @PageNumber int,
    @PageSize int
AS
BEGIN
    IF 1 = 2  
    BEGIN    
       SELECT
          cast(null as int )  as rowNum
          ,cast(null as text)    as serverName
          ,cast(null as text)    as jobName
          ,cast(null as DATETIME)    as oDate
          ,cast(null as int)    as runCount
          ,cast(null as nvarchar(10))    as orderID
          ,cast(null as text)    as applicationName
          ,cast(null as text)    as memberName
          ,cast(null as text)    as nodeID
          ,cast(null as nvarchar(10))    as endStatus
          ,cast(null as int)    as returnCode
          ,cast(null as DATETIME)    as startTime
          ,cast(null as DATETIME)    as endTime
          ,cast(null as nvarchar(50))    as status
          ,cast(null as text)    as owner
          ,cast(null as bit)    as existsNote
      WHERE
          1 = 2  
   END

DECLARE @LowerLimit int;
SET @LowerLimit = (@PageNumber - 1) * @PageSize;
DECLARE @UpperLimit int; 
SET @UpperLimit = @PageNumber * @PageSize;

PRINT CAST (@LowerLimit as varchar)  
PRINT CAST (@UpperLimit as varchar)

SELECT ROW_NUMBER() over (order by  Expr1) as rowNum, * 
into #temp
from ( 

SELECT     dbo.SOInfo.jobName, dbo.SOInfo.nodeID, dbo.SOInfo.nodeGroup, dbo.SOInfo.endStatus, dbo.SOInfo.returnCode, dbo.SOInfo.startTime, dbo.SOInfo.endTime, 
                      dbo.SOInfo.oDate, dbo.SOInfo.orderID, dbo.SOInfo.status, dbo.SOInfo.runCount, dbo.SOInfo.owner, dbo.SOInfo.cyclic, dbo.SOInfo.soInfoID, dbo.SOInfo.docInfoID, 
                      dbo.SOInfo.existsNote, dbo.SOInfo.noSysout, dbo.serverInfo.serverName, dbo.Groups.label AS applicationName, Groups_1.label AS memberName, 
                      Groups_2.label AS groupName, Groups_3.label AS scheduleTableName, dbo.SOInfo.serverInfoID, dbo.SOInfo.applicationID, dbo.SOInfo.groupID, 
                      dbo.SOInfo.memberID, dbo.SOInfo.scheduleTableID, dbo.docFile.docFileID, dbo.docInfo.docInfoID AS Expr1, dbo.docFile.docFileObject
FROM         dbo.SOInfo INNER JOIN
                      dbo.serverInfo ON dbo.SOInfo.serverInfoID = dbo.serverInfo.serverInfoID INNER JOIN
                      dbo.docInfo ON dbo.SOInfo.docInfoID = dbo.docInfo.docInfoID INNER JOIN
                      dbo.docFile ON dbo.docInfo.docFileID = dbo.docFile.docFileID LEFT OUTER JOIN
                      dbo.Groups AS Groups_3 ON dbo.SOInfo.scheduleTableID = Groups_3.ID LEFT OUTER JOIN
                      dbo.Groups AS Groups_1 ON dbo.SOInfo.memberID = Groups_1.ID LEFT OUTER JOIN
                      dbo.Groups AS Groups_2 ON dbo.SOInfo.groupID = Groups_2.ID LEFT OUTER JOIN
                      dbo.Groups ON dbo.SOInfo.applicationID = dbo.Groups.ID

                      WHERE CONTAINS (docfileObject,@searchText) 
) tbl

  SELECT Count(1) FROM #temp

  SELECT rowNum, serverName, jobName ,oDate,runCount,orderID,applicationName,memberName,nodeID, endStatus, returnCode,startTime,endTime,status,owner,existsNote      
  FROM #temp WHERE rowNum > @LowerLimit AND rowNum <= @UpperLimit 
END

My overall goals are:

1. search through clustered indexed table (docInfo) and find all rows that contain a specific search string value

2. at the same time capture metadata from other tables associated with each docInfo object

3. The results of actions (1) and (2) above are written to a #temp table to which I then add a rowNum column to enable me to introduce paging i.e.

4. introduce paging for the number of metadata results that can be returned at any one time, based on supplied PageNumber and PageSize variables.

What does work

1. I am able to successfully create the stored procedure.

2. Within SSMS I am able to successfully execute the stored procedure and it delivers the results I expect, here's an example

3. Within EF I have been able to update and import the stored procedure by updating from database

4. Within EF I am then able to see the Function Imports and can see the Mapping

5. Within ED I am then able to see the generated complex types

6. I use the following code to call the process

   using (TestEntities context = new TestEntities())
   {
       List<sp_SoInfoDocs_Result> lst = context.sp_SoInfoDocs(searchText, 1, 10).ToList();
   }
   

7. I compile and run my solution and get the following error from EF

   'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll

   Additional information: The data reader is incompatible with the specified 'SysviewModel.sp_SoInfoDocs_Result'. A member of the type, 'rowNum', does not have a corresponding column in the data reader with the same name.

I am very much a novice / basic user when it comes to both SSMS / SQL and EF, this has stretched me as far as I understand / can go and I really don't know where to turn to next in order to resolve this problem.

I've searched extensively through SO and can see others who have had similar problems and have tried the solutions suggested but nothing seems to work for me.

I really would be very very grateful to anyone who could help me understand

1. what is it that is wrong / I've done wrong?

2. is there a better approach to achieve what I need?

3. ideas as to how I can fix this.

Thanks in advance

解决方案

The solution I have found is to use SQL temporary variables in stead of using temporary tables which then enables me to expose the table columns via my final SQL Select statement and then consume them as meta data in EF using the "Add Function Imports" function.

Here's an example of my successfully working sp.

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_SoInfoDocs_Archive]    Script Date: 09/07/2015 10:35:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_SoInfoDocs_Archive] 
    @searchText nvarchar(200),
    @PageNumber int,
    @PageSize int,
    @out int = 0 output

AS
BEGIN

DECLARE @LowerLimit int;
SET @LowerLimit = (@PageNumber - 1) * @PageSize;
DECLARE @UpperLimit int; 
SET @UpperLimit = @PageNumber * @PageSize;

-- Create temporary column variables

Declare @temp TABLE  
(
rowNum INT,
jobName text, 
nodeID nvarchar(50),
nodeGroup text,
endStatus nvarchar(10),
returnCode int,
startTime datetime,
endTime datetime,
oDate smalldatetime, 
orderID nvarchar(10),
status nvarchar(50),
runCount int,
owner text,
cyclic text,
soInfoID int,
docInfoID int,
existsNote bit,
noSysout bit,
serverName varchar(256),
applicationName nvarchar(255),
memberName nvarchar(255), 
groupName nvarchar(255),
scheduleTableName nvarchar(255),
serverInfoID int,
applicationID int,
groupID int, 
memberID int,
scheduleTableID int,
docFileID int,
Expr1 int,
docFileObject varbinary(MAX)
)

INSERT INTO @temp 
SELECT ROW_NUMBER() over (order by  Expr1) as rowNum, * 
from ( 

SELECT     dbo.SOInfoArchive.jobName, 
dbo.SOInfoArchive.nodeID,
dbo.SOInfoArchive.nodeGroup,
dbo.SOInfoArchive.endStatus,
dbo.SOInfoArchive.returnCode,
dbo.SOInfoArchive.startTime,
dbo.SOInfoArchive.endTime,
dbo.SOInfoArchive.oDate, 
dbo.SOInfoArchive.orderID,
dbo.SOInfoArchive.status,
dbo.SOInfoArchive.runCount,
dbo.SOInfoArchive.owner,
dbo.SOInfoArchive.cyclic,
dbo.SOInfoArchive.soInfoID,
dbo.SOInfoArchive.docInfoID,
dbo.SOInfoArchive.existsNote,
dbo.SOInfoArchive.noSysout,
dbo.serverInfo.serverName,
dbo.Groups.label AS applicationName,
Groups_1.label AS memberName, 
Groups_2.label AS groupName,
Groups_3.label AS scheduleTableName,
dbo.SOInfoArchive.serverInfoID,
dbo.SOInfoArchive.applicationID,
dbo.SOInfoArchive.groupID, 
dbo.SOInfoArchive.memberID,
dbo.SOInfoArchive.scheduleTableID,
dbo.docFile.docFileID,
dbo.docInfo.docInfoID AS Expr1,
dbo.docFile.docFileObject
FROM         dbo.SOInfoArchive INNER JOIN
                      dbo.serverInfo ON dbo.SOInfoArchive.serverInfoID = dbo.serverInfo.serverInfoID INNER JOIN
                      dbo.docInfo ON dbo.SOInfoArchive.docInfoID = dbo.docInfo.docInfoID INNER JOIN
                      dbo.docFile ON dbo.docInfo.docFileID = dbo.docFile.docFileID LEFT OUTER JOIN
                      dbo.Groups AS Groups_3 ON dbo.SOInfoArchive.scheduleTableID = Groups_3.ID LEFT OUTER JOIN
                      dbo.Groups AS Groups_1 ON dbo.SOInfoArchive.memberID = Groups_1.ID LEFT OUTER JOIN
                      dbo.Groups AS Groups_2 ON dbo.SOInfoArchive.groupID = Groups_2.ID LEFT OUTER JOIN
                      dbo.Groups ON dbo.SOInfoArchive.applicationID = dbo.Groups.ID

                      WHERE CONTAINS (docfileObject,@searchText) 
) tbl

-- Select enables me to consume the following columns as meta data in EF

  SELECT rowNum,
         serverName,
         jobName ,
          oDate,
          runCount,
          orderID,
          applicationName,
          memberName,
          nodeID,
          endStatus,
          returnCode,
          startTime,
          endTime,
          status,
          owner,
          existsNote,
          docFileID      
  FROM @temp WHERE rowNum > @LowerLimit AND rowNum <= @UpperLimit 



END

So to recap, I can now

• 1) Import the stored procedure into my EDMX.

• 2) The "Add Function Import" successfully creates

a) sp_SoInfoDocs Function Imports

b) sp_SoInfoDocs Complex Types

I can now successfully call my stored procedure as follows

     using (TestEntities context = new TestEntities())
    {
          string searchText = "rem";
          ObjectParameter total = new ObjectParameter("out",typeof(int));

          List<sp_SoInfoDocs_Result> lst = context.sp_SoInfoDocs(searchText, 1, 10, total).ToList();

          foreach (var item in lst)
          {
            Console.WriteLine(item.jobName + " " + item.oDate + " " + item.serverName + " " + item.startTime + " " + item.endTime);

          }
           Console.ReadLine();
    } 

And an example of the results returned.

I am now successfully using the basis of this process to import and display the metadata in a dynamically created HTML table in my View.

If anyone else is experiencing similar problems and I have neglected to explain fully why I adopted this solution and how I made it work ~ then please feel free to P.M. me and I'll do my best to explain.

这篇关于（附加）EF不能从#temp表从存储过程中选择的推断回报模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！