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)

	await cmd.ExecuteNonQueryAsync();

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

Download full source code here.

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

Leave a Reply

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