Perhaps it makes sense, to turn the question around and find usecases where only stored procedures could do, what you want to achieve. Perhaps there are really use cases, where stored procedures stand out.
If it makes a difference, I am using MSSQL and Entity Framework.
My knowledge on EF is limited, but as far as I can see, EF is (just) an ORM like any other; and fortunately is capable of using raw SQL.
If I take your two major points:
A complicated report that was taking minutes to run (this was a page in a web app). I found I could write SQL that was much more efficient (only taking seconds to run) than what LINQ was providing.
LINQ / EF was falling short, when doing a report. And as you noticed, was
SQL way faster, than using an ORM. But speaks this in favour of stored procedures or only against using the ORM for everything?
Your problem could obviously solved with SQL. If that query was stored and version controlled in your codebase makes - according to your example - at least no difference.
A web application needed to read and write to a few tables on a separate database which contained a lot of other, sensitive information that was irrelevant to the application. Rather than giving it access to everything, I used a stored procedure that only does what is needed, and only returns limited information. The web application could then be given access to this stored procedure only, without access to any tables etc.
Same thing here: a simple connection string and and UPDATE and your problem is done. This problem could be solved even with an ORM:
simply use a webservice in front of the other DB and the same compartementalization / isolation is achieved.
So nothing to see here.
Looking at some points others have made:
You have complex units of work, perhaps involving many tables, that cannot be wrapped in a transaction easily using the features of EF.
SQL can do that. No magic involved here.
Your database does not play well with EF
Again: Use EF when appropriate.
You need to work with data that crosses server boundaries with linked servers
I do not see, how stored procedures help. So I can not see an advantage of stored procedures; but perhaps someone sheds some light on that.
You have very complex data retrieval scenarios where "bare metal" SQL is needed in order to ensure adequate performance
Again: "Boundaries of EF".
Your application does not have full CRUD permissions on a table but your application can be allowed to run under a security context that your server trusts
Okay. I go with a maybe.
So far only a half point was made in favor of stored procedures.
Perhaps there are performance considerations, which speak in favour of stored procedures.
1) Storing queries has the benefit of a simple call to the stored procedure which encapsulates the complexity. Since the query planer knows the query, it is "easier" to optimize. But the saves are with the current sophisticated query planer _minimal.
On top of that, even if there is a slight cost using ad hoc queries, if your data is well structured and carefully indexed, the database is merely the bottleneck of your application. So even if there is a small delta, it is neglectable considering other factors.
2) Nevertheless it was argued for storing complex queries in a DB.
There are two things to consider:
a) complex queries make massive use of the DB infrastructure, which increases asnwering times for every other query. You can not run many costly queries in parallel. This speaks neither pro nor contra stored procedures, but against complex queries.
b) If the query takes time anyways, why bother with small speed wins of a stored procedure.
Nothing speaks directly against stored procedures. So using stored procedures is okay - if it makes you happy.
But on the other hand: I couldn't imagine a proper use case which speaks unequivocally pro.
When should I use stored procedures?
The proper answer is: Whenever you like. But there are other options.