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?
>Solution :
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>.
Further Reading:
Raw SQL Queries (EF6)
Database.SqlQuery Method