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
1
2
4/29/2017 10:28:36 AM

Accepted Answer

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);
4
5/13/2017 1:45:02 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow