Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Using Stored Procedures with LINQ to SQL

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Language Integrated Query LINQ

Problem
In a recent tip on Querying SQL Server databases using LINQ to SQL, you have described how it can be used to query SQL Server databases. How do I use LINQ to SQL with stored procedures that retrieve database records?

Solution
We use stored procedures to encapsulate business logic on the database server side. This gives us the advantage of modularity, performance, and security to name a few. We can use these stored procedures with LINQ to SQL. LINQ to SQL maps these database-defined abstractions to code-generated client objects, so that you can access them in a strongly typed manner from client code. This gives us the advantage of using Visual Studio's IntelliSense feature to expose them as methods together with the strongly-typed parameters which might be needed to call the stored procedures.

We'll start with the example project used in the LINQ to SQL tip to write LINQ query expressions that call stored procedures. Open the Northwind.dbml file by double-clicking it from the Solutions Explorer window.

This will open up the designer surface for you to drag-and-drop the stored procedures from the Server Explorer window. I'll use the SalesByCategory stored procedure from the Northwind database to retrieve a list of sales record based on the CategoryName and Year passed as parameters. If the stored procedure generates a list of classes based on what is already on the designer surface, you can drag-and-drop the stored procedure from the Server Explorer window to the class that will be generated. Let's say the stored procedure retrieves records from the Products table, you can drop the stored procedure on the Products class in the designer surface. This tells the designer that the resultset of the stored procedure is a list of Products classes. Otherwise, you can just drop it anywhere on the designer surface which will then be added to the DataContext class as a method.

Notice that the data types defined by the parameters accepted by the stored procedure are also strongly-typed. By default, the name of the method will be the same as the name of the stored procedure. If the stored procedure name contain spaces, the designer automatically converts them to the underscore ( _ ) character.

Calling the stored procedure using LINQ

Let's start writing some code inside the static void Main(string[] args).

//Obtaining the data source
var dbNorthwind =new NorthwindDataContext() ;

// Create the query
var query = dbNorthwind.SalesByCategory("Beverages","1997");      
                   

// Execute the query
foreach (var c in query)
{
     Console.WriteLine(c.ProductName + "," + c.TotalPurchase);
}

//Pause the application
Console.ReadLine();

Visual Studio's IntelliSense feature now exposes the DataContext class' new method together with the parameters with their corresponding data types.

Even the results returned by the method are strongly typed

Running the application with logging will look like this when you run your project in Visual Studio. You can press F5 or click on Debug - Start Debugging in Visual Studio.

There is a limitation on calling stored procedures using LINQ to SQL. The designer does not generate code for stored procedures that use dynamic SQL statements to return a result set or those that depend on temporary tables to return a result. This is because LINQ to SQL cannot acquire the metadata for the resultset during design time. But it doesn't mean it is not possible. It just means that you will not be able to use the IntelliSense feature to retrieve the strongly-typed resultsets based on the metadata returned by the stored procedure.

Next Steps

You have seen how LINQ to SQL supports the ability to call SQL Server stored procedures and call them as methods of the DataContext class

  • Start using stored procedures with LINQ to SQL.
  • Learn more about LINQ to SQL


Last Update: 7/17/2008


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, May 15, 2014 - 2:05:27 AM - Vexy Read The Tip

Hi. I have a trouble seeing stored procedures in Visual Studio. I connected to the database (Northwind) but shows me that I don't have any stored procedures. Any idea what can be an isue?? For SQL I use SQL Management Studio 2008 R2 and Visual Studio is 2010.


Saturday, August 30, 2008 - 9:44:42 AM - bass_player Read The Tip

Great to hear you managed to work it out. I forgot to highlight in the article that you have to do things manually when you deal with dynamic SQL or temp tables. This would definitely help you a lot if you're a big fan or IntelliSense, like I am :-)


Friday, August 29, 2008 - 8:11:07 AM - tendrel Read The Tip

 Hi,

I actually figured it out. when there is dynamic sql or temp tables involved in the SProc, you need to set up the class with all the properties and such manually, rather than how it does it automagically for you when there is no dynamic sql.

Then you have to reset the resultType yourself to that new class that you manually built.  In my endeavor, I actually set up a SProc that had a lot of similar return fields, and then used that model to help manually create the fields etc for the SProc with dynamic sql in it.

I don't think the contents of the Sprocs would prove useful for you, if you are having the same issue. Or were you offering to help solve the puzzle? =) Either way, I am good on this end now. Is there anything I might be able to help you with?

 thanks! 

jen 


Friday, August 29, 2008 - 6:57:32 AM - bass_player Read The Tip

Can you post your stored procedure code and LINQ code so I can test it out?


Tuesday, July 22, 2008 - 11:19:51 AM - tendrel Read The Tip

 about the stored procs that contain dynamic sql or temp table dependencies, do you have an example of how get the ResultsType correct? I have stored procs of both types, and I cannot figure how to get the ResulType correct, as it keeps assigning it as an int, and disallows any change I try to make. 

thanks.

jen 




 
Sponsor Information