Chapter 10. Using Stored Procedures and Database Functions with LINQ to SQL
LINQ to SQL generates dynamic SQL for queries and inserts, updates, and deletes. However, in some cases data access must be made through stored procedures, also called sprocs. Stored procedures may encapsulate business logic, ensure security restrictions, or provide an optimized and restricted way to query or modify data. Occasionally a database may be accessible exclusively through stored procedures. Hence, LINQ to SQL fully supports stored procedures for CRUD operations. This chapter looks at how to specify the stored procedures and database functions for mapping and how to use them to perform CRUD operations.
Stored Procedures and Functions for Querying
Let’s begin by exploring techniques for querying data using stored procedures, user defined functions, and table value functions. Later in the chapter, we will see how to use stored procedures to perform create, update, and delete operations.
Mapping and Using a Stored Procedure
The sample Northwind database contains a stored procedure titled SalesByCategory. To use it in LINQ to SQL, open the project used for the code in previous chapters and follow these simple steps:
1. Open the Northwind.dbml file in the project.
2. In the Server Explorer pane, expand the Stored Procedures node under the Northwind database, and select the SalesByCategory stored procedure.
3. Drag and drop the selected stored procedure on the designer surface to generate a method with the same name. The left pane shows classes generated from tables, and the right pane shows methods generated from stored procedures and database functions. As a result of the drag-and-drop operation, the LINQ to SQL designer displays a method with the same name—SalesByCategory.
4. Right-click the method and choose Properties to see additional details, as shown in Figure 10.1.
Figure 10.1. A stored procedure mapped to a method.
5. Save the dbml file to regenerate code and mapping to include the displayed method.
6. In Solution Explorer, double-click Northwind.designer.cs to view the generated method NorthwindDataContext.SalesByCategory().
You could also use SqlMetal with the /sprocs option to automatically map stored procedures. However, unlike in the case of the designer, you cannot selectively map stored procedures or change their return types easily without editing the intermediate dbml file.
As shown in Figure 10.1, LINQ to SQL autogenerates a type to match the shape of the result returned by the stored procedure. The method signature in step 6 shows that the return type is ISingleResult<SalesByCategoryResult>. ISingleResult is a generic return type that LINQ to SQL uses for methods used to call stored procedures returning a single result set. LINQ to SQL generates the SalesByCategoryResult type for the SalesByCategory stored procedure.
The generated method is ready for use, as shown next. The period after the DataContext variable db shows the SalesByCategory method in IntelliSense.
using System.Data.Linq;
NorthwindDataContext db = new NorthwindDataContext();
db.Log = Console.Out;
ISingleResult<SalesByCategoryResult> SalesResult =
db.SalesByCategory("Produce", "1997");
Console.ReadLine();
ObjectDumper.Write(SalesResult);
The execution produces translated SQL with stored procedure invocation; the results are as follows. Notice that unlike the case of a normal IQueryable<T> query expression, the execution of the method SalesByCategory is not deferred. Even before you press Enter for the Console.ReadLine() call, the stored procedure has been executed.
EXEC @RETURN_VALUE = [dbo].[SalesByCategory] @CategoryName = @p0,
@OrdYear = @p1
-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Produce]
-- @p1: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [1997]
-- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1
ProductName=Longlife Tofu TotalPurchase=1001.00
ProductName=Manjimup Dried Apples TotalPurchase=24571.00
ProductName=Rossle Sauerkraut TotalPurchase=13949.00
ProductName=Tofu TotalPurchase=6234.00
ProductName=Uncle Bob's Organic Dried Pears TotalPurchase=9186.00
Thus, using the designer, you can execute a stored procedure by simply invoking the corresponding generated methods with suitable parameters. LINQ to SQL does all the plumbing associated with creating parameters, executing the stored procedure, and materializing objects from the rows in the stored procedure result.
The preceding example shows how to get results of the shape specified by the stored procedure. The type SalesByCategoryResult is not a class with a key; it is not an entity class that can be used for insert, update, and delete operations. It is limited to read-only use. But the designer also lets you specify an existing entity type if you want to retrieve entities using a stored procedure. Let’s look at a stored procedure that returns rows with key values that can be used to create entities. Because the sample Northwind database available for download on the web does not contain a suitable entity-shape returning stored procedure, we will add the following stored procedure to Northwind for use in this example:
CREATE PROCEDURE OrdersByCustomer @CustomerID nchar(5)
AS
SELECT *
FROM Orders
WHERE CustomerID = @CustomerID
You can add the OrdersByCustomer stored procedure to a copy of the Northwind database by running the preceding SQL in Visual Studio 2008 or SQL Server Management Studio or a similar tool. In Visual Studio, right-click the database in Server Explorer, select New Query, and run the SQL just shown. As soon as the stored procedure is created in the database, it is available for use in the Server Explorer. Using the steps outlined a moment ago, you will map this newly created stored procedure. The resulting method with the same name, OrdersByCustomer, returns a result type that the designer autogenerates by default. To use an existing entity type obtained by dragging and dropping a table, change the return type, and pick Order instead of the autogenerated type, as shown in Figure 10.2.
Figure 10.2. A stored procedure returning an entity type.
The replacement of the autogenerated type brings up the confirmation dialog shown in Figure 10.3. Click the Yes button, and save the dbml file to regenerate the code, including the OrdersByCustomer method.
Figure 10.3. Stored procedure returning an entity type.
The OrdersByCustomer method returns ISingleResult<Order>. It can be used as follows:
NorthwindDataContext db = new NorthwindDataContext();
db.Log = Console.Out;
ISingleResult<Order> OrdersQuery = db.OrdersByCustomer("BOLID");
ObjectDumper.Write(OrdersQuery);
The result of executing this code is the execution of the corresponding stored procedure and the materialization of a set of Order entities for the customer with ID BOLID. The returned entities can be modified just like entities retrieved using dynamic SQL. The following listing shows the generated SQL for stored procedure execution and the first order in the results:
EXEC @RETURN_VALUE = [dbo].[OrdersByCustomer] @CustomerID = @p0
-- @p0: Input NChar (Size = 5; Prec = 0; Scale = 0) [BOLID]
-- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1
OrderID=10326 CustomerID=BOLID EmployeeID=4
OrderDate=10/10/1996 RequiredDate=11/7/1996
ShippedDate=10/14/1996 ShipVia=2 Freight=77.9200
ShipName=Bolido Comidas preparadas ShipAddress=C/ Araquil, 67
ShipCity=Madrid ShipRegion=null ShipPostalCode=28023
ShipCountry=Spain Order_Details=...
Customer={ }
...
Using Stored Procedures That Return Multiple Results
So far we have looked at stored procedures that return single results. However, stored procedures can also return multiple results. Currently, the graphical designer doesn’t support mapping of such stored procedures. However, you can use the command-line tool SqlMetal.exe or map them manually, as shown next.
You will go through three steps to learn how to use a stored procedure that returns multiple results. First you will create a stored procedure, and then you will map it to a method. Finally, you will execute the method to obtain multiple results.
The sample Northwind database does not have a suitable stored procedure returning multiple entities. So first, add a suitable stored procedure to the sample Northwind database. The following stored procedure returns suppliers and customers from a given city as two distinct results. As before, right-click the Northwind database in Server Explorer, select New Query, and run the following SQL:
CREATE PROCEDURE SuppliersAndCustomers @City nvarchar(15)
AS
SELECT *
FROM Suppliers
WHERE City = @City
SELECT *
FROM Customers
WHERE City = @City
Next, add a partial class for the generated NorthwindDataContext class. Right-click the designer surface and select View Code. The designer creates a separate file for you to write code, with a stub declaration for the corresponding partial class. Add the following code for mapping the newly created stored procedure. The mapping specifies the stored procedure name and the result types. The method parameter indicates the corresponding stored procedure parameter. The method body is similar to the one generated by the designer for executing any stored procedure based on the mapping attributes. The return type IMultipleResults exposes multiple results that you will use next.
public partial class NorthwindDataContext
{
[Function(Name = "dbo.SuppliersAndCustomers")]
[ResultType(typeof(Supplier))]
[ResultType(typeof(Customer))]
public IMultipleResults SuppliersAndCustomers(
[Parameter(Name = "City", DbType = "NVarChar(15)")] string city)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), city);
return ((IMultipleResults)(result.ReturnValue));
}
}
Now the method is available for getting suppliers and customers. Add the following code to Program.cs to use the multiple results returned by the stored procedure:
using(IMultipleResults results = db.SuppliersAndCustomers("London"))
{
List<Supplier> suppliers = results.GetResult<Supplier>().ToList();
List<Customer> customers = results.GetResult<Customer>().ToList();
ObjectDumper.Write(suppliers);
ObjectDumper.Write(customers);
}
Executing this code brings back suppliers and customers from London as two separate results. Here, only the first customer is shown; the others are elided:
EXEC @RETURN_VALUE = [dbo].[SuppliersAndCustomers] @City = @p0
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]
-- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1
SupplierID=1 CompanyName=Exotic Liquids ContactName=Charlotte
Cooper ContactTitle=Purchasing Manager
Address=49 Gilbert St. City=London Region=null PostalCode=EC1 4SD
Country=UK Phone=(171) 555-2222
Fax=null HomePage=null Products=...
CustomerID=AROUT CompanyName=Around the Horn
ContactName=Thomas Hardy ContactTitle=Sales Representative
Address=120 Hanover Sq. City=London Region=null
PostalCode=WA1 1DP Country=UK Phone=(171) 555-7788
Fax=(171) 555-6750 Orders=...
...
Using Table-Valued Functions
Stored procedures return results but do not allow further query composition on the server. You cannot use a stored procedure in place of a table to write a SQL query. You can use LINQ to Objects to further query the results of a stored procedure. However, such a query is executed entirely on the mid-tier or client machine and cannot benefit from the indexes in the database or the capabilities of the SQL query optimizer.
A table-valued function (TVF) can be used in place of a table in an SQL query. LINQ to SQL lets you exploit this capability by allowing a method mapped to a TVF in a LINQ query in a composable fashion. Such a query expression is translated to SQL, and it is executed entirely by the database server.
Let’s add the following TVF to the copy of the sample Northwind database. You can use the same steps described earlier to run the following SQL for a TVF in Visual Studio using the Server Explorer. The TVF returns the orders shipped by a particular shipper identified by the TVF parameter.
CREATE FUNCTION OrdersByShipper(@shipper integer)
RETURNS TABLE
AS
RETURN (SELECT *
FROM Orders ord
WHERE ord.ShipVia = @shipper)
The LINQ to SQL designer lets you map a TVF just like a stored procedure. Expand the Functions node in Server Explorer to view the newly created TVF. Drag it to the designer surface. In the property grid, change the return type from autogenerated to Order. Next, save the dbml file to generate the corresponding method. In Northwind.designer.cs, a new method appears as follows:
[Function(Name="dbo.OrdersByShipper", IsComposable=true)]
public IQueryable<Order> OrdersByShipper([Parameter(DbType="Int")]
System.Nullable<int> shipper)
{
return this.CreateMethodCallQuery<Order>(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), shipper);
}
Unlike the case of a stored procedure, the resulting method is marked as composable using the IsComposable property of the Function mapping attribute. This tells the LINQ to SQL runtime that the method may be used in place of a table. The following code in Program.cs does just that:
IQueryable<Order> orders = from o in db.OrdersByShipper(1)
where o.Customer.City == "London"
select o;
Console.ReadLine();
ObjectDumper.Write(orders);
The first statement shows how the mapped method OrdersByShipper() can be used with the appropriate parameter in place of the Orders table. The second statement, Console.ReadLine(), lets us see the execution semantics. As in the case of LINQ to SQL queries shown in previous chapters, and unlike the stored procedure executions shown in this chapter so far, the TVF-mapped method can be composed inside an expression tree that is translated into SQL only when the results are consumed. Pressing the Enter key provides the following results. As in the previous results, Customer entities beyond the first one are elided in the following listing:
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID],
[t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate],
[t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress],
[t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode],
[t0].[ShipCountry]
FROM [dbo].[OrdersByShipper](@p0) AS [t0]
LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t1].[CustomerID] =
[t0].[CustomerID]
WHERE [t1].[City] = @p1
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1
OrderID=10355 CustomerID=AROUT EmployeeID=6 OrderDate=11/15/1996
RequiredDate=12/13/1996
ShippedDate=11/20/1996 ShipVia=1 Freight=41.9500
ShipName=Around the Horn ShipAddress=Brook Farm Stratford St. Mary
ShipCity=Colchester ShipRegion=Essex ShipPostalCode=CO7 6JX
ShipCountry=UK Order_Details=...
Customer={ }
...
Using Scalar-Valued Functions
Scalar-valued functions return a single value, such as an integer, string, or DateTime. Like their table-valued counterparts, scalar-valued functions are also free from side effects; hence, they can be composed into a SQL query. Hence, LINQ to SQL also supports scalar-valued functions in LINQ queries.
Let’s add the following scalar-valued function to a copy of the sample Northwind database using the steps described in the previous sections. The function AverageProductUnitPriceByCategory returns the average price for the given product category.
CREATE FUNCTION AverageProductUnitPriceByCategory
(@categoryID int)
RETURNS Money
AS
BEGIN
DECLARE @ResultVar Money
SELECT @ResultVar = (SELECT Avg(UnitPrice)
FROM Products
WHERE CategoryID = @categoryID)
RETURN @ResultVar
END
In the LINQ to SQL designer, drag and drop the newly created function onto the designer surface. The designer lists a method with the same name as the database function. The property grid shows that the method returns System.Decimal—the default mapping for the database type Money. Save the dbml file, and use the generated method in a query as follows:
var categoryQuery =
from c in db.Categories
where db.AverageProductUnitPriceByCategory(c.CategoryID) < 22
select c;
ObjectDumper.Write(categoryQuery);
This LINQ to SQL query uses the method mapped to the scalar-valued function inside a regular LINQ query. It produces the following SQL query and results. The scalar-valued function is composed inside the generated SQL query.
SELECT [t0].[CategoryID], [t0].[CategoryName],
[t0].[Description], [t0].[Picture]
FROM [dbo].[Categories] AS [t0]
WHERE [dbo].[AverageProductUnitPriceByCategory]([t0].[CategoryID]) <
@p0
-- @p0: Input Decimal (Size = 0; Prec = 33; Scale = 4) [22]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1
CategoryID=5 CategoryName=Grains/Cereals
Description=Breads, crackers, pasta, and cereal Picture={ }
Products=...
CategoryID=8 CategoryName=Seafood Description=Seaweed and fish
Picture={ } Products=...
Stored Procedures for Inserts, Updates, and Deletes
Stored procedures are often used in databases for create (insert), update, and delete (CUD) operations. They may ensure access restrictions or include business logic related to the operations. Hence, in certain cases, a developer may have no option but to use the stored procedures provided by a database administrator (DBA) for persisting changes to the database. LINQ to SQL supports such stored procedures through a combination of designer and runtime support.
Stored procedures can be used via a more general-purpose mechanism in LINQ to SQL for overriding CUD operations. The mechanism works as follows: if the DataContext class contains a method with a canonical name and signature of a CUD operation, that method is considered to override the generation of normal dynamic SQL commands for CUD operations. The CUD override methods use convention rather than configuration as follows. For the Order entity, the three methods are
void InsertOrder(Order instance) {...}
void UpdateOrder(Order instance) {...}
void DeleteOrder(Order instance) {...}
The method bodies can contain arbitrary logic, including a call to a stored procedure for carrying out the corresponding operation. During SubmitChanges() processing, for each CUD operation, the LINQ to SQL runtime checks if there is a corresponding CUD override method for that entity and invokes the method if one is found.
The code generator used in the designer and SqlMetal provides additional help by pregenerating the method signatures as partial method declarations in the DataContext class as follows:
partial void InsertOrder(Order instance);
partial void UpdateOrder(Order instance);
partial void DeleteOrder(Order instance);
The LINQ to SQL designer further simplifies the use of stored procedures in the bodies of such override methods with a dialog for specifying the operation and the stored procedure parameters. Let’s add the following stored procedure for updating an Order row to the copy of the sample Northwind database using the steps mentioned in previous sections.
CREATE PROCEDURE UpdateOrder
@OrderID int,
@CustomerID nchar(5),
@EmployeeID int,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15)
AS
UPDATE Orders
SET
CustomerID = @CustomerID,
EmployeeID = @EmployeeID,
OrderDate = @OrderDate,
RequiredDate = @RequiredDate,
ShippedDate = @ShippedDate,
ShipVia = @ShipVia,
Freight = @Freight,
ShipName = @ShipName,
ShipAddress = ShipAddress,
ShipCity = @ShipCity,
ShipRegion = @ShipRegion,
ShipPostalCode = @ShipPostalCode,
ShipCountry = @ShipCountry
WHERE
OrderID = @OrderID
RETURN @@ROWCOUNT
Next, drag and drop the stored procedure onto the designer surface to obtain the UpdateOrder() method, as shown in Figure 10.4.
Figure 10.4. An update stored procedure mapped to a method.
In the left pane, which contains entity classes, right-click the Order class, and select Configure Behavior, as shown in Figure 10.5.
Figure 10.5. Configuring CUD operation on an entity.
The Configure Behavior dialog lets you specify a stored procedure for CUD operations and map its parameters, as shown in Figure 10.6.
Figure 10.6. Specifying a stored procedure for CUD operations on an entity.
Select Order from the Class drop-down, Update from the Behavior drop-down, and UpdateOrder from the Customize drop-down. The stored procedure parameters and entity properties are matched by name and presented. In this case, you don’t need to change any parameter mappings, so click OK to complete the generation of an update override method. Now all Order update operations will be routed through the override method and, in turn, through the UpdateOrder stored procedure listed earlier. Insert and delete operations can be configured in a similar fashion. Note that the override methods are not meant to be called in your code; they are defined so that the LINQ to SQL runtime can call them at the appropriate point when your code calls SubmitChanges().
Stored Procedures for Loading Relationships
Query and CUD operations are the foundation of any object-relational mapping solution. All these operations can be easily done using stored procedures. However, as discussed in Chapter 8, “Reading Objects with LINQ to SQL,” the real attraction of an object-relational mapping solution is the “power of the dot.” You can navigate from a Customer to its Orders by simply referencing the Customer.Orders property. There is no need to do explicit queries or joins. Hence, LINQ to SQL also lets you load related entities using stored procedures. It uses a similar override pattern to load related objects. For example, the following signatures define override methods to load Customer.Orders and Order.Customer, respectively:
partial class NorthwindDataContext
{
private IEnumerable<Order> LoadOrders(Customer customer) { ... }
private Customer LoadCustomer(Order order) { ... }
}
Normally, LINQ to SQL formulates dynamic SQL to load related entities. However, if it finds an override method following the canonical method name and signature, as just shown, it uses the override method instead of executing dynamic SQL. Such overrides may be used for either deferred or eager loading.
Let’s use the previously defined stored procedure OrdersByCustomer to load Customer.Orders. As in the case of stored procedures returning multiple results, this requires the addition of code to a partial class. Add the following code to the partial class created in the previous section:
private IEnumerable<Order> LoadOrders(Customer customer)
{
return this.OrdersByCustomer(customer.CustomerID);
}
LINQ to SQL uses this override method to load the corresponding collection, as in the following code:
var cust = db.Customers.Where(c => c.CustomerID == "BOLID").Single();
ObjectDumper.Write(cust.Orders);
The query and results for this code show that the OrdersByCustomer stored procedure is called for loading cust.Orders as follows. The query for loading the customer with ID BOLID is done using dynamic SQL, but the deferred loading of cust.Orders does not use dynamic SQL; it uses the mapped stored procedure instead.
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],
[t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [BOLID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1
EXEC @RETURN_VALUE = [dbo].[OrdersByCustomer] @CustomerID = @p0
-- @p0: Input NChar (Size = 5; Prec = 0; Scale = 0) [BOLID]
-- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1
OrderID=10326 CustomerID=BOLID EmployeeID=4 OrderDate=10/10/1996
RequiredDate=11/7/1996 ShippedDate=10/1
4/1996 ShipVia=2 Freight=77.9200 ShipName=Bolido Comidas
preparadas ShipAddress=C/ Araquil, 67
ShipCity=Madrid ShipRegion=null ShipPostalCode=28023
ShipCountry=Spain Order_Details=...
Customer={ }
...
Summary
Stored procedures are not only a key mechanism for data access. In some cases, they may be the only available mechanism. LINQ to SQL supports queries using stored procedures returning single or multiple results. It also supports composable queries using table-valued functions (TVF) or scalar-valued functions. Stored procedures can also be used in overrides for insert, update, and delete operations. Finally, the set of operations is significantly expanded by the ability to use stored procedures or table-valued functions for relationship loading. These capabilities can be used together or in combination with dynamic SQL to get the best combination of flexibility, security, and performance.