title: The required column was not present in the results of a 'FromSql' operation :. FromSqlInterpolated
body: <p>[Pre-Amble]</p>
  
  <p>When I upgraded my api from .netcore2.1 to .netcore3.1 I found I needed to change my Entity Framework code considerably.</p>
  
  <p>What I didn't realise was that I had a bug in a stored procedure in my remote database, that was not being expressed in my .netcore2.1 code.</p>
  
  <p>The error messages I experienced did not point me in the direction of checking inside the stored procedure.  I leave the final form of my question in case searching for the error messages helps someone.</p>
  
  <p>[Question]</p>
  
  <p>I have the following code to call a stored procedure that returns an integer </p>
  
  <p>The code works in a server side unit test but fails when it is called via the api or via SwaggerUI</p>
  
  <pre><code>var obj = connect.Ints.FromSqlInterpolated&lt;intDto&gt;(@$"Set NOCOUNT ON
            declare @num int 
            exec @num = spGetDefaultID {userName}  
            select @num as num").ToList();
  
  id = (int)obj.First().num;
  </code></pre>
  
  <p>Where connect is my DbContext containing</p>
  
  <pre><code>public DbSet&lt;intDto&gt; Ints { get; set; }  // I actually don't want a table
  </code></pre>
  
  <p>with</p>
  
  <pre><code>public class intDto
  {
      public int num { get; set; }
  }
  </code></pre>
  
  <p>I followed <a href="https://stackoverflow.com/questions/59427708/ef-core-3-dbquery-equivalent-functionality">the advice to create a dummy view</a> so that I would not get an unwanted table in my database.</p>
  
  <p>and have</p>
  
  <pre><code>    protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          base.OnModelCreating(modelBuilder);
          modelBuilder.Entity&lt;intDto&gt;).HasNoKey().ToView("view_name_that_doesnt_exist");
  </code></pre>
  
  <p>The call stack is</p>
  
  <pre><code>System.InvalidOperationException: The required column 'num' was not present in the results of a 'FromSql' operation.
  at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.InitializeFields()
  at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.Initialize(DbDataReader reader, IReadOnlyList`1 columns)
  at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.Initialize(IReadOnlyList`1 columns) 
  at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
  at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
  at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
  at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
  at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)   
  at MyAPi.Job_Management.JobDataRead.GetCartIdForUser(ApiDbContext connect, String userName) 
  </code></pre>
  
  <p>Note I have edited this question heavily because at one point I had the api working , but no longer do.</p>
  
  <p>I found that I had to use .ToList() or I got an error</p>
  
  <blockquote>
    <p>System.InvalidOperationException: FromSqlRaw or FromSqlInterpolated
    was called with non-composable SQL and with a query composing over it.
    Consider calling <code>AsEnumerable</code> after the FromSqlRaw or
    FromSqlInterpolated method to perform the composition on the client
    side.</p>
  </blockquote>
  
  <p>[Update]</p>
  
  <p>I popped the following code in and it did not fail</p>
  
  <pre><code> var obj = db.Ints.FromSqlInterpolated(@$"Set NOCOUNT on
                                         select count(*) as num from people 
                                         where email like  {name} ").ToList();
  </code></pre>
  
  <p>so next step is to try making a new stored procedure</p>
  
  <p>Alternatively maybe it is the database version..</p>
  
  <p>Interestingly the unit test fails unless I have </p>
  
  <pre><code>Microsoft.EntityFrameworkCore.Relational.dll
  </code></pre>
  
  <p>referenced in the project being tested. Even though I do not explicitly use that.</p>
  
  <p>The error message in this scenario is </p>
  
  <pre><code>System.MissingMethodException: 'Method not found:'System.Linq.IQueriable '1&lt;!!O&gt; 
  Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.
  FromSqlInterpolated(Microsoft.EntityFrameworkCOre.DbSet'1&lt;!!0&gt;, 
  System.FormattableString)'
  </code></pre>
  
  <p>I tried changing my code to </p>
  
  <pre><code>var obj = connect.Ints.FromSqlInterpolated&lt;intDto&gt;(@$"declare @num int 
            exec @num = spGetDefaultID {userName}").ToList();
  </code></pre>
  
  <p>But my unit test gave an error</p>
  
  <pre><code>The underlying reader doesn't have as many fields as expected
  </code></pre>
  
  <p>[Update]</p>
  
  <p>The remote stored procedure returns data using a result set.</p>
  
  <pre><code>create PROCEDURE [dbo].[spGetDefaultID] 
  @email varchar(300)
  AS
  BEGIN
      SET NOCOUNT ON;
      declare @cartID int
      select @CartID= id from people where email = @email   /* simplified */
      select @CartID /* this line was not present in my local database */
      return @CartID  
  END
  </code></pre>