EF Core 2 executing Stored Procedure with OUTPUT param

c# entity-framework-core


Why is the output value not getting returned?

Using EF Core 2 (2.1.0-preview-final) as the platform for managing schema through code-first modelling, I wanted to add other database objects such as SPs. That was a journey itself, however the challenge under topic here is executing an SP using context.Database.ExecuteSqlCommandAsync() and retrieving the OUTPUT param value.

This is the code that successfully executes the SP, however the output value is never returned. You would think that setting the Direction of the parameter was sufficient.

public async Task AddAsync(Models.Filename entity)
    SqlParameter name = new SqlParameter("@Name", entity.Name);
    SqlParameter idOut = new SqlParameter
        ParameterName = "@Id",
        SqlDbType = System.Data.SqlDbType.BigInt,
        Direction = System.Data.ParameterDirection.Output
    using (var db = new MetaDataDbContext())
        await db.Database.ExecuteSqlCommandAsync("[dbo].[AddFilename] @Name, @Id", name, idOut);
    entity.Id = Convert.ToInt64(idOut.Value);
3/13/2018 5:14:07 PM

Accepted Answer

All that was needed to solve the issue was to change the T-SQL statement to be "[dbo].[AddFilename] @Name, @Id OUTPUT"

EF6 - ExecuteSqlCommandAsync - Get return parameter (declare scalar variable error) looked promising however it misses a critical piece namely that the T-SQL portion must include the OUTPUT keyword for the targeted parameter.

Why does EF Core always return -1 with this stored procedure?, in hindsight offers the solution, however it's name (title) didn't come up in my search results.


3/2/2018 10:15:10 PM

Related Questions


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