Using Stored Procedures In Linq

Using Linq to SQL, you can use stored procedure to return your entities. If you want to use the designer, you can go to the server explorer and drag a stored procedure on an existing entity. This would cause the Linq to SQL designer to generate code for calling a stored procedure that returns your entities. If the stored procedure does not return any particular entity, dragging the stored procedure on the designer would generate a class on the fly which would contain properties that match the columns your stored procedure is returning. If you are using SqlMetal utility to generate your entities, than add /sprocs option to indicate that you want code generated to call stored procs in your database. To better understand what code designer generates, let's take a look at the code generated when calling a simple stored procedure.

Dragging the stored procedure generates the following code by Linq to SQL designer.

In the above code, the method GetCustsByCity is attributed with Function parameter indicating that this method calls a stored procedure. One of the parameters for function is a name that determines what stored procedure to call. The name parameter matches our stored procedure name. There is additional parameter called IsComposable. If IsComposable is marked as false which in this case it is, means we are calling a stored procedure instead of a function. Our method takes in a parameter of city which we pass to the stored procedure. To call the stored procedure we make use of protected method called ExecuteMethodCall. ExecuteMethodCall takes the class that is calling the method as the first parameter. To conform to this requirement we pass instance of our self in the first parameter. In the second parameter, we are passing methodInfo object by calling GetCurrentMethod using reflection. The reason ExecuteMethodCall needs this info is because MethodInfo object has the metadata that contains our attribute which tells which stored procedure to call. The third parameter is an array of parameters the stored procedure requires to run.

Calling ExecuteMethodCall returns an instance of IExecuteResult. IExecuteResult is an interface interface that has one method and one readonly property.

If the stored procedure returns output parameters, you can use GetParameterValue to get the value for the stored procedure. The returnvalue property is responsible for returning tabular results from the stored procedure.

If the stored procedure returns a single result, ReturnValue needs to be casted to ISingleResult of the entity type returned as shown above in our generated code. Since ISingleResult of T implements IEnumerable of T, we can enumerate the result and even further filter our results. Since ISingleResult implements IEnumerable of T, any filters applied are executed in memory and are not converted to SQL and send to the database.

Below is an example of using our GetCustsByCity stored procedure in a Linq query. Notice that after calling our stored procedure in a Linq query we are further filtering the result by getting only customers with contact title of Sales Representative.