Are SPs redundant if using Linq and EF (best practice)

Question

I'm thinking about moving my development team to LINQ and the Entity framework. If I do, should I be thinking about removing SPs?

Typically our architecture is (in order);

SQL -> SPs -> Data Access Layer -> Business Objects -> GUI

Should I be moving to something similar to:

SQL -> Entity Framework Layer -> Business Objects (probably inherited from EF layer) -> GUI

Will I see as much benefit if I leave SPs in? Also what are the best practices?

Answer 1

No, they are not redundant.

We use Entity Framework for 90% of our data access code.

That 10% is used in the following scenarios:

• When the code is weighty in logic (overly complicated)
• When performance is essential
• When multiple records need to be affected in single transaction

LINQ-Entities (and LINQ, in general) is a brilliant and consistent framework, but there are times that the SQL translated is not as optimal as you expect - extra JOIN's, CASE's, etc. Sometimes in these scenarios it's wise to skip the expression tree translation and go straight to native SQL.

What's more, Entity Framework promotes stored procedures. You can map them on your model, and you can even map procedures directly to CRUD operations on your entities.

So in general, use EF for most simple operations (e.g CRUD), and use stored procedures when performance and complexity are factors.

HTH.

Answer 2

Yes, you should.

Maintaining stored procedures will create huge amounts of friction when you're using an ORM like Entity Framework. EF generates valid, performant SQL, and avoids things like SQL injection attacks.

There may be occasional scenarios where you need to run unusual queries that require complex joins or multiple tables - it's easy in these scenarios to expose a special method on your repository that invokes a stored procedure - but for general-purpose CRUD data access, just let your ORM generate the SQL for you at runtime and stop worrying about it.

(We used to do everything via stored procedures. We switched to NHibernate last year, haven't written a sproc since, and the sky hasn't fallen in or anything...)

Answer 3

Just try to move to the EF partially (CRUD operation, etc), but some part of your code leave using SPs. Second case can be applied to the heavy-weight queries, transactions, and when you want to use code defined by you. Generally EF provides development speed, but some specific things conveniently develop using SPs. Also SPs give always give you a performance boost.