I m using ASP.NET Core 1.0 and website will be big (expecting about 5,000 visitors per hour). I have read many times that stored procedures are very fast and safe (in terms of SQL injection etc). But the code first EF if so easy that I want to use it, but the CRUD methods are hidden.
What will be the disadvantages (if any) to use Scaffolding with EF over SP's in terms of performance and security?
As long as you are using parameterized SQL you should be okay in terms of SQL injection. Never use user input to directly "build" SQL queries via string concatenation. So, as long as you use Entity Framework, stored procedures, or other tools correctly you shouldn't have to worry about SQL injection.
In terms of performance Entity Framework and other similar tools do perform worse overall. I'm not sure that alone is enough to keep you from using it though unless your program expects to have very heavy usage.
Stack Exchange has a nice open source tool Dapper is more lightweight than Entity but still has some nice features. It allows you to write raw SQL. See the section on performance in the Dapper readme. It performs very well, much better than other frameworks.
Staying away from stored procedures can help improve your architecture. Stored procedures may encourage you to encode lots of business logic in the database where unit and integration testing is more difficult to do. Also, deploying new apps becomes more difficult due to having to keep your application, stored procedures, and schema in sync.
So, in short Entity Framework is a great tool but can decrease performance. There are alternatives to stored procedures that are still high performing. Security shouldn't be an issue with whatever mature tool you use (correctly).
Edit to answer additional questions
Isn't Dapper susceptible to SQL injection?
Sure, but just about any tool is if used incorrectly. Here is the proper way to use Dapper example from their documentation. This query is parameterized.
connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
)
Now, here is a bad example that is susceptible to SQL injection:
connection.Execute(@"insert MyTable(colA, colB) values ('" + a + "', '" + b + "')")
Is Entity Framework always safe?
No, if you concatenate variables like the previous section and use ExecuteQuery you run into issues as well.
Are stored procedures always safe?
No, you can still run into SQL injection issues if you use dynamic SQL without parameterization.
Here is a link that discusses how ORMs and stored procedures are susceptible to SQL injection: http://www.troyhunt.com/2012/12/stored-procedures-and-orms-wont-save.html
What will be the disadvantages (if any) to use Scaffolding with EF over SP's in terms of performance and security?
No security issues if used properly like discussed above. Scaffolding is great if you're doing CRUD pages.
If you need to do advanced reporting you may need custom SQL which can still be done with Entity Framework. There is nothing wrong with using scaffolding for as much of your app as it makes sense to use it for and then use parameterized SQL via Entity for everything else. You can always even use scaffolding and then modify the generated classes to do what you want. For CRUD pages use scaffolding and for advanced queries write your own.
The performance hit from using Entity Framework for simple queries probably won't hurt you for basic queries - it should be minimal. You need to worry more about proper indexing and database schema most likely. A bad schema or incorrect indexing will cause performance issues in a hurry.
I like to think of choice between writing stored procedures and writing queries in EF as similar (in some way) to difference between programming in assembler and writing programs that are being runned in managed environment (like .NET and Java). Applications written for managed environments are always slower than those written in assembly language, but on the other hand it is usually much easier (and thus cheaper) to write complex applications using managed languages. With use of EF linq you will write your queries much faster and they will be usually simpler than their sql equivalents and thus easier to maintain. Other important factor is that applications changes rapidly, new functionalities are being added, old are being rewritten and along with this process tables and other db structures changes. When this happen your SP can no longer work correctly. When do you know that they should be fixed? Only when you run them. When do you know that you need to fix EF query because table changes? During compilation. And performance lost? Generally nowadays we have fast and quite cheap machines so we do not care that much. I examined once execution time of EF query and its sql equivalent - plain sql gained me decrease in execution time like 5% - this is nothing for me. And as sql queries are much more complex there is high probability of writing your query in wrong way so that it will be unnecessary slower. And finally, if you need in your app really great performance for some complex query - write it as SP and execute it easily with EF.
When it comes to security EF query generators always use sql parameters so they are as safe to sql injections as SP.
You can use scaffolding to create DB and views and replace the calls to inbuilt methods by your own methods.
