LINQ queries vs Stored procedures [closed]

Question

What are the pros and cons of using linq queries(along with an ORM like EF or linq2sql) VS. Stored Procedures(SQL server 2008) to query and update a data model? Performance? Speed? Etc...

Answer 1

Linq is definitely more readable when you're in the code. Seeing a call to execute a sproc called "sp_GetSomething" doesn't tell you anything as a developer, unless you go and physically look at what the sproc does. seeing code like

var query = from c in db.TableName
            where c.Name == "foo"
            select c;

That tells you exactly what data is being pulled.

Stored procedures on the other hand do not require you to recompile the application if you decide to change the code. If you decide to suddenly change a "where" clause or change the Order By - changing a sproc is easy. Changing the Linq code could be more time consuming.

I'm sure there are plenty more, but these are two I've noticed.

Answer 2

There are 2 camps: for stored procs and against stored procs.

I've found that it is lack of experience that make people go one way or another. There are different kinds of shops where we develop.

In practice

• if you're a corporate programmer, then you'll never change your RDBMS platform. You refactor your client every now and and you'll reimplement your DAL/repository. Why? Use stored procs.
• if you work for a vendor, then you will probably have to support several RDBMS. An ORM abstracts this away mostly.

I'm in a corporate shop so...

• Pros: with Linq you don't have to know SQL
• Cons: you're screwed when things go wrong

We (as a developer DBA team) frequently have to bail out ORM users in sister teams.

There are also more subtle issues such that:

• stored procedures can be used by any client
• will outlast your refactor into EF or whatever .net 5 brings
• encapsulation offered by stored procedures to abstract schema away
• reduced round trips because shouldn't stored procs be treated like methods, or atomic calls?