In my organization we have to answer to a separate DBA group for decisions like using LinqToSQL . What do you see as some of the best reasons for using L2S rather than stored procedures.
views:
196answers:
7
+2
A:
You would be moving functionality previously in the database into your application, which can be a good or bad thing. I think a strong argument for LinqToSQL is that it can allow you to possibly have faster turn-around times using Linq if you have to submit your request for stored procedures to the DBA group, wait for them to finish, and then work with them to test and iron out any issues. You could at least use Linq for basic data access, and then use stored procedures if needed (they can be helpful for relieving performance bottlenecks).
Jon
2009-07-08 17:33:53
+1
A:
When using L2S, more control is under application developer. So, I would say, you can quickly fix something without involving additional communication to DBA group.
Other than reports which is very data centric, I rarely use stored procedure nowadays.
J.W.
2009-07-08 17:34:36
+6
A:
The Stackoverflow question, Linq to SQL vs Stored Procedures?, has some good points for both sides.
Using stored procedures you get more tight control over your queries and when performance matters this can be a plus. You can also make changes to the procedures without needing to re-compile and re-deploy which can be handy.
However, using LINQ you get type-safety, your DAL is easier to keep versioned and maintained along with your project, it is easier to test and better debugging support.
KingNestor
2009-07-08 17:35:23
Agreed on the type safety thing, that's the largest benefit for me.
bbqchickenrobot
2009-07-08 17:41:04
I would say that LINQ to SQL is decidedly NOT easier to test. Given that it provides you with far more avenues for query construction, you have many more points to test than a simple parameter list for a stored procedure.
Adam Robinson
2009-07-08 17:42:52
+3
A:
I think it boils down to code integration. Rather than having to look at stored procedures, you can simply look at the code in your application.
Take a look at this link... http://www.linqpad.net/WhyLINQBeatsSQL.aspx
I've also found the capability to build dynamic sql like LINQ queries to not appear so dynamic and rather easy to debug. For example, suppose that you have a search page with 10 different criteria/filters. If the user only filters on 2 of them, you can create an extension method to add a where filter on the query if some condition is true. If you had a sproc, you'd have a heap of a mess to debug...
RSolberg
2009-07-08 17:35:31
there are many pro cons each way, but to decide application architecture so you don't have to look at stored procedures, is just crazy!
KM
2009-07-08 18:37:05
@KM - don't think I ever suggested using LINQ to avoid having to write a sproc. I'm a fan of the single responsibility pattern so more often than not, my connectivity to the database and how I get the data is some form of stand alone structure... I do agree that there are pros/cons each way and you need to make a decision on what your DAO structure will be on a case by case basis.
RSolberg
2009-07-08 20:18:54
@RSolberg said "I think it boils down to code integration. Rather than having to look at stored procedures, you can simply look at the code in your application." I read "Rather than having to look at stored procedures" as being the same as "so you don't have to look at stored procedures".
KM
2009-07-08 20:28:07
This article is completely flawed. It seems to imply that LINQ to SQL is an alternative to SQL, when it is in fact NOT. LINQ to SQL simply generates the SQL queries and does the mapping itself. There is abolustely NOTHING faster about LINQ compared to hardcoded queries or stored procedures, it is (at the very, very best) simply not SLOWER or LESS reliable. The only advantage comes in viewing the code, not in query performance or flexibility. The comparison of C# vs C++ is spurious; were it applicable that would mean that C# gets compiled into C++, which is silly.
Adam Robinson
2009-07-08 23:01:03
http://en.wikipedia.org/wiki/Single_responsibility_principle, if the DB tables used in your LINQ code are changed so does you class, and it then does not meet the requirements of the single responsibility pattern
KM
2009-07-09 11:36:43
@KM - Changing the table does not "automagically" change your class (at least in my experience)... Changing tables may force you to change your classes in order for them to deliver expected results, but that is a completely different story.
RSolberg
2009-07-09 15:14:48
@RSolberg, as per the link in my previous comment, "a class or module should have one, and only one, reason to change". If the business rules change, you'll need to change your class. Also, if the table structure changes, you're out of luck and have to write new queries in your class (more than one reason for a change). using a stored procedure, you're more likely to still be able to make the same call to Load_xyz procedure and not even notice the table change.
KM
2009-07-09 18:09:01
+1
A:
It will prevent against SQL Injection as well.
Sprocs do too, but the DBAs will be concerned about this if you are using linq2sql, so letting them know this will help in the convincing of letting you use L2S
L2S will also allow the DBAs to focus on optimization of the Database and its' table structures, indexes, etc and not have to worry about supporting any sprocs within the database, because that responsibility will be shifted to the dev team.
Jon Erickson
2009-07-08 17:37:12
Both procedures and Linq2sql prevent against this. Any paramterized queries will prevent against this.
bbqchickenrobot
2009-07-08 17:40:31
@bbqchickenrobot: you are correct, but the DBAs cannot use the fact that sprocs prevent against SQL injection as a way to promote sprocs > linq2sql (i guess this is what I was really trying to say)
Jon Erickson
2009-07-08 17:45:36
A:
Check this out, sort of been discussed here:
http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures
Also, we like to use linq for reading of data and stored procedures for the updating of data as there is some business logic in our sprocs (unfortunately). I don't agree with biz logic in sprocs however with the exception of the most rudimentary. There can be arguments for both sides and those would be relevant to your environment.
bbqchickenrobot
2009-07-08 17:38:39