Using LINQ to SQL with a Stored Procedure

By | May 10, 2010

In this post I’ll go over a very bare bones way to call a stored procedure using LINQ to SQL.


So I was writing up a bare bones tool to send me the results of a handful of queries I wanted to run against our OnTime database. This tool would have at most three clients, would run every hour, and was certainly not mission critical. I didn’t really want to spin up SubSonic or IBatis.NET, and I certainly didn’t want to spend time learning EF just for this mini-project. Amazingly enough I’d gotten away without having to learn LINQ to SQL up to this point, but alas, it seemed like the time was right. It seemed like the right choice, since all I wanted to do was to call a stored procedure in a database and massage the results into some text I’d be emailing out every hour.

For our sample below, we’ll be using Visual Studio 2010 RC and the AdventureWorks database.

Generating the Model

While there a lot of pictures in the below steps, they are pretty straightforward and self-explanatory. What’s really powerful here is the options you’d have if you were after something more than a bare bones way of calling a stored procedure.

Right click on the project node in the Solution Explorer and add a new ADO.NET Entity Data Model.


Name the model AdventureWorks, for obvious reasons.


Click Add.


Ensure that “Generate from database” is selected and click Next.


Set up a new connection to the AdventureWorks database (this will be dependent on your database so I’ll skip the steps). After you have your connection your dialog will look something similar to the below.


And yes, my laptop is a cheapo, so I’m accepting donations. Email me for details! The next step brings up a list of all the objects in the target database that we can model. Since our goal is a bare bones, easy way of calling a stored procedure without a lot boilerplate code, we just need to select the ones we are interested in. For this example, we are going to call uspGetManagerEmployees.


Leave the defaults selected for not and click Finish. Visual Studio will work its magic and add the needed references to your project to support the newly introduced entity data model. Your IDE will appear similar to the below.


We need to tell Visual Studio to generate code so that we can call our stored procedure in a bare bones way. To do this, select the Model Browser tab in the Solution Explorer.


Expand the Stored Procedures node and find the one we’re interested in, uspGetManagerEmployees.


Right click on the stored procedure and select Add Function Import.


As you can see, we have lots of options available to us when adding a function import. For now, we’ll keep most everything set to the default selections. Press the “Get Column Information” button to have Visual Studio go out to the database to determine what our stored procedure returns.


Pretty nifty if you ask me. Even niftier is the button that’s now enabled, “Create New Complex Type.” Click that.


So what we’ve basically done is tell Visual Studio create a new type based on the return columns of the stored procedure we want to call. Click OK. You’ll get taken back out to Visual Studio, but you’ll see now that the stored procedure we were working with has its mapping details filled in.


Again, pretty nifty.

The Code

I saved the best for last in this one. To call our newly modeled stored procedure, we use the code below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;

namespace SimpleLINQtoSQLWithSproc
    class Program
        static void Main(string[] args)
            using (AdventureWorksEntities db = new AdventureWorksEntities())
                ObjectResult<uspGetManagerEmployees_Result> result =

The result object will contain the rows returned from our stored procedure call – that simple. It’s even better than that though. Because of our prior modeling, we have full IntelliSense support on our results object.


So if we wanted to produce a simple list of employees and who they report to, we modify our console app as below.

static void Main(string[] args)
    using (AdventureWorksEntities db = new AdventureWorksEntities())
        ObjectResult<uspGetManagerEmployees_Result> result =

        foreach (var x in result)
                "{0} {1} reports to {2} {3}",

    Console.WriteLine("press any key...");

And that’s it. We’ve modeled our stored procedure, which enables us to execute it and return a strongly-typed result set. This approach is even better when you realize you can throw the full power of LINQ at your result set. So for small apps that don’t need a full blown “architecture,” LINQ to SQL has become my data access tool of choice. Always remember your context when choosing the tool for the job.