Using Stored Procedures with LINQ to SQL

ProblemIn 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?

SolutionWe 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 Explorerwindow. 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 Explorerwindow 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