C# Tip Article

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

• EF Core 3 - Use appsettings.json instead of hardcoded connection string
• EF Code First: How to insert master/details data
• Entity Framework 6 SQL Server Connection String