Entity Framework Core and calling a stored procedure

Download full source code here.

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.

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Microsoft.Data.Entity;
using Nito.AsyncEx;

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

Download full source code here.

10 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.

  2. I’m trying to use your example but Visual Studio can’t find either SqlParameter nor SqlDbType. Sure wish there was a comment about which namespace(s) your example is using. 🙂

      • Thanks for uploading your code. Unfortunately your solution is targeting 4.6.1, where I’m targeting .NETCoreApp 1.1. Apparently it doesn’t have access to either System.Data.SqlClient, or Microsoft.Data.Entity. I could be wrong, so going to look into it further.

  3. Thank you for this!!!! I have worked entirely to long today trying to get the ExecuteSqlCommandAsync to work but it wouldn’t accept the parameters. This is working with one slight exception and it may be that I just don’t know what I am doing still. I am trying to return a string with new ids found in the process. I am only receiving a single character of the string getting returned from the database.

    I can’t seem to figure out why. I am using .NET Core 2.0 and have only been at .NET for 3 months. Any information you may have to help would be appreciated.

Leave a Reply

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