Good day all,

I have inherited multiple desktop applications which have a heavy reliance upon stored procedures for data operations. These apps were written in VB6, and I am currently attempting to figure out how to port them to .Net 5 or .Net 6.

I understand that Entity Framework Core is capable of executing stored procedures. However, in a video I watched recently, I came to understand that Entity Framework Core executes data functions via the stored procedure sp_executesql, to allow for execution of whatever procedure a developer may have dynamically generated.

However, as the video linked above mentions, this presents a security hole for desktop applications. To fire sp_executesql, users of desktop applications must have credentials which can fire it upon their system. These values may be encrypted, but encryption is not invulnerable.

If I create and utilize database credentials that do not have access to sp_executesql, but do have access to the stored procedures my colleagues have made, will Entity Framework be capable of firing the latter?

CodePudding user response：

You can use a "Raw SQL Query" to execute your stored procedures directly, without requiring sp_executesql:

var customers = context.Customers.SqlQuery("dbo.sp_getcustomers");

or:

var customers = context.Customers.SqlQuery("dbo.sp_getcustomerbyid @p1", customerID);

SqlQuery returns a lazy-loading IEnumerable<T>.

As an aside, raw SQL queries are incredibly useful for more than just calling stored procedures. Instead of relying solely on Entity Framework's SQL generation mechanism (which can sometimes produce sub-optimal SQL), you can just execute an arbitrary, well-formed SQL statement of your own choosing, directly.

Further Reading:
Raw SQL Queries (EF6)
Database.SqlQuery Method

CodePudding user response：

If I create and utilize database credentials that do not have access to sp_executesql, but do have access to the stored procedures my colleagues have made, will Entity Framework be capable of firing the latter?

The premise of this question is doubly flawed.

1. sp_executesql is available to all users and does not present any kind of security hole. Your users will have access to sp_executesql.

2. EF isn't acutally calling sp_executesql. It's invoking the stored procedure as an RPC instead of a TSQL batch. This is built-in TDS protocol functionality. But profiler displays the RPC call as a TSQL batch using sp_executesql so that you can copy and paste the call into a query window for testing.