Entity SQL or StoredProcedure, what to use?

I have got choice for using either Entity SQL query inside my DAL or use Stored Procedure at db level as a option.

Which one should I use. And what are the benefits/drawbacks for either approach?

I am more inclined towards Entity SQL as I don't want my any form of db level 开发者_StackOverflowexposure of my logic.

---

Based on my experience:

Entity Framework:

• Logic stays in the application
• Easier to integrate with source control (in my experience)
• Can also use stored procedures if needed (I used it in a full text search routine)
• Database agnostic -> well, I haven't really tried changing db's before, but its supposed to shield you from the underlying persistence store
• Personal Preference: its so much prettier and convenient to get an array of objects/models instead of a bulky datable.. not to mention that I need to know the order of the columns and such..

Database SPs

• You don't have to learn anything new - There are certain tasks that are trivial from a storedproc but can be so annoyingly obscure when using EF. Well, it depends on how quick you catch up.
• Easy to use, more granular control - up til now, I don't know how to create a query that uses WITH and OVER() in linq to entities..

Personally, I'd go with EF too for the reasons I've mentioned above (plus a few others I cant think of right now). Besides, if there's something I can't do or cant do fast with a linq query, I'll just create a stored proc or just execute an SQL statement (yes you can execute SQL using the context).

---

If you are the DB administrator as well, then use LINQ Entities. It's easier if you keep your code together.

However, if you know your way around SQL (or someone else is in charge of SQL Server Performance), then use Stored Procedures. It's much easier to optimize your DB operations without re-releasing your application.

---

Both are having its own pros and cons.

Entity SQL is secure from SQL injection attacks as its always parameterized.
But if you know SQL well enough, you would see cases where a Stored Procedure would be more prudent.

Do you know that you can use Stored Procedures in Entity Framework?
Would prefer suggesting Entity Framework. Use Stored Procedures when you feel in a particular scenario Stored Procedures are more optimized.

---

I use StoredProc only if:

1. For get some data you should made more than 2 requests to database - why to sent it from script, it's easer to write a proc that will done all action in db and only sent to you the resalts (its too much faster)
2. If simplier to write a proc and spend 5 mins than a genious entities and spend 1 day
3. If some operations by defaults runnig faster in db or not supported by entities