The required column was not present in the results of a 'FromSql' operation :. FromSqlInterpolated

Question

[Pre-Amble]

When I upgraded my api from .netcore2.1 to .netcore3.1 I found I needed to change my Entity Framework code considerably.

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.

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.

[Question]

I have the following code to call a stored procedure that returns an integer

The code works in a server side unit test but fails when it is called via the api or via SwaggerUI

var obj = connect.Ints.FromSqlInterpolated<intDto>(@$"Set NOCOUNT ON
          declare @num int 
          exec @num = spGetDefaultID {userName}  
          select @num as num").ToList();

id = (int)obj.First().num;

Where connect is my DbContext containing

public DbSet<intDto> Ints { get; set; }  // I actually don't want a table

with

public class intDto
{
    public int num { get; set; }
}

I followed the advice to create a dummy view so that I would not get an unwanted table in my database.

and have

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<intDto>).HasNoKey().ToView("view_name_that_doesnt_exist");

The call stack is

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) 

Note I have edited this question heavily because at one point I had the api working , but no longer do.

I found that I had to use .ToList() or I got an error

System.InvalidOperationException: FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it. Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side.

[Update]

I popped the following code in and it did not fail

 var obj = db.Ints.FromSqlInterpolated(@$"Set NOCOUNT on
                                       select count(*) as num from people 
                                       where email like  {name} ").ToList();

so next step is to try making a new stored procedure

Alternatively maybe it is the database version..

Interestingly the unit test fails unless I have

Microsoft.EntityFrameworkCore.Relational.dll

referenced in the project being tested. Even though I do not explicitly use that.

The error message in this scenario is

System.MissingMethodException: 'Method not found:'System.Linq.IQueriable '1<!!O> 
Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.
FromSqlInterpolated(Microsoft.EntityFrameworkCOre.DbSet'1<!!0>, 
System.FormattableString)'

I tried changing my code to

var obj = connect.Ints.FromSqlInterpolated<intDto>(@$"declare @num int 
          exec @num = spGetDefaultID {userName}").ToList();

But my unit test gave an error

The underlying reader doesn't have as many fields as expected

[Update]

The remote stored procedure returns data using a result set.

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

Answer 1

When I tried calling the stored proc using ssms as suggested by Zohar, I was supprised to see 2 results output

When I removed the unnecessary

select @CartID

the problem was solved.