title: (Additional) EF can't infer return schema from stored procedure selecting from a #temp table
body: <p>I have a similar problem as described in </p>
  
  <p><a href="https://stackoverflow.com/questions/16593473/ef-cant-infer-return-schema-from-stored-procedure-selecting-from-a-temp-table">EF can&#39;t infer return schema from Stored Procedure selecting from a #temp table</a> </p>
  
  <p>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.  </p>
  
  <blockquote>
    <p>A member of the type, 'rowNum', does not have a corresponding column in the data reader with the same name.</p>
  </blockquote>
  
  <p>My specific error: </p>
  
  <blockquote>
    <p>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.  </p>
  </blockquote>
  
  <p>My stored procedure:</p>
  
  <pre><code>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 &gt; @LowerLimit AND rowNum &lt;= @UpperLimit 
  END
  </code></pre>
  
  <p>My overall goals are:</p>
  
  <ol>
  <li><p>search through clustered indexed table (docInfo) and find all rows that contain a specific search string value  </p></li>
  <li><p>at the same time capture metadata from other tables associated with each docInfo object   </p></li>
  <li><p>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.  </p></li>
  <li><p>introduce paging for the number of metadata results that can be returned at any one time, based on supplied PageNumber and PageSize variables.</p></li>
  </ol>
  
  <p><strong>What does work</strong></p>
  
  <ol>
  <li><p>I am able to successfully create the stored procedure.  </p></li>
  <li><p>Within SSMS I am able to successfully execute the stored procedure and it delivers the results I expect, here's an example </p>
  
  <p><a href="https://i.stack.imgur.com/1M2ik.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1M2ik.png" alt="enter image description here"></a></p></li>
  <li><p>Within EF I have been able to update and import the stored procedure by updating from database </p>
  
  <p><a href="https://i.stack.imgur.com/eiKFW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eiKFW.png" alt="enter image description here"></a></p></li>
  <li><p>Within EF I am then able to see the Function Imports and can see the Mapping </p>
  
  <p><a href="https://i.stack.imgur.com/hXtP7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hXtP7.png" alt="enter image description here"></a> </p>
  
  <p><a href="https://i.stack.imgur.com/8brXi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8brXi.png" alt="enter image description here"></a></p></li>
  <li><p>Within ED I am then able to see the generated complex types </p>
  
  <p><a href="https://i.stack.imgur.com/IqFo3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IqFo3.png" alt="enter image description here"></a></p></li>
  <li><p>I use the following code to call the process </p>
  
  <pre><code>using (TestEntities context = new TestEntities())
  {
      List&lt;sp_SoInfoDocs_Result&gt; lst = context.sp_SoInfoDocs(searchText, 1, 10).ToList();
  }
  </code></pre></li>
  <li><p>I compile and run my solution and get the following error from EF</p>
  
  <blockquote>
    <p>'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll</p>
    
    <p>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.</p>
  </blockquote></li>
  </ol>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>I really would be very very grateful to anyone who could help me understand </p>
  
  <ol>
  <li><p>what is it that is wrong / I've done wrong?</p></li>
  <li><p>is there a better approach to achieve what I need?</p></li>
  <li><p>ideas as to how I can fix this. </p></li>
  </ol>
  
  <p>Thanks in advance </p>