Why does EF Core always return -1 with this stored procedure?

Question

Why does EF Core always return -1 with this stored procedure?

.net-core entity-framework entity-framework-core localdb

Question

I am trying to use EF Core (latest release) against a Local 2016 DB and I am getting -1 back every time. I don't know what I have done wrong?

I know that it is reaching the database. I checked that.

int returnCode = _dbContext.Database.ExecuteSqlCommand("CheckReceivedNotificationDuplicate @p0, @p1, @p2", 
parameters: new[] { sendMessage.MESSAGE_TEMPLATE_NAME, sendMessage.MESSAGE_SUBJECT, sendMessage.MESSAGE_TEXT_SUMMARY });

Stored Procedure:

ALTER PROCEDURE [dbo].[CheckReceivedNotificationDuplicate]
@MESSAGE_TEMPLATE_NAME nvarchar(100),
@MESSAGE_SUBJECT nvarchar(255),
@MESSAGE_TEXT_SUMMARY nvarchar(4000)
AS  DECLARE @NMID Int
BEGIN   
IF  EXISTS (SELECT 1 FROM dbo.Notification WHERE MESSAGE_TEMPLATE_NAME = @MESSAGE_TEMPLATE_NAME
                and MESSAGE_SUBJECT = @MESSAGE_SUBJECT AND ReceiveTimeEST > dateadd(minute,-5, dbo.GetGMTtoEST(getutcdate())))
BEGIN 
    IF  EXISTS (SELECT 1 FROM dbo.Notification WHERE MESSAGE_TEMPLATE_NAME = @MESSAGE_TEMPLATE_NAME
                and MESSAGE_SUBJECT = @MESSAGE_SUBJECT AND MESSAGE_TEXT_SUMMARY = @MESSAGE_TEXT_SUMMARY
                AND ReceiveTimeEST > dateadd(minute,-5, dbo.GetGMTtoEST(getutcdate())))
        RETURN -99
    ELSE
        RETURN 0
END
ELSE
    RETURN 0
END

Paul Duer

View more on Stack Overflow

Ilya Chumakov · Accepted Answer · 5/13/2017 1:45:02 PM

ExecuteSqlCommand calls IRelationalCommand.ExecuteNonQuery internally. It returns the number of rows affected.

To return an arbitrary value from stored procedure, add output parameter to its declaration and to ExecuteSqlCommand call:

var outParameter = new SqlParameter("@outParameter", DbType.Int32)
{
    Direction = ParameterDirection.Output
};

context.Database.ExecuteSqlCommand("exec GetFoo @outParameter OUT", outParameter);

Why does EF Core always return -1 with this stored procedure?

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related