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

查看：143 发布时间：2017/8/20 0:47:45 c# sql-server entity-framework

本文介绍了（附加）EF不能从存储过程中从#temp表中选择返回模式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有类似的问题，如

在EF内，我已经能够通过更新来更新和导入存储过程从数据库

在EF I内我可以看到功能导入，并可以看到映射

我可以看到生成的复杂类型

我使用以下代码调用过程

  using（TestEntities context = new TestEntities（））
 {
列表< sp_SoInfoDocs_Result> lst = context.sp_SoInfoDocs（searchText，1，10）.ToList（）; 
}

我编译并运行我的解决方案，并从EF中获取以下错误

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

附加信息：数据读取器与指定的SysviewModel.sp_SoInfoDocs_Result不兼容。类型rowNum的成员在数据读取器中没有相同的列。

我在SSMS / SQL和EF两方面都是一个新手/基本用户，这远远超出了我的理解/可以去，我真的不知道哪里转向下一步，以解决这个问题。

我通过SO广泛搜索，可以看到有类似问题的人，并尝试了解决方案，但对我来说似乎没有任何效果。

我真的非常非常感谢任何可以帮助我理解的人

有没有更好的方法来实现我所需要的？

关于我如何解决这个问题的想法。

提前感谢

解决方案

我发现的解决方案是使用SQL临时变量代替使用临时表，然后使我能够通过我的最终SQL Select语句公开表列，然后使用Add将它们作为元数据功能导入功能。

这是我成功工作的一个例子。

 使用[TestDB] 
 GO 
 / ******对象：StoredProcedure [dbo] 。[sp_SoInfoDocs_Archive]脚本日期：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输出
 
 AS 
 BEGIN 
 
 DECLARE @LowerLimit int; 
 SET @LowerLimit =（@PageNumber  -  1）* @PageSize; 
 DECLARE @UpperLimit int; 
 SET @UpperLimit = @PageNumber * @PageSize; 
 
  - 创建临时列变量
 
声明@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，
所有者文本，
循环文本，
 soInfoID int，
 docInfoID int 
 existsNote位，
 noSysout位，
 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 （）（（Expr1））作为rowNum，* 
从（
 
 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 
 
  - 选择使我能够使用以下列作为EF $ b $中的元数据b 
 SELECT rowNum，
 serverName ，
 jobName，
 oDate，
 runCount，
 orderID，
 applicationName，
 memberName，
 nodeID，
 endStatus，
 returnCode，
 startTime，
 endTime，
状态，
所有者，
 existsNote，
 docFileID 
 FROM @temp WHERE rowNum> ; @LowerLimit AND rowNum< = @UpperLimit 
 
 
 
 END

所以要重述，我现在可以

1）将存储过程导入到我的EDMX中。 >

2）添加功能导入成功创建

a）sp_SoInfoDocs函数导入

b）sp_SoInfoDocs复杂类型

现在我可以成功调用我的存储过程如下

  using（TestEntities context = new TestEntities（））
 {
 string searchText =rem ; 
 ObjectParameter total = new ObjectParameter（out，typeof（int））; 
 
列表< 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（）; 
}

并返回一个结果示例。

我现在成功地使用了此过程在我的View中的动态创建的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:

search through clustered indexed table (docInfo) and find all rows that contain a specific search string value
at the same time capture metadata from other tables associated with each docInfo object
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.
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

I am able to successfully create the stored procedure.
Within SSMS I am able to successfully execute the stored procedure and it delivers the results I expect, here's an example
Within EF I have been able to update and import the stored procedure by updating from database
Within EF I am then able to see the Function Imports and can see the Mapping
Within ED I am then able to see the generated complex types

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();
}

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

what is it that is wrong / I've done wrong?
is there a better approach to achieve what I need?
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屋！

查看全文

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

问题描述

相关文章

C#/.NET最新文章

热门教程

热门工具

登录关闭

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

问题描述

相关文章

C#/.NET最新文章

热门教程

热门工具

登录 关闭

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

登录关闭