Linq over Stored Procedures

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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% 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 no-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 http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

Answer 8

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.