C# Tips

C# Tip Article

EF Core 3 Stored Procedure Example

Here is a simple example that shows how to use stored procedure in Entity Framework Core 3.x.

// using Microsoft.Data.SqlClient;

var paramList = new[] {
	new Microsoft.Data.SqlClient.SqlParameter("@year", 2020),
	new Microsoft.Data.SqlClient.SqlParameter()
		ParameterName = "@total",
		SqlDbType = System.Data.SqlDbType.Int,
		Direction = System.Data.ParameterDirection.Output

MyDbContext db = new MyDbContext();
int n = db.Database.ExecuteSqlRaw("EXEC sp_GetTotal @year, @total out", paramList);
int total = Convert.ToInt32(paramList[1].Value);  // @total output value

The stored procedure above (sp_GetTotal) is taking one input parameter (@year) and one output parameter (@total). In order to provide parameter, I made array of SqlParameter instances but they can be passed individually as 2nd, 3rd parmater of ExecuteSqlRaw() method.

To call stored procedure, use Database.ExecuteSqlRaw(ExecuteSqlRawAsync) or Database.ExecuteSqlInterpolated(ExecuteSqlInterpolatedAsync).
Please note that Database.ExecuteSqlCommand() used in EF Core 2 is deprecated.

One important thing is that SqlParameter class is under Microsoft.Data.SqlClient namespace, NOT in System.Data.SqlClient namespace. If you use System.Data.SqlClient.SqlParameter class, you will get the following error (somewhat confusing!). To emphasize this, I used the "new Microsoft.Data.SqlClient.SqlParameter(...)" statement in the example above.

"The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects."

Related articles: