LINQ to SQL: Working with Stored Procedures

LINQ to SQL revolutionized how .NET developers interact with SQL Server databases by providing a direct mapping between SQL Server database objects and .NET classes. While LINQ to SQL is excellent for working with tables directly, many enterprise applications rely heavily on stored procedures for data access. This article explores how to effectively use stored procedures with LINQ to SQL.

Why Use Stored Procedures with LINQ to SQL?

Before diving into the implementation details, let's understand why you might want to use stored procedures with LINQ to SQL:

• Performance optimization for complex queries
• Security benefits through controlled access to database objects
• Centralized business logic at the database level
• Legacy system integration where stored procedures already exist
• Batch operations that are more efficient as stored procedures

Adding Stored Procedures to Your LINQ to SQL Data Context

The first step in working with stored procedures is adding them to your LINQ to SQL data context. Visual Studio provides a designer that makes this process straightforward:

1. Open your DBML file in the Visual Studio designer
2. Right-click on the designer surface and select "Add > Stored Procedure..."
3. Select the stored procedures you want to include
4. Click "OK" to add them to your data context

Once added, the stored procedures appear in your data context as methods that you can call from your code.

Types of Stored Procedures in LINQ to SQL

LINQ to SQL supports three types of stored procedures:

1. Select Stored Procedures

These procedures return result sets that can be mapped to entity classes. The designer automatically detects the shape of the result set and can map it to existing entity classes or create new result classes.

// Example of a select stored procedure in SQL Server
CREATE PROCEDURE GetCustomersByCity
    @City nvarchar(50)
AS
BEGIN
    SELECT CustomerID, CompanyName, ContactName, City, Country
    FROM Customers
    WHERE City = @City
END
                

In your C# code, you can call this procedure and work with the results as strongly-typed objects:

using (var db = new NorthwindDataContext())
{
    var customers = db.GetCustomersByCity("London");
    
    foreach (var customer in customers)
    {
        Console.WriteLine($"{customer.CompanyName} - {customer.ContactName}");
    }
}
                

2. Insert, Update, and Delete Stored Procedures

LINQ to SQL allows you to map stored procedures to perform insert, update, and delete operations on your entity classes. This is particularly useful when you need to override the default CRUD operations generated by LINQ to SQL.

To configure these mappings:

1. Select the entity class in the designer
2. Open the Properties window
3. Expand the "Insert", "Update", or "Delete" properties
4. Select the appropriate stored procedure from the dropdown
5. Map the parameters to the entity properties

3. User-Defined Functions

SQL Server user-defined functions can also be used with LINQ to SQL. These functions can be called directly from LINQ queries, allowing you to incorporate database functions into your LINQ expressions.

// Example of using a scalar function in a LINQ query
var discountedProducts = from p in db.Products
                         select new
                         {
                             ProductName = p.ProductName,
                             OriginalPrice = p.UnitPrice,
                             DiscountedPrice = db.CalculateDiscount(p.UnitPrice, 0.1M)
                         };
                

Handling Complex Parameters and Return Values

Input Parameters

Stored procedures often require input parameters. LINQ to SQL makes it easy to pass parameters to your stored procedures:

// Passing parameters to a stored procedure
var results = db.GetOrdersByDateRange(startDate, endDate);
                

Output Parameters

For stored procedures that use output parameters, LINQ to SQL provides a way to capture these values:

// Handling output parameters
decimal totalSales;
var results = db.GetSalesSummary(2009, out totalSales);
Console.WriteLine($"Total sales for 2009: {totalSales:C}");
                

Return Values

Similarly, you can capture the return value from a stored procedure:

// Handling return values
int returnValue;
var results = db.ProcessOrder(orderId, ref returnValue);
if (returnValue == 0)
    Console.WriteLine("Order processed successfully");
else
    Console.WriteLine($"Error processing order: {returnValue}");
                

Best Practices for Using Stored Procedures with LINQ to SQL

1. Use Strongly-Typed Result Sets

Whenever possible, configure your stored procedures to return results that match your entity classes. This allows LINQ to SQL to map the results directly to your entities, giving you strongly-typed access to the data.

2. Consider Transaction Management

When calling multiple stored procedures that need to be part of the same transaction, use the TransactionScope class to ensure proper transaction management:

using (var scope = new TransactionScope())
{
    using (var db = new NorthwindDataContext())
    {
        db.CreateOrder(customer.CustomerID, orderDate);
        db.AddOrderDetails(orderId, productId, quantity);
        
        scope.Complete();
    }
}
                

3. Handle Exceptions Properly

Stored procedure calls can fail for various reasons. Always implement proper exception handling to catch and process these errors:

try
{
    var results = db.GetCustomerOrders(customerId);
    // Process results
}
catch (SqlException ex)
{
    // Handle database-specific errors
    Console.WriteLine($"Database error: {ex.Message}");
}
catch (Exception ex)
{
    // Handle other exceptions
    Console.WriteLine($"Error: {ex.Message}");
}
                

Conclusion

LINQ to SQL provides robust support for working with stored procedures, allowing you to leverage the power of SQL Server's procedural capabilities while still enjoying the benefits of LINQ's strongly-typed, object-oriented approach to data access.

By understanding how to properly integrate stored procedures into your LINQ to SQL data context, you can create applications that combine the best of both worlds: the performance and security benefits of stored procedures with the productivity and type safety of LINQ.

Whether you're working with an existing database that heavily relies on stored procedures or designing a new system that needs to balance ORM convenience with database-level optimizations, LINQ to SQL's stored procedure support offers a flexible and powerful solution.

Any use of exclusive PersiaDevelopers articles is permitted subject to citation of the source and author name.