Stored Procedures in Entity Framework Vs ADO.NET

Question

For one of my project, I am using Entity Framework to connect to a SQL Server database. Due to large volume of data like (1000 - 100000) insert/update/delete, I had to use stored procedures with user-defined table types as parameter to improve the performance.

Also, these insert/update/delete operations involve data from multiple other tables as dependent. So, I used stored procedure with proper temp table and CTE with join queries (it is quite a complex JOIN) to achieve the required result.

Achieving the same in Entity Framework (repository patterns) is creating performance issues. We can call stored procedures from Entity Framework also

Now, my question is: which is better in my above scenario?

1. Calling stored procedure from Entity Framework (if so, please suggest some articles to achieve it)
2. Calling stored procedure from ADO.Net

Thanks in advance.

Udhay

Answer 1

I would avoid implementing the repository pattern with Entity Framework/Entity Framework Core as it already implements a repository pattern. Specifically, the DbContext is your UoW (Unit of Work), and each DbSet is the repository. Applying another layer on top of this is redundant and will make maintenance harder.

Regarding your specific questions, you should look into using Dapper vs. Entity Framework if all you are doing is calling stored procedures as there is less overhead and possibly better performance depending on the underly queries. It will also handle mapping the results into a strongly typed class.

code snippet

using (var connection = new SqlConnection("your connection string"))
{
    await connection.OpenAsync();

    var results = await connection.QueryAsync<SomeClass>("SomeStoredProc",
                  new {Param1 = value}, 
                  commandType: CommandType.StoredProcedure).ToList();

    // ... more code
 }