CODEWITHSUNDEEP
c#sql-serverentity-frameworkef-core-2.0
dumbchemistry
dumbchemistry

Reputation: 405

Access result from SELECT statement in stored procedure using EF Core and ExecuteSqlCommandAsync

I'm trying to use ExecuteSqlCommandAsync from EF Core to get the result of a select query in a somewhat odd legacy stored procedure that has an empty RETURN statement.

The stored procedure looks like this:

CREATE PROCEDURE [dbo].[SelectNextCounter]
(
@CounterName nvarchar(50)
)
AS
    UPDATE  counter
    SET NextValue = NextValue + 1
    WHERE   CounterName = @CounterName
    SELECT  NextValue
    FROM    counter
    WHERE   counterName = @CounterName
RETURN
GO

Using this code I'm able to access the RETURN value of the stored procedure (though what I'm really interested in is NextValue): 

var counterName = new SqlParameter
{
    ParameterName = "@CounterName",
    Value = "CustomerNumber",
    SqlDbType = SqlDbType.NVarChar
};
var returnValue = new SqlParameter
{
    ParameterName = "@return_value",
    Direction = ParameterDirection.Output,
    SqlDbType = SqlDbType.Int
};

await _context
    .Database
    .ExecuteSqlCommandAsync(
        "EXEC @return_value = SelectNextCounter @CounterName", counterName, returnValue
    );

I can then get the value through returnValue.Value (which is always 0, because of the empty RETURN statement). However, is there any way to get the value from NextValue using EF Core? Seems like FromSql might work but I'm really just interested in getting a single value back, not an entity. This answer seems to do what I want but I would rather use EF Core than SqlCommand, if possible.

Upvotes: 1

Views: 1899

Answers (3)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

Also you should change the query to use UPDATE … OUTPUT so that two sessions won't get the same value. EG

CREATE PROCEDURE [dbo].[SelectNextCounter]
(
@CounterName nvarchar(50)
)
AS

    UPDATE  counter
    SET NextValue = NextValue + 1
    OUTPUT INSERTED.NextValue
    WHERE   CounterName = @CounterName

GO

Upvotes: 0

Ivan Stoev
Ivan Stoev

Reputation: 205769

It has to be FromSql. Not possible in EF Core 2.0. Possible in EF Core 2.1, but a bit more complicated than needed, because SQL queries returning primitive types are still not supported. So you need to utilize Query Type like this:

First you need to create a "query type" class which will hold the result:

public class SelectNextCounterResult
{
    public int NextValue { get; set; }
}

Second you need to register it in your OnModelCreating override:

modelBuilder.Query<SelectNextCounterResult>();

Then you can use it to call your SP

var counterName = "CustomerNumber";
var result = await context.Query<SelectNextCounterResult>()
    .FromSql($"SelectNextCounter {counterName}")
    .FirstAsync();

Upvotes: 4

jkramer
jkramer

Reputation: 91

You could do something like the following. I wrote my example to run only in SQL but the concept should be the same for the c# portion. I would recommend using SqlCommand if you ever intend this to be extensible. Return also only every works with integers.

Since you can return any integer value, you can just say Return [integer] https://learn.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql?view=sql-server-2017 

CREATE PROCEDURE [dbo].TestProc
AS
DECLARE @returnVal INT = (SELECT 2)
RETURN @returnVal
GO

DECLARE @newReturnVal INT
EXEC @newReturnVal = [dbo].TestProc
SELECT @newReturnVal

Upvotes: 1

Related Questions