Stored procedures vs. LINQ queries

.net entity-framework linq linq-to-sql sql-server


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...

1/14/2011 2:53:02 AM

Accepted Answer

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.

1/14/2011 4:34:01 PM

Expert Answer

False dichotomy. IMO, you can get the sweet spot of both by using raw SQL (which is an important skill, that we should not shy away from) with tools that simply make that convenient. Stored procedures introduce some coupling / deployment issues (timing, etc) that most people don't need; ORMs can be poor performing. Most people aren't going to change RDBMS (and if they do, it will be a major project anyway), so: I'm a big fan of parameterized command text. And tools that help, for example, dapper.

Another user posted a LINQ example of filtering by name; well, here's the same in dapper:

string name = ...
var list = connection.Query<YourType>(
    "select * from TableName where Name=@name",
    new { name }).ToList();

Fully parameterized; havily optimized; safe; quick; clean; easy to get right. Job done.

8/13/2014 2:03:04 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow