CODEWITHSUNDEEP
c#entity-framework-core
drmssst
drmssst

Reputation: 133

EF Core 2 executing Stored Procedure with OUTPUT param

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);
}

Upvotes: 3

Views: 5582

Answers (1)

drmssst
drmssst

Reputation: 133

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.

HTH!

Upvotes: 5

Related Questions