How to call stored procedure which returns data using Entity Framework and .Net Core 1.1

Question

I am trying to call a stored procedures which returns a value. But for some reason, it's not returning the value and it's instead throwing an error:

I am calling the stored procedure like this:

var arTransactionid = context.Set<ARTransaction>()
                             .FromSql("core.ARTransaction_Insert @PersonID = {0},@ContractID = {1},@TransactionCodeID = {2},@TransactionDate = {3}," +
        "@TransactionDesc = {4},@Amount = {5},@CurrencyID = {6},@ExchangeRate = {7}," +
        "@BaseAmount = {8},@PostedDate = {9},@DueDate = {10},@Reference = {11}," +
        "@Reference2 = {12},@Reversal = {13},@BaseAdjustment = {14},@BatchID = {15}," +
        "@ParentTransactionID = {16},@InvoiceID = {17},@UserID = {18}", 46736, null, 197, "2017-08-25 00:00:00 -05:00", null, 501.0000, 2, 1, 501.0000, null, null, null, null, 0, 0, null, 0, null, 3052)
                             .FirstOrDefault();

This is my model class which is supposed to be filled with the return value from the stored procedure:

public class ARTransaction
{
    public String arTransactionID { set; get; }
}

This is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [core].[ARtransaction_Insert]
    @PersonID int,
    @ContractID int,
    @TransactionCodeID int,
    @TransactionDate date,
    @TransactionDesc nvarchar(100),
    @Amount money,
    @CurrencyID int,
    @ExchangeRate decimal(18,6),
    @BaseAmount money,
    @PostedDate DATE = null,
    @DueDate date,
    @Reference nvarchar(50),
    @Reference2 nvarchar(50),
    @Reversal BIT = 0,
    @BaseAdjustment BIT = 0,
    @BatchID int,
    @ParentTransactionID int,
    @InvoiceID INT = null,
    @UserID INT,
    @SessionGUID UNIQUEIDENTIFIER = null
AS
    SET NOCOUNT ON

    DECLARE @ARtransactionID INT,
            @Valid INT,
            @ValidMessage NVARCHAR(100)

    --More code goes here ***********

    RETURN @ARtransactionID

If I call the stored procedure in SQL Server Management Studio, it doesn't return the value - just a message.

In order to obtain a result, I declare a variable and assign it to the stored procedure.

DECLARE @arTransctionInserted int;

EXEC @arTransctionInserted = core.ARTransaction_Insert 
          @PersonID = 46736,
          @ContractID = NULL,
          @TransactionCodeID = 197,
          @TransactionDate = '2017-08-25 00:00:00 -05:00',
          @TransactionDesc = NULL,
          @Amount = 500.0000,
          @CurrencyID = 2,
          @ExchangeRate = 1, 
          @BaseAmount = 500.0000,
          @PostedDate = NULL,
          @DueDate = NULL,
          @Reference = NULL,
          @Reference2 = NULL,
          @Reversal = 0,
          @BaseAdjustment = 0,
          @BatchID = NULL,
          @ParentTransactionID = 0,
          @InvoiceID = NULL,
          @UserID = 3052;

SELECT @arTransctionInserted as IDTransaction;

How can I call the stored procedure to return the value?

context.Set<ARTransaction>().FromSql("")

I already tried to called it including all the query, but the error keeps throwing.

var arTransactionid = context.Set<ARTransaction>().FromSql("DECLARE @arTransctionInserted int; exec @arTransctionInserted = core.ARTransaction_Insert @PersonID = {0},@ContractID = {1},@TransactionCodeID = {2},@TransactionDate = {3}," +
               "@TransactionDesc = {4},@Amount = {5},@CurrencyID = {6},@ExchangeRate = {7}," +
               "@BaseAmount = {8},@PostedDate = {9},@DueDate = {10},@Reference = {11}," +
               "@Reference2 = {12},@Reversal = {13},@BaseAdjustment = {14},@BatchID = {15}," +
               "@ParentTransactionID = {16},@InvoiceID = {17},@UserID = {18};SELECT @arTransctionInserted as IDTransaction;", 46736, null, 197, "2017-08-25 00:00:00 -05:00", null, 501.0000, 2, 1, 501.0000, null, null, null, null, 0, 0, null, 0, null, 3052).FirstOrDefault();

Answer 1

RETURN will not output the SP values and will only return the data to be put on a variable on the calling database object. This is why EXECUTE works but running the SP itself will just return a "Command completed successfully."

Use SELECT @ARtransactionID; as this should output the ID you are looking for.