Linq over stored procedures

Question

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

Answer 1

LINQ is a great addition to the .NET Framework, but it has limitations. At the moment, LINQ does not yet support all of the SQL syntax (although I'm sure they are working on it). In addition, LINQ does not have a clean way for it to process multiple tables and provide us with only the data we need. With LINQ, you will need to extract all the data and then save what you want and discard the rest, thus transferring more data than is really necessary, which is a performance issue.

If all you do is just the INSERT, UPDATE and DELETE LINQ instructions - this is the way (in my opinion) and all the optimization is done for you, for more complex work, I would say to stick to stored procedures.

Answer 2

Since no one added CON - I will suggest here ...

Stored Procs offer you the ability to refuse to select / insert / delete / update in base tables and views so that you can potentially improve security using stored procedures like you could use Linq to SQL. By granting execution permissions only for your processes, you have less space for managing security.

Of course, you can still use Linq for SQL and store procs together - perhaps this would be the best option.

Answer 3

From my point of view, the main value of stored procedures was eliminated using LINQToSQL. For a long time, the key to using stored procedures was to encapsulate SQL in a form in which you naturally used parameterized queries. This provided the developer with a level of security for SQL injection. Since LINQToSQL queries are parameterized by default, I found that my use of stored procedures was significantly reduced.

This does not mean that there is still no place for them, but now I feel that you should only use them when they provide significant value for the LINQ parameterized query in terms of less complexity or increased performance, possibly due to the server’s ability to optimize procedure. I feel that eliminating the dependency on stored procedures leads to a more convenient code base, since most of the code is in your IDE and not shared between your database and your IDE.

Note that you can still use stored procedures with LINQToSQL. I just don't really appreciate that. In fact, I cannot imagine a single stored procedure that I wrote since I switched to LINQToSQL, although I wrote several table functions to perform certain searches. They get into my DataContext and are displayed as methods that I can call to get the corresponding objects from the database using this function.

Answer 4

There are a few things I would like to add to the discussion:

• Linq is less likely to get into the SQL Server execution cache than a stored procedure. Although compiling basic select statements is easy, in more complex queries, compiling can be quite expensive. (With many factors, you need to make this statement true / false for your situation, but this is probably for another post).

• If you have a twist in recompiling data, it may actually be helpful to prevent the use of odd SQL usage queries. Think of select * FROM Person where LastName = @Letter First pass, where @Letter='Z' (say 0.25% of the total number of people) compared to @Letter='S' (6.00% of the total number of people ) may lead to completely different execution plans.

• Linq effectively re-injects ad hoc sql into our code. Provided it through the abstraction layer and in the new language, but I no longer call exec GetOrders @ DateStart=@now @DayRange=7 , instead I write my table name, where clause and order by.

• Tracking down broken SQL queries back to the code generated in statements is more complicated than with SP. In an environment with large volumes, the SQL profiler is often run on a regular basis to find non-executable queries (high processor, read, or duration). As Linq abstracts the text that it creates, it becomes more difficult to track sql back to a specific place in the code, especially in large applications.

• As a side note, when necessary, Linq can invoke a specific stored procedure, rather than generate its own SQL. See Scott Gun’s article .

Answer 5

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

One of the main reasons for using ORM is to increase development speed. Whenever you have a component that will create queries for you, this is another thing you should write to yourself.

Answer 6

I talked about this with someone here the other day, because we are currently using stored procedures for all access to the database. We discussed LINQ in general and the implementation of LINQ to SQL, 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 happens in the database is very clear. In addition, development time may be shorter, mainly depending on the process, as there is one less work product.

The benefits of Sprocs, as I see it, are also twofold. Stored procedures can significantly improve access control for the database administrator, since they can check sproc before deployment and allow the application to use access only to run this sproc, and not read / write for all necessary tables. This allows a much better analysis of the problems of competition and database performance. Another advantage that I see is that although LINQ to SQL generates the correct query, in the case of complex queries there are times when you get into a case that leads to poor optimization on the database side. In these cases, you either rewrite the query or provide hints to the optimizer, both of which are difficult / impossible / metaphors with LINQ.

Maybe this is a DBA in me (not a DBA, but there were), but I am very nervous when working with a large high load on a transactional database and I don’t know exactly all the possible operators that will be executed by the system. Therefore, I myself adhere to sprocs.

Answer 7

I don’t understand why every discussion of this topic seems to imply the fact that writing SQL is difficult, but writing linq queries is not. Although it’s true that simple linq is, well, simple, as soon as you want to write a query that accesses many tables with complex outer joins, it just becomes incredibly dirty. Not only that, but I have no idea how the main engine will display my request, and if it does not work, it is very difficult to debug it.

I find it 100 times easier to quickly write the complex part of SQL (if I grew up with this, but certainly not the only one) than doing the same thing in linq.

If I need to configure it and save it in a stored procedure, then I just pick it up and release a new saved proc. I do not need to do a complete assembly of the application, because the code is embedded in it.

If it does not work well, I can work with the request until it does.

However, every linq presentation says you no longer need to learn SQL. Yet SQL is such a well-understood, mature language. Hell, I would prefer that I could put SQL directly in my C # code instead of learning the new syntax.

Even the theory of access to the cross-domain database “if I write it in linq, I can use any database I want,” is not interesting to me, especially if I write, say, SQL Azure, where I know exactly which database will be.

So, my attendant (who’s been swelling for some time !!) on this issue. I would go to storage procs. If you want it safe, then load the results into well-defined business objects or linq into sql objects, if you prefer.

Answer 8

I use LINQ-to-SQL extensively in my projects and found that it works, as well as SP in almost all cases.

There are some cases where you lose performance / capabilities with LINQ-to-SQL, no doubt:

• Since queries are authenticated in the code using LINQ, you cannot use the SQL built into the query optimizer / index optimizer optimization tool (as easy). Things like tracking execution plans also take an extra step (getting the generated sql). Pretty trivial, I think.

• If you have a low bandwidth situation, sending extra text with a parameterized query over the cable will have more bandwidth than just sending a stored procedure call. Therefore, if you are writing a Windows application that communicates via a dial-up connection, it can be more dangerous than a web application (where the servers are next to each other) or if you are in a very high usage situation.

Answer 9

How about modularity?

One thing that I find in LINQ-to-SQL is that if there is an error, we may need to recompile and redistribute the entire application (or just the affected DLLs), but with stored procedures, I just need to fix one stored procedure and my work is done.

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

Again, I like LINQ and the functions that came with this ... but I'm not quite selling at LINQ-TO-SQL.