Linq over Stored Procedures

Question

What are some pros and cons of using linq over stored procedures?

score 10 · Answer 1 · answered Apr 07 '09 at 18:15

10

Since nobody's added a CON - I'll suggest one here...

Stored Procs offer you the ability to deny select/insert/delete/update on your base tables and views, so you could have potentially better security using stored procs as you could using Linq to SQL. By granting exec permissions only on your procs, you have a smaller surface area to manage for security.

Of course - you can still use Linq to SQL and stored procs together - maybe that would be the better option.

answered Apr 07 '09 at 18:15

Scott Ivey

40,768
21
80
118

And what I said earlier, but mine was wordy. :) – Darren Clark Apr 07 '09 at 19:20
This may be more a PRO for stored procs than really a CON for LINQ. it's yet another layer to control authorization to resources. But this be done at other layers too, e.g. the application, and we don't say it's a *disadvantage* of stored procs is that they don't access e.g. the ASP.NET security API, although C# does. – T. Webster Jan 05 '12 at 08:11

score 5 · Accepted Answer · answered Apr 07 '09 at 18:21

5

LINQ is a wonderful addition to the .Net Framework, it does, however, have it's limitations. As of yet LINQ does not yet support all of the SQL Syntax (though I'm sure they're working on it). Also, with LINQ there is no clean way of having it process multiple tables and give us only the data we need. With LINQ you would have to retrieve all the data, then keep what you want and throw the rest out, thus transmitting more data than is actually needed, which is a performance issue.

If all you're doing is simple INSERT, UPDATE, and DELETE statements LINQ is the way to go (in my opinion) and all the optimization is done for you, for more complex work I would say to stick with stored procedures.

answered Apr 07 '09 at 18:21

PsychoCoder

10,570
12
44
60

2

Actually, since query execution is deferred, as long as you don't do something like ToArray in the middle, it will process the joins properly and let the DB do what it does best. If you're talking about row width, you can always use anonymous objects and it'll make the right select. – Darren Clark Apr 07 '09 at 18:41
Yeah, agreed - you can certainly restrict the data that is pulled back to a very detailed level via LINQ. – Sam Schutte Aug 10 '09 at 19:43
erm... LINQ is for Querying, not for inserts, deletes or anything else – BlackTigerX Sep 28 '11 at 19:20
1

and LINQ is not a silver bullet, the great thing is that it unifies the querying language, regardless of the providers, the syntax is the same for querying in memory objects, xml or a database – BlackTigerX Sep 28 '11 at 19:20

score 4 · Answer 3 · answered Apr 07 '09 at 18:10

From my perspective, the primary value of stored procedures has been eliminated with LINQToSQL. For a long time, the key value of using stored procedures was to encapsulate the SQL in a form where you would naturally use parameterized queries. This provided a layer of security to the developer with respect to SQL injection. Since LINQToSQL queries are, by default, parameterized I find that my use of stored procedures has been reduced dramatically.

That's not to say that there isn't still a place for them, but now I feel that you should only use them when they provide significant value over a parameterized LINQ query in terms of less complexity or increased performance, perhaps because of the server's ability to optimize for the procedure. Removing over dependence on stored procedures, I feel, results in a more maintainable code base as most of the code is located in your IDE instead of being split between your database and your IDE.

Note that you can still use stored procedures with LINQToSQL. I just don't find much value in doing so. Actually, I can't think of a single stored procedure that I've written since switching to LINQToSQL, though I have written a few table-valued functions to perform specific searches. These get dropped onto my DataContext and appear as methods that I can invoke to get the appropriate entities from the DB using the function.

LINQ to SQL is still far less secure becasue you must grant access at the table level which you do not have to do with properly written procs. You might be protecting OK from outside but you are allowing the potential for fraud in your system by insiders. — HLGEM, Apr 07 '09 at 21:58
The difference would be that I control access at the application level via roles, not at the database level. No one has access using their own credentials. All access is filtered through a permissions layer on top of the LINQToSQL data context. — tvanfosson, Apr 08 '09 at 01:07

EBarr · Answer 4 · 2012-08-05T15:59:56.993

There are a few things I wanted to add to the discussion:

