LINQ and Stored Procedures

Okay so last quarter I was asked if you can call stored procedures using Language Integrated Query (LINQ).  The answer, of course, is YES!  It would be kind of silly if we didn't include this type of functionality. 😛


NOTE:  You need the following items to do this example

SQL Server (pretty much any version will do but this example uses 2005)

Visual Studio 2008 (Beta 2 was used for this example)

A little time to sling some code



So how exactly DO you call a stored proc using LINQ?  Easy just follow these simple steps:

  1. Create a new Windows Forms Application in Visual Studio 2008, let's call it "LINQ_SP_Coolness"


  2. Next, make sure you have a connection to some data source in your Server Explorer.  You may need to add a new connection.


  3. For the purposes of this discussion I am using the Northwind Database on SQL Server.


  4. Take a look at the existing stored procedures because what you need may already be there.


  5. Aaaaaand since I'm bored watching re-runs tonight, why don't we make our own stored procedure?


  6. Just write a simple SELECT to yank out some data AND remember to save 🙂


  7. Just to make sure, refresh the stored procedure folder on your data source in Server Explorer to see your shiny, new stored proc listed...


  8. You might even want to test it 😛


  9. To get this to a point we can manipulate it using LINQ we need to have our classes and mappings in place.  Fortunately, this is a VERY easy thing to do.  Just right-click your project, choose Project...Add New Item from the Menu Bar.  Let's add a "LINQ to SQL Classes" item with the default name of "DataClasses1.dbml".


  10. Click and drag the stored procedure from Server Explorer on to the LINQ design surface.


  11. You should see the procedure showing up on the upper right of the design surface.


  12. Now let's slap some controls on our Windows Form.  How about a list box and a button?  Live on the edge!


  13. Now we need to work with database by establishing a context to it.  Next, we need to create a query expression that treats the result of the stored procedure as a table and query against it selecting only the LastName column.  Just for fun why don't we sort all the last names as well?  Finally, we will take the list of names returned and loop through them adding each one to our listBox as we go along.

    Double-click on the button to code for the Click event and write the following lines of code:
    (NOTE: make sure to substitute your server name in place of "zero_cool" and if you are using SQL Express you need to modify the server piece to read like this "SQLEXPRESS\myserver")


  14. If all goes well, you should get the following result:


  15. And that's how you use a stored procedure with LINQ.  This was a very simple example but if you really want to see a great series on LINQ go to my buddy Benko's site.   He has a webcast series on LINQ that kicks major butt! 🙂

    [Updated:  added tags]

Comments (19)

  1. jmeridth says:

    Awesome post.  I have the DevCares tomorrow on LINQ and I will definitely be sending people here for how to do this.

    Thanks Zain.

    Also, Server = Zero_Cool???  Hackers fan, much? 🙂

  2. Not a whole lot of links this week. I have been pretty pushed getting some stuff ready for production.

  3. zainnab says:

    I was wondering if anyone would get the reference to Hackers hehehe

  4. Glen says:

    I have a complex stored proc that i would like to run but can not be generated as it contains either temp tables or nested loops.

    Is there a way with linq to call a stored proc explicitly that is not in my datacontext file?

  5. Ron says:

    Do you have C# example like your "LINQ and Stored Procedures" using datagridview in windows forms?


  6. ahtesham says:

    I am using linq 2 sql store procedures . I didn’t change the return type of any store procedure so it is returning isingleResult<storeprocedureResult>

    I make a class ”mydataconetcion” which use the datacontext class object and I am getting all store procedures in that class by

    Datacontext db=new datacontext();

    One example of my store procedure is

    public ISingleResult<dashCommerce_Store_FetchCategoryManufacturersResult> getCategoryManufecturer(int catId)


    ISingleResult<dashCommerce_Store_FetchCategoryManufacturersResult> CategoryManufecture = db.dashCommerce_Store_FetchCategoryManufacturers(catId);

    return CategoryManufecture;


    In my web pages I make object of mydataconetcion class

    Mydataconetcion dc=new mydataconetcion();

    I am storing the result  in var then by foreach I am traversing it . or I simply bind it to my gridview and repetors and so on .

    I am doin this

    Var selectedProduct =dc. getCategoryManufecturer(7);

    CatGridview.datasource= selectedProduct;

    It works fine but now I want something like this

    If (selectedProduct!=null )



    CatGridview.datasource= selectedProduct;


    Or empty or what ever condition that tell me that there is any value in  selectedProduct

    So help me out on this how can I make sure that there is any value or not

  7. This was actually a question in the thread for my post on LINQ and Stored Procedures but it was such

  8. steve says:

    If I fill a temp table in my stored procedure where I define and fill the columns in the stored procedure, how come I can’t get LINQ to recognize that there are records comming back and not an ‘int’?  Seems to think its a retrun value of type int. strange.



  9. JNick says:

    What happen if the store procedures signature change in the DB. Linq will change his won signature or he will do nothing ( you will do erease the current store Procedures and readd this ) ?

  10. zainnab says:

    Yep if the stored proc signature changes you have to re-add the proc to your model so the app is aware of the change

  11. JNick says:

    Can I specified to the dbml to regenerate the designer.cs, each time I build the project ?

  12. zainnab says:

    Well, yes and no.  You can’t do it through the IDE but you can use SQLMetal to autogenerate your dbml for you.  Check out my (very brief) post here:

    And my webcast that mentions it is here:

    Called "LINQ Features in Visual Studio 2008"

  13. Sekharm says:

    This is useful and easier to me TQ

  14. Dan says:

    I’m running into an issue with this and wanted to see if anyone had an idea.  I wrote a stored proc that uses a temp table to compile some complex resutls into a easy to use tabular output.  When I try to use the stored proc through LINQ it incorrectly identifies the output type as an int instead of the result set.  The stored proc works fine.  Any idea why LINQ is doing this?

  15. Mario says: version

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

           Dim db As New DataClasses1DataContext("Data Source=CRAY-1SQLEXPRESS2008R2;Initial Catalog=RA1;Integrated Security=True")

           Dim cadena As String

           cadena = "6390002937"


           Dim result = From C In db.prueba(cadena)

                        Order By C.POS

                        Select C.POS, C.MERCANCIA, C.NUMAUTOR

           For Each stuff In result

               ListBox1.Items.Add(stuff.POS & vbTab & stuff.MERCANCIA)


    End Sub

  16. abdo alamin says:

    i want to ask if i can used linq with proc in mvc or may be make problems

Skip to main content