LINQ to SQL Strongly Typed Stored Procedures

One of the issues I have run into is how to get multiple stored procedures to return a single type using LINQ to SQL. I have seen several methods that edit the Xml of the dbml by hand to do this. This is problematic as your changes revert as the dbml is modified. I found this solution online (although I can’t remember where. Thanks to the original source that I cannot properly credit).

The solution is a class defined in the dbml to serve as the Return Type of the stored procedure. That’s all there is to it.

The Stored Procedure
I’ll be using the adventure works database for this example. If you need this db you can download it here: http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=37109

In particular I wanted a stored procedure that returned composite data. I chose the uspGetManagerEmployees function for its high content vs. low arguments appeal.



The Project
The example application is a simple console application. Create one and accept the defaults. Once VS 2008 has finished loading, right click the project and select Add -> New Item (Crtl+Shift+A).



Select the LINQ to SQL Classes template. I have chosen to name mine DB.dbml. Visual studio will show the LINQ to SQL diagram waiting for your SQL Tables and Procedures. In order to add items to the diagram we need a database connection. Open the Server Explorer (Crtl+Alt+S). Now you need to add a Data Connection if you don’t already have one. Click the Connect to Database button on the Server Explorer (Sorry no default shortcut).



Here you can set your server settings. Once you have a successful test you click OK. This will save the connection information to the app.config file.

Now it’s time to add our Return Type to the diagram. Right click the design surface and select Add -> Class. Select the default class name Class1 and rename it to ManagerEmployee.



Right click the class designer and select Add -> Property. Rename this property to RecursionLevel. This is easier of you use the property window (F4). Change the type of the RecursionLevel to int. Add the rest of type as listed below:



ManagerID int
ManagerFirstName string
ManagerLastName string
EmployeeID int
FirstName string
LastName string

When you are done you should see this:



The next step is to add your stored procedure. Expand the database in the server explorer until you find uspGetManagerEmployees. Select and drag this procedure to the Methods Pane of the diagram. Return to the properties (F4) and change the name of the procedure to GetManagerEmployees. Change the Return Type from (Auto-generated Type) to ManagerEmployee by selecting it from the list. Note: the source of the procedure still points to uspGetManagerEmployees.



Now you can save the dbml. When you do this you will get this prompt. Say Yes to it to make it go away. This is notifying you that the Return Type will be reset to (Auto-generated Type) only by deleting it and re-adding it to the Method Pane.



Once the save operation is complete if you open the DB.designer.cs file you will see the code generated to support your LINQ stored procedure. The part that relates is listed below:



So now we have a nice strongly typed class that can be used by any number of procedures. I have included a simple example to show its use below:



And here are the results:



Well there it is. Even though it is a simple solution, since it is the LINQ to SQL way it persists your changes across edits and prevents hand edits of the source xml.