Linq is less likely to hit a cached execution plan in SQL Server than a stored procedure. While compilation of basic select statements is light, in more complicated queries compilation can be quite expensive. (At lot of factors go into making this statement true/false for your situation, but that's probably for a different post).
If you have skewed data recompilation may actually be a benefit prevent odd index use decisions by SQL. Think select * FROM Person where LastName = @Letter First pass where @Letter='Z' (lets say 0.25% of total people) versus @Letter='S' (6.00% of total people) can result in wildly different execution plans.
Linq is effectively reintroducing ad hoc sql into our code. Granted its through a layer of abstraction and in a new language, but no longer am I calling exec GetOrders @DateStart=@now @DayRange=7 instead I'm writing out my table name, where clause, and order by.
Tracking non-performant SQL statements back to the code that generated the statements is more diffcult than with SP's. In a high volume environment SQL profiler is often run on a regular basis to find non-performant queries (high CPU, reads, or duration). Since Linq abstracts the text it generates it becomes tough to trace the sql back to a specific location in the code, particularly in larger applications.
As a side note, when necessary Linq can call a specific stored procedure rather than generating it's own SQL. See this Scott Gu article.

As to "Linq is less likely to hit a cached execution plan": i have found that queries generated by LINQ are more likely to generate several plans per query - and while this requires a bit of time to "cache up" a fresh server, the plans which are generated are more specialized thereby often enhancing performance. — NTDLS, Aug 03 '12 at 14:26
@NTDLS - I agree for the most part. Linq query generation has evolved since I wrote this to better align with your perspective. More (+ more specific) plans *can* enhance performance--they *can also* degrade performance. In a large/variable enough system too many plans can result in cache scavenging to free up resources resulting in fewer cached query plan hits. Also, implicit in my statement was that in SQL 2008 I can pin the 'proper' SP execution plan, and avoid the pitfalls skewed data & parameter sniffing can create. I guess that's why i put a disclaimer in that point. — EBarr, Aug 03 '12 at 14:45

score 2 · Answer 5 · answered Apr 07 '09 at 18:04

I assume you mean LINQ to SQL as LINQ and stored procedures are two very different things.

One of the main reasons to use any ORM is to increase the speed of developement. Whenever you have a component that will create queries for you that is one less thing you have to write yourself.

score 1 · Answer 6 · answered Oct 19 '10 at 09:16

I dont understand why every discussion on this topic seems to assume the fact that writing SQL is hard, and writing linq queries is not. While its true that simple linq is, well, simple, as soon as you want to write a query that accesses lots of tables, with complex outer joins, it just becomes incredibly messy. Not only that but I have no idea how the underlying engine is going to render my query, and if it doesnt work, its very difficult to debug.

I find it 100 times easier to quickly write a complex piece of SQL (granted I grew up with this, but surely I'm not the only one), than to do the same thing in linq.

If I need to tweak it, and its in a stored proc, then I just tweak it and release a new stored proc. I dont have to do a full build of the app because the code is embedded within it.

If its not performing well, I can work on the query until it does.

Yet every presentation on linq says you no longer have to learn SQL. And yet SQL is such a well understood, mature language. Hell, I would have rather that I could put SQL directly into my C# code instead of having to learn a new syntax.

Even the theory of cross-database access "if I write it in linq I can use whatever database I want" is of no interest to me, particularly if I'm writing, say SQL Azure where I know exactly what database its going to be.

So thats my rant (thats been bottling up for a while now!!) on the subject. I'd go stored procs. If you want it type safe then load the results into well defined business objects, or linq to sql entities if you prefer.

score 1 · Answer 7 · answered Apr 07 '09 at 18:39

I was talking about this with someone here the other day, as we use stored procedures for all database access currently. We were discussing LINQ in general, and the LINQ to SQL implementation, IQueryable etc. She quickly realized that using LINQ with sprocs would be redundant at best and difficult at worst.

The advantages of LINQ to SQL are that the code lives in one place, and what is occurring in the DB is very clear. In addition the development time can be less, depending mostly on process, as there is one less work product to make.

The advantages of Sprocs, as I see it, are also twofold. Stored procedures allow for much better access control for a DBA, as they can inspect the sproc before deployment, and allow the application use access only to execute that sproc rather than read/write access to all the tables required. This makes for much better reviews of database contention and performance issues. The other advantage I see is that while LINQ to SQL will generate a correct query, in the case of complex queries there are times where you hit a case that causes poor optimization on the DB end. In those cases you would either rewrite the query, or provide hints to the optimizer, both are difficult/impossible/metaphor breaking with LINQ.

Maybe it's the DBA in me(not a DBA, but have been), but I feel really nervous when working on a large high transaction load DB and not knowing exactly every possible statement that would be executed by a system. So I'm sticking with sprocs myself.

score 0 · Answer 8 · answered Aug 10 '09 at 19:51

I use LINQ-to-SQL extensively in my projects, and have found it to perform as well as SP's in almost all cases.

There are some cases in which you lose performance/ability by using LINQ-to-SQL, without a doubt:

Since queries are wrapped up in code by using LINQ, you can't use SQL's built in query optimizer/index optimizer tool (as easily). Things like tracing the execution plans take an extra step as well (getting the sql that is generated). Pretty trivial I guess.
If you have a really low-bandwidth situation, sending the extra text of the paramaterized query across the wire will eat up more bandwidth than just sending the stored procedure call. So if you're writing a windows app that communicates over a modem connection, this could be more of a concern than a web app (where the servers are sitting next to each other), or if you're in a really high usage situation.

score 0 · Answer 9 · edited Dec 24 '16 at 10:09

What about modularity?

One thing that I find with LINQ-to-SQL is that if there is a mistake, we might need to recompile and redeploy the entire application (or just affected DLLs), but with stored procedures, I just need to fix the one stored procedure and my job is done.

It seems to me that LINQ feels more adhoc and stored procedure feels more formal.

Again, I do like LINQ and features that came with that ... but am not quite sold on LINQ-TO-SQL.

Linq over Stored Procedures

9 Answers9

Linked

Related