How to call Stored procedure using Linq

While working on a reporting project, I wanted to use Linq to Sql to fetch some data using stored procedure. After I looked at some of the samples microsoft provided and reading some blogs, they all talked about using a typed DataContext object. So far I have been using generic DataContext object and doing some manual set up of queries etc. Here is some description of how to use Linq to Sql ORM mapper provided in Visual Studio 2010. It is very straigh forward to use and work with and get you up and running in no time.

• Right click on your project to bring up context menu and then choose option to add new item.

• Now depending on language you are choosing for your project, you will have option to select what type of item you want to add. Client on Data in left pane. Now you will see an option of LINQ to Sql Classes. Click on it.

  

• Pick a name for your mapper object and click ok. It will bring up ORM designer for you. From Server Explorer select the database that you want to work with. Depending on all the objects that you want to work with (Tables, Stored Procedures etc.), drag it from explorer tree to designer surface in middle. You will see that tables get added on left side and stored proceddures get to right side. Do this for all objects that you want to map. I just added one table and one stored procedure for demo.

  

• Now just write two lines of code to call a stored procedure as shown below.

  static void Main(string[] args)
  {
    var dataCtx = new AdventureWorksDataDataContext();
    dataCtx.uspGetBillOfMaterials(1, DateTime.Today);
  }
  

  It could not get any easier. You can see that ORM mapper has already provided you with a method to call your stored procedure and has stongly typed parameters that need to be passed to stored procedure.

• When you make any change to your database objects in database, just right click on DBML file in your project and choose Run Custom Tool option to synchronize your ORM objects with database.