Hi,
Difference between Entity Framework and Stored Procedure is chosen based on your requirement.
Stored Procedure:
Encapsulate multiple statements as single transactions using stored procedure.
Implement business logic using temp tables.
Better error handling by having tables for capturing/logging errors.
Parameter validations / domain validations can be done at database level.
Control query plan by forcing to choose index.
Use sp_getapplock to enforce single execution of procedure at any time.
Entity Framework:
LINQ to SQL and EF queries will be "untunable" because, even if you discover a performance problem, you can't change the underlying API code to produce the exact SQL query that you want. There's too many layers of abstraction to change it.
T-SQL is a declarative language, allowing you the ability to rewrite queries for better performance.
EF 5 simplifies this part a bit with auto-compiled LINQ Queries, but for real high volume stuff, you'll definitely need to test and analyze what fits best for you in the real time world.
Thanks,
Mani