If all access to the SQL Server database is through stored procedures

Question

If all access to the SQL Server database is through stored procedures, and I plan to continue this practice, use linq2sql and / or an entity structure for future projects - an unnecessary level of complexity that does not bring much significance?

A related question: is Microsoft trying to distract developers from using stored procedures to access data?

Answer 1

No. LINQ2SQL adds great value in terms of being able to easily map database objects to classes in your code and easily work with these classes using native langugage constructs. You can easily map the CRUD operations of the generated entity class to your stored procedures if you want. I found that some things no longer require stored procedures to work easily, and so I have refused to use them, but you are not forced to. Essentially, what LINQ2SQL can do is replace a lot, if not all, of your DAL, which saves you from writing this code.

Answer 2

I use Linq2sql to call my stored procedures, and also simply because it generates .net code so quickly that I can call from my application by simply dragging and dropping it, basically, in a matter of seconds. However, I think you need to ask yourself how much time you spend on these stored procedures. If you do this, you will save a lot of time using linq2sql making your calls. I use sprocs only when performing operations with several steps in the database.

Answer 3

LINQ-to-SQL supports matching many operations (including CRUDs) with stored procedures, but you lose the ability to link - i.e. you cannot just add (exp).Where(x=>x.IsActive) . One option is table functions (UDF) instead of stored procedures that request data; it also presents a tougher metamode (rather than SET FMT_ONLY ON , which is amazing).

Thus, your query methods can be compiled into a database; but note that Entity Framework does not support it , although LINQ-to-SQL does.

Answer 4

Just to solve your related question: the benefits of using stored procedures are not as important as before. Although I would not say that stored procedures are evil, as some have said in the past ( http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html - this is very at least) , I would say that using dynamic sql, if done in a clearly structured way, is quite acceptable these days.

I am not trying to force developers to abandon the use of stored procedures, but this dynamic sql should be considered as an acceptable option.

Answer 5

You lose the ability to write Linq queries, which is the best part of linq-to-sql.