(Additional) EF can't infer return schema from stored procedure selecting from a #temp table

Question

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

Answer 1

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.

Answer 2

Why to use temporary table or table variable at all. Table variables has many performance drawbacks like:

Table variables does not have distribution statistics, they will not trigger recompiles. Therefore, in many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). Temp tables may be a better solution in this case. Alternatively, for queries that join the table variable with other tables, use the RECOMPILE hint, which will cause the optimizer to use the correct cardinatlity for the table variable.

table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.

Use simple CTE:

ALTER PROCEDURE [dbo].[sp_SoInfoDocs_Archive] 
    @searchText NVARCHAR(200),
    @PageNumber INT,
    @PageSize   INT,
    @out        INT = 0 OUTPUT

AS
BEGIN
SET NOCOUNT ON;
DECLARE @LowerLimit INT = (@PageNumber - 1) * @PageSize;
DECLARE @UpperLimit INT = @PageNumber * @PageSize;


;WITH cte AS
(
  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
  JOIN dbo.serverInfo 
    ON dbo.SOInfoArchive.serverInfoID = dbo.serverInfo.serverInfoID
  JOIN dbo.docInfo 
   ON dbo.SOInfoArchive.docInfoID = dbo.docInfo.docInfoID
  JOIN dbo.docFile 
    ON dbo.docInfo.docFileID = dbo.docFile.docFileID
  LEFT JOIN dbo.Groups AS Groups_3 
    ON dbo.SOInfoArchive.scheduleTableID = Groups_3.ID
  LEFT JOIN dbo.Groups AS Groups_1 
    ON dbo.SOInfoArchive.memberID = Groups_1.ID
  LEFT JOIN dbo.Groups AS Groups_2 
    ON dbo.SOInfoArchive.groupID = Groups_2.ID
  LEFT JOIN dbo.Groups 
    ON dbo.SOInfoArchive.applicationID = dbo.Groups.ID
  WHERE CONTAINS (docfileObject,@searchText) 
),
 cte2 AS 
(
   SELECT ROW_NUMBER() OVER (ORDER BY  Expr1) AS rowNum, * 
   FROM cte
)
SELECT rowNum,
       serverName,
       jobName ,
       oDate,
       runCount,
       orderID,
       applicationName,
       memberName,
       nodeID,
       endStatus,
       returnCode,
       startTime,
       endTime,
       status,
       owner,
       existsNote,
       docFileID      
  FROM cte2 
  WHERE rowNum > @LowerLimit
    AND rowNum <= @UpperLimit 
END

Answer 3

Probably nobody cares anymore, but the reason it didn't work is that ROW_NUMBER() returns a BIGINT and your code defines the structure this way: cast(null as int) as rowNum