Entity Framework Core and calling a stored procedure

I thought this would be easy, there is a method called ExecuteSqlCommand which takes the stored proc name and an array of parameters. I wanted to pass in two parameters and get one back, no problem, I’ve done this with ADO.Net and Entity Framework 6.

But holy s**t, two hours later and it was nowhere near working and I was very annoyed. ExecuteSqlCommand takes SqlParameters and the constructor for the output parameter is way more confusing than it needs to be. The input SqlParameter takes a name, a type and a value but the output SqlParameter needs eight more constructor params!!

Fortunately, EF Core still supports old style commands –

            DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

DbCommand still has the ExecuteNonQueryAsync method. Save yourself a major headache and use the cmd.ExecuteNonQueryAsync().

Here is a simple example passing in two parameters and getting one output back.

private async Task ExecuteStoredProc()
{
	DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

	cmd.CommandText = "dbo.sp_DoSomething";
	cmd.CommandType = CommandType.StoredProcedure;

	cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
	cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });

	cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

	if (cmd.Connection.State != ConnectionState.Open)
	{
		cmd.Connection.Open();
	}

	await cmd.ExecuteNonQueryAsync();

	long id = (long)cmd.Parameters["@Id"].Value;
}

2 thoughts on “Entity Framework Core and calling a stored procedure

  1. Thanks for the blog. I was using dbContext.Set().FromSql for EF Core and instead needed a non query result like you have above.

Leave a Reply

Your email address will not be published. Required fields are marked *