ASP.Net Core Web API with Entity Framework is there any benefit to using stored procedures?

Question

I have an SQL Server database that I am accessing with an ASP.Net Core Web API using Entity Framework. Here are 2 examples of the same query with 1 using the default query method and the second using a stored procedure.

Default Web API Query

     // GET: api/Players
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Player>>> GetPlayers()
        {
            return await _context.Players.ToListAsync();
        }

Using Stored Procedure

  // GET: api/Players
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Player>>> GetPlayersProc()
        {
            string storedProc = "exec GetAllPlayers";
            return await _context.Players.FromSqlRaw(storedProc).ToListAsync();
        }

Both of these return the exact same list of players.

My question is what are the benefits to using 1 approach over the other? Is using a stored procedure faster or more secure in any way? What are the pros and cons to both methods?

Answer 1

EF provides strongly typed access to the db, so you get compile time checking of each of your queries. This comes at a minor cost to performance.

When you use stored procs there is a disconnect between the EF structures and the query, if your stored procs are incorrect, then you may not know until runtime. But yes you can increase performance with SPs, it comes at a cost to maintenance effort.

Generally SPs are reserved in EF projects for scenarios where the SPs were pre-existing or you have some need of Uber performance or are doing something complicated that the SQL engine has optimised support for but is hard to express in C#.

SPs are also used a lot where a separate team of DBAs manage the database and access to it and your application code needs to conform to their control.

SPs are often an unnecessary abstraction layer in EF projects, especially if you are using the code-first paradigm and are managing the schema in your solution as well.

Don't try to pre-optimise your application until you can justify it. Save SPs for special occasions (think of SP as Special Procedures) or for optimisations once your solution or parts of it has matured and you have a well established test or QC regime.