Goodbye Stored procedures, It’s the time to move on "Linq to Sql"

O/RM Vs Stored Procedures. Which is the best approach?

For the last few years, many developers and architects are engage in a series of debates about ORM Vs Stored Procedures. Many of them argue for ORM and others are arguing for stored procedures.  The interesting thing is that people with highly object orientation sense, recommends ORM. The J2EE community strongly recommending the ORM approaches instead of using stored procedures. Some .NET developers coming from Visual Basic 6.0 background supports stored procedures. Hibernate and NHibernate (.NET version of java version Hibernate) are the highly successful ORM that using both .NET and J2EE community. Then which is the best approach for data persist? Personally I hate stored procedures and strongly recommend for ORM instead of using the legacy stored procedure programming. 

Why I hate stored procedures?

Stored Procedures are written by DB languages such as PL/SQL and T-SQL and this type of languages are not designed for writing business logic and debugging process is really a nightmare. And stored procedures hide the business logic and lacks readability of business process. If you are going to port DB from one RDBMS to another one, you have to re-write your all stored procedures. If you want to run your product on multiple databases, the ORM is the right approach. And ultimately the stored procedure restricts the proper business abstraction. Many people argue that stored procedure provides better performance than dynamic generated Sql from an ORM and people believed that all stored procedure are pre-compiled. According to Microsoft Sql Server documentation, Sql Server does cache the execution plan for stored procedure instead of pre-compiled. Have a look at the MSDN article Execution Plan Caching and Reuse.  And I believe that maintainability, scalability and proper abstraction are the key factors of enterprise applications. The ORM approach enables these benefits.

If you are a .NET developer, there is happy news for you. A new ORM named Linq to Sql coming from the Redmond campus along with the .Net framework 3.5.

What is LINQ to SQL?

LINQ to SQL is an O/RM (object relational mapping) of the .NET Framework 3.5. It provides you to model a relational database using .NET classes.  You can then query the database using LINQ, as well as insert, update and delete data from it. LINQ to SQL supports all types of database objects such as views, and stored procedures and also transactions. It also provides an easy way to integrate data validation and business logic rules into your data model. Visual Studio 2008 provides a Linq to SQL designer that enables to model and visualize a database as a LINQ to SQL object model. You can create the all database representations using the Linq to SQL designer. With the Linq to SQL designer, you can drag and drop the tables into the Linq to SQL designer surface and can represent the relations between tables. Linq to SQL allows you to model classes that map to tables within a database.  These classes are known as "Entity Classes" and instances of them are called "Entities".  Like other OR/Ms, the Linq to SQL OR/Ms will generate the SQL statements at the runtime when interacting with the Entity Classes.

Lets look at the below business object that mapped the customers table using Linq to Sql.

public class Customer
            public string CustomerID;

            public string CustomerName;

            public string City;

            public string Phone;

 After modeling the Database with Linq to Sql, we can do all DB operations against it. The below code is the query against Customer object that represents the Customer table.

DataContext db = new DataContext();

var q = from c in db.Customer
            where c.City == "Cochin"
            select c;

In the above query will select customers of city Cochin .The DataContext represent an abstraction of your database.

The below code is update existing customer

  DataClassesDataContext db=new DataClassesDataContext();

  Customer cust=db.Customer.Single(c=> c.CustomerID ==”C101”);



The below code is add new customer

DataClassesDataContext db=new DataClassesDataContext();

Customer cust=new Customer();

Cust. CustomerName=”ABC Ltd”





The below code is delete customer

DataClassesDataContext db=new DataClassesDataContext();

Customer cust=db.Customer.Single(c=> c.CustomerID ==”C101”);


The below code is using a join query

var orders =
from o in db.Orders
join c in db.Customer on o.CustomerID equals c.CustomerID
where c.CustomerID == "C101"
select new {c.CustomerName, o.ShipName, o.ShipAddress };

