Syntax for calling a stored procedure in .NET Core without parameters

Question

I have created a stored procedure that returns a recordset model which joins several different tables in a single database. I have scoured the internet for information regarding "proper syntax for calling a stored procedure from mvc 6 C# without parameters" and have learned several things.

Firstly, there used to be what looked like understandable answers, to wit: "ExecuteSqlCommand " and "ExecuteSqlCommandAsync ", which, evidently are no longer used. Their replacements are explained here: [https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#fromsql][1]. They seem to be limited to "FromSql/FromSqlRaw" (which returns a recordset model) and "ExecuteSqlRaw/ExecuteSqlRawAsync()" which returns an integer with a specified meaning.

The second thing is that, everywhere examples of "before and after" are given, the example without parameters are skipped (as in all of the MS docs).

And thirdly, all of the examples that return a recordset model with data seem tied to a table, such as: "var students = context.Students.FromSql("GetStudents 'Bill'").ToList();" And, as stored procedures are stored in their own directories, can reference any tables, multiple tables, or even no tables, I don't understand this relationship requirement in calling them. (such as here: [https://www.entityframeworktutorial.net/efcore/working-with-stored-procedure-in-ef-core.aspx][2] var students = context.Students.FromSql("GetStudents 'Bill'").ToList();) Or maybe they are models (since in this entity framework, everything seems to have the exact same name)... But what if your stored procedure isn't returning a recordset tied to a model. Do you have to create a new model just for the output of this stored procedure? I tried that, and it didn't seem to help.

So, my fundamental question is, how do I call a stored procedure without any parameters that returns a recordset model with data?

return await _context.(what goes here?).ExecuteSqlRaw("EXEC MyStoredProcedure").ToListAsync(); return await _context.ReturnModel.ExecuteSqlRaw("EXEC? MyStoredProcedure").ToListAsync();

Updated Code:

Added Model

public class InquiryQuote
     {
        public Inquiry inquiry { get; set; }
        public int QuoteID { get; set; } = 0;
     }

Added DBSet:

public virtual DbSet<InquiryQuote> InquiryQuotes { get; set; } = null!;
    

And updated the calling controller:

        // GET: api/Inquiries
        [HttpGet]
        public async Task<ActionResult<IEnumerable<InquiryQuote>>> GetInquiries()
        {
            //return await _context.Inquiries.ToListAsync();
            //return await _context.Inquiries.Where(i => i.YNDeleted == false).ToListAsync();

          //  var IQ = await _context.InquiryQuotes.FromSqlRaw("GetInquiryList").ToListAsync();
            var IQ = await _context.InquiryQuotes.FromSqlRaw("EXEC GetInquiryList").ToListAsync();
            return Ok(IQ);
        }

Both versions of "IQ" return the same results:

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
   at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
   at Microsoft.Data.SqlClient.SqlBuffer.get_Int32()
   at Microsoft.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
   at lambda_method17(Closure , QueryContext , DbDataReader , Int32[] )
...

[And here is the image of the stored procedure run directly from my development site:][1]

UPDATE (And partial answer to the question in the comments):

I am using the Entity Framework, and will be performing data manipulation prior to returning the newly created InquiryQuotes model from the stored procedure to be used in several views.

Why I am getting a SQL error thrown in postman (System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.) when calling the stored procedure directly from visual studio returns a "dataset" as shown in my image. Does it have something to do with additional values being returned from the stored procedure that are not being accounted for, like "DECLARE @return_value Int / SELECT @return_value as 'Return Value' ", or is this just a feature of executing it from VS. Since it has no input params, where is the NULL coming from? [1]: https://i.stack.imgur.com/RJhMr.png

Answer 1

I seem to have found the answer (but still don't know the why...)

I started breaking it down bit-by-bit. The procedure ran on sql, and ran remotely on Visual Studio when directly accessing sql, but not when called. So I replaced the complex stored procedure with a simple one that returned all fields from the inquiry table where the id matched an input variable (because I had LOTS) of examples for that.

Stored Procedure:

CREATE PROCEDURE [dbo].[GetInquiry]
    @InquiryID int = 0
AS
BEGIN
SET NOCOUNT ON

    select i.*
    FROM dbo.Inquiries i  
    WHERE i.YNDeleted = 0 AND i.InquiryId = @InquiryID
END

And the controller method (with the InquiryQuote model modified to eliminate the "quote" requirement:

    public async Task<ActionResult<IEnumerable<InquiryQuote>>> GetInquiries()
    {
        //return await _context.Inquiries.ToListAsync();
        //return await _context.Inquiries.Where(i => i.YNDeleted == false).ToListAsync();

        SqlParameter ID = new SqlParameter();
        ID.Value = 0;
        var IQ = _context.InquiryQuotes.FromSqlRaw("GetInquiryList {0}", ID).ToList();
        //var IQ = await _context.InquiryQuotes.FromSqlRaw("dbo.GetInquiryList").ToListAsync();
        return IQ;
}

And (after a bit of tweaking) it returned a JSON result of the inquiry data for the ID in Postman.

{
    "inquiryId": 9,
     (snip)
    "ynDeleted": false
}

So, once I had something that at least worked, I added just the quote back in to this simple model and ran it again

select i.*, 0 AS Quoteid
FROM dbo.Inquiries i  
LEFT JOIN  dbo.Quotes q ON i.InquiryId = q.InquiryId
WHERE i.YNDeleted = 0 AND i.InquiryId = @InquiryID

(I set the QuoteID to 0, because I had no data in the Quotes table yet).

AND the Model:

[Keyless]
public class InquiryQuote
{
    public Inquiry inquiry { get; set; }
    public bool QuoteID{ get; set; } = 0;
}

And ran it again, and the results were astonishing:

{
   inquiry:{null},
   QuoteID:0
}

I still don't understand why, but, evidently it must have been because of my LEFT join of the inquiryID from the Inquiry Table left joined with a null table returned null results - but when running on SQL, results were returned... The join in sql worked and returned results, but somewhere between sql and the API, the data was being nullified...

To test this theory, I updated my InquiryQuote model to put the "inquiry" data and "quoteid" at the same level, to wit:

public class InquiryQuote
{
    public int InquiryId { get; set; } = 0;
    (snip)
    public Boolean YNDeleted { get; set; } = false;
    public int QuoteID { get; set; } = 0;
}

and the entire results set was null...

So at that point, I figured it must have something to do with that LEFT JOIN with a table with no records. So I added a blank (default) entry into that table and, voila, the data I was expecting:

{
    "inquiryId": 9,
    (snip)
    "ynDeleted": false,
    "quoteID": 0
}

So, now I have a working way to call a stored procedure with one parameter!!

I then updated the stored procedure to deal with nulls from the database as so:

select i.*, ISNULL(q.QuoteId,0) AS Quoteid
FROM dbo.Inquiries i  
LEFT JOIN  dbo.Quotes q ON i.InquiryId = q.InquiryId
WHERE i.YNDeleted = 0 AND i.InquiryId = @InquiryID 

And now am returning correct data.

I still don't know why the stored procedure runs in sql and returns data, but returns a SQL error when run from the controller. That will require a deeper dive into the interconnectivity between the sql and the API and how errors are passed between the two. And I am pretty certain I will be able to figure out how to convert this call into one that uses no parameters.

Thank you everyone for your help.