In my experience, both of these are usually substitutes for skill in the subject.

That can be a good thing, if the company you work for can't hire expertise (not available, not affordable, whatever), and needs something that will probably at least work, even if not well.

However, it can leave you in a situation where, when you finally can/must obtain expertise (either through hiring an expert or through training existing employees), that the expertise is pointless because of the limitations that are now imposed on things like performance tuning. This is true of any data solution that doesn't include all data access through a DAL the tuning expert (usually a DBA) can code in, whether that DAL is procs or something higher in the stack than those.

LINQ to stored procedures, with no direct table/view/UDF access, on the other hand, I've had more positive experience with. But it seems to be a rare solution. It does require having proc-writing expertise available through one means or another, so may not be available to everyone.

Since database tuning expertise is usually a DBA skillset, and since DBAs tend usually towards writing SQL in stored procedures, which can act as an API to the data (a Data Access Layer), I've found that to be a "best option" in cases where it can be done.