Linq to Sql is an exciting ORM tool from Microsoft and I hope that people will use this ORM along with .NET 3.5 applications.


  • Try creating the customer outside the context and add it later.. then you realize.. did Microsoft forget to tell me that Linq doesnt support this out of the box?

  • To say stored procedures hide the business functionality is a definitely defined as a comment coming from someone who really doesnt understand TSQL and wants to hide that fact. If all your business functionality is wrapped within your TSQL and your frontend correctly acts as a pure presentation layer then you will find that nothing is hidden - its all there -> In your stored procedures !
    We have worked with 100% SProcs used in our systems for years. All our business logic sits ONLY on the DB side. If we have major business changes we find them easy to do by just modifying stored procedures. And no - nothing is hidden - its all there.

  • Good article. The database should act solely as a repository with no business logic incorporated within. That doesn't imply the database shouldn't have constraints as the primary goal, without exception, is the protection of its data.

    Goodbye and good riddance to Stored procs indeed.

  • We only use stored procedures for data retrieval. Also security is MUCH more reliable granting execute only to procs instead of each database table and view. Looks like a step backwards of the intent is to NOT use procedures. Especially since the link explaining the Execution Plan Caching and Reuse, this is true HOWEVER, if you have a bad plan to begin with, it will Still perform poorly, no matter if it is direct SQL call via LINQ OR a proc that is poorly written! One other aspect lost is change management. If procs are used solely for data retrieve, update, delete, insert, there should be little to change in them as long as the schema design does not change... not good arguments to me.

  • Like so many other ORM promoters, you've gone with the argument that if you need to change your back end database you have to rewrite stuff and then you assume that your middle tier will never change, so the business logic will be safe there.

    From 15 years of experience I can tell you that most corporate IT departments that I've worked with change their middle tier a lot more often than they change their database technology.

    You also mention abstraction being one of "the key components to enterprise architecture". If that's the case, then why would you possibly want to couple your middle tier directly to tables rather than abstract them through stored procedures???

  • Thanks this resolve a issue with the partner.

  • >We have worked with 100% SProcs used in our systems for years. All our business logic sits ONLY on the DB side.

    Have you even heard of OOP and modelling your business rules with classes and objects? I am a fan of sprocs, but i restrict their usage for basic CRUD functionality, anything else is modelled in the middle tier.

  • additional value is revealed when you have associated objects.. So when you save an object of type A you are persisting all related objects TypeA.TypeB.... Even more of a benefit is that you can have simple external transaction wrapping it up, so you never have the possibility of a stored procedure creating a record in table A but not in table B....

    this discussion is the same as arguing for and against object oriented programming. The benefits are obvious: type-safe scalable and readable code.

    Humans make mistakes, and by adding stored procedures you better know what you are doing cause you can easily reak havoc.

    I myself am a big fan of nHibernate and Linq seems to offer the same advantages. And i loathe cursors and all the other bs that should be in easily extensible code rather than TSQL...

  • I couldn't agree more with:

    Monday, February 16, 2009 11:12 AM by Tom

    Linq is just another layer to convolute matters. Should a business layer be aware of a database or a table name?

    If we have to learn good patterns and pactices with OO, then shouldn't we learn correct db methods?

    If I want to access XML, why should I use Linq when I have DOM or SAX?

    The pro arguments for things like Linq are from people who don't understand db's and SQL.

    How would you like someone who is not an OO expert to cut corners as he doesn't fully understand OO principles? There would be uproar.

    I'm sorry, but I will never use Linq.

  • I just don't understand why people fear/hate stored procs so much. If you know what you are doing it makes life easier, not harder. Try going outside the vanilla with all these auto generated classes in the OOP paradim and you run into continuous trouble, and write a lot more code. I'm not anti OOP and definately not anti LINQ, (LINQ rocks!), but every time I see the "stored procs suck" type of post it is like reading right out of some kind of sales book. Its all pitch verbage. I wouldn't bother to express my opinion (hey, 90% of the people who read this blog are probably more knowledgable than myself), but I feel I have to just to counter balance things. Everything has its place.

  • This article is pretty old but the arguments you present are a little funny. The truth is that Linq doesn't eliminate stored procs. Yes it is easy to access tables directly, however, people who have strong sql knowledge and a strong programming background understand that the use of stored procedures is done for a multitude of reasons. First, it is important for DBAs to be able to understand which applications are accessing which tables. By utilizing stored procs, DBAs are able to tell exactly what is going on. They can optimize the procedures to make sure that your procs are getting the most out of the database system. In addition to that, when you reference a table directly, any changes to that underlying table will break your code. If you are using stored procedures, processes against the tables may have no effect on your tables at all - especially if you are using views to further abstract your data.

    There are additional reasons, but I don't feel like listing them all here. Directly accessing tables in your code is a bad idea - no matter how easy it is.

  • its not that fear of learning new things..but SP hiding business logic..well if entire data you want to manipulate resides on SQL server, to me it makes sense to just manipulate entire data on sql server and bring back only data that is and compiled form of SP always gives you edge. its a case by case scenario where to use Linq and SP..

  • I just a beginner to LINQ,from mine humble opinion, I like the LINQ, it make the code more easy, but after I want to apply the 3 tier programming, I found it not so convenience, Let say We using Data Layer to get Customers info using DataContext A and pass bacl to GUI, after GUI do some changes to the data, We call again Data Layer to Update the record, in this case I using DataContext B, assume I not pass DataContext over the Layer. In order to update the record, I have to get again n assign again the values for each fields n call SubmitChanges(). Which mean We are exec DB twice to do 1 Event which can simple done by SP.

    What I do is Using Code when I want to Get data, but SP for INSERT, UPDATE, DELETE.

  • I´m really septic about machine generated code,I come from banks,where people still uses Cobol,We make code,than we test,test,test,test and after that we lock it.
    Will the ORM generate de code,and change it whenever it thinks it would be good to do so?
    Is there any big ERP system made solely with ORM from scratch?All I see is hello worlds and single screens CRUD samples.
    How about the performance?

  • By the way in my Twenty two years of experience,I´ve seen only two companies changing their RDBMS,that was because CA Dropped Ingres,and Ingress was a little bit weird.
    I use only ANSI syntax,so migrating a SP from ORACLE to SQL Server take a few minutes.
    MVC is great,It makes code more reusable,if you have cash in order to have several Application servers it can perform even better than having SP´S ,and I think they would fit more in the "Cloud era".
    But I still don´t believe in ORM´S,I would like to see the magic and if I like it,investing my time to discover how it works,Still want for the name of very big and trustable systems made with ORM like stuff.

  • I see that although it has been a couple years now this is still a debated topic. Coming from the Java world and having worked on some very large projects it was a shock to me that people in the .NET world were doing so much in stored procedures. I am working on an older .NET application now where even very simple queries get their own stored proc, and I just don't get what is the point when it would be simpler to do in the C# code. Somebody said they don't trust ORM for large projects, but you should know that there are tons of Java projects that are using ORM (usually Hibernate) and it works great. Also, on the large projects I worked on in the past usually we had a DBA team separate from the software developers who would lock down the database and make it hard to make changes. Stupid I know, but this is just a corporate thing that we developers sometimes have to deal with the red tape created by other teams. There is no need to fear the technology just because it is newer. It is really funny to me to see the Cobol programmer chiming with his fear of computer-generated code. Don't you know that your Cobol gets compiled to something else? Only way to not have computer-generated code is to write machine language. To me it is very nice to see that Microsoft is being so innovative and giving us some great new ways of doing things. Instead of thinking you are smarter than the guys at Redmond maybe you should listen to them and maybe you will learn something.

  • Logic in the stored procedures provides a real gain in one of the most important aspect on professional programming. It is called TIME. With stored procedures you save compilation time, loading time, initializacion time, reload application time, recompilation time. They give you agility to try the behavior and results of your application in instant, real time. If you are clear and clean on your stored procedures and in the coding of your classes you can have an elegant system, easy to modify and update.

  • Thanks, this Blog really help to understand the basic concept of ORM. Can i get some more updates on the ORM concept.

  • Linq to SQL is a pain and lacks the advanced functionality of a language you can use in a stored proc. It's only a matter of time until this idea ends up next to Bob.

  • "If you are going to port DB from one RDBMS to another one, you have to re-write your all stored procedures."

    If you changing your RDBMS .. I Must say that you did not design the application properly .. How many times in your App's lifetime do you change the RDBMS .. ?

  • I hate to let this die. (: I have a love-hate with sprocs, but they do offer a few advantages. They offer a finer control when you need to implement strong row level security in business logic such as for a financial or medical or HR application. Its much easier to manage your overall data access - easier to search queries, track down problem queries, virtualize, limit access, track chages, etc. I can greatly reduce traffic between web and db servers by paging in the sprocs - this can be a huge performance gain (gotta love the datagrid loaded up with 5000 rows mapped to an object collection, just to show 20 rows). Also, I can't guarantee all access will flow through .NET in the future. ORM's are fine for ecommerce sites and the like, but not sites with stronging auditing and access control needs.

  • "Have you even heard of OOP and modelling your business rules with classes and objects? I am a fan of sprocs, but i restrict their usage for basic CRUD functionality, anything else is modelled in the middle tier."

    Have you ever heard of the Entity Data Model. You create a stored proc, then generate a class form it directly... With a few small tweaks you can even get insert and update objects to work with from your stored procs. Exactly what you are talking about above. Manage your class objects directly from the database when you use the Data Entity Model correctly.

    And no - Linq has bad performance and no easy way to specify indexes and forces you to use lamda expressions and calculates "stuff" at run time and... I'll stop there.

Comments have been disabled for this content.