Friday, October 30, 2009

LINQ to SQL Part 8 - Executing Custom SQL Expressions

LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

Below are the first seven parts in this series:

In my last two posts (Part 6 and Part 7) I demonstrated how you can optionally use database stored procedures (SPROCs) to query, insert, update and delete data using a LINQ to SQL data model.

One of the questions a few people have asked me since doing these posts has been "what if I want total control over the SQL expressions used by LINQ to SQL - but I don't want to use SPROCs to-do it?" Today's blog post will cover that - and discuss how you can use custom SQL expressions that you provide to populate your LINQ to SQL data model classes, as well as perform insert, updates, and deletes.

Using LINQ Query Expressions with LINQ to SQL

For the purposes of this blog post, let's assume we've used the LINQ to SQL ORM designer in VS 2008 to define a set of data model classes like below for the Northwind database (note: read Part 2 of this series to learn how to use the LINQ to SQL ORM designer to do this):

In Part 3 of this blog series I covered how you can use the new LINQ language support in VB and C# to query the above data model classes and return back a set of objects that represent the rows/columns in the database.

For example, we could add a "GetProductsByCategory" helper method to the DataContext class of our data model that uses a LINQ query to return back Product objects from the database:

VB:

C#:

Once we've defined our encapsulated LINQ helper method, we can then write code like below that uses it to retrieve the products, and iterate over the results:

VB:

When the LINQ expression within our "GetProductsByCategory" method is evaluated, the LINQ to SQL ORM will automatically execute dynamic SQL to retrieve the Product data and populate the Product objects. You can use the LINQ to SQL Debug Visualizer to see in the debugger how this LINQ expression is ultimately evaluated.

Using Custom SQL Queries with LINQ to SQL

In our sample above we didn't have to write any SQL code to query the database and retrieve back strongly-typed Product objects. Instead, the LINQ to SQL ORM automatically translated the LINQ expression to SQL for us and evaluated it against the database.

But what if we wanted total control over the SQL that is run against our database, and don't want LINQ to SQL to-do it for us in this scenario? One way to accomplish this would be to use a SPROC like I discussed in Part 6 and Part 7 of this series. The other approach is to use the "ExecuteQuery" helper method on the DataContext base class and use a custom SQL expression that we provide.

Using the ExecuteQuery Method

The ExecuteQuery method takes a SQL query expression as an argument, along with a set of parameter values that we can use to optionally substitute values into the query. Using it we can execute any raw SQL we want against the database (including custom JOINs across multiple tables).

What makes the ExecuteQuery method really useful is that it allows you to specify how you want the return values of your SQL expression to be typed. You can do this either by passing a type-object as a parameter to the method, or by using a generic-based version of the method.

For example, we could change the GetProductsByCategory() helper method we created earlier - using a LINQ expression - to instead use the ExecuteQuery method to execute our own raw SQL expression against the database and return "Product" objects as a result:

VB:

C#:

We can then call the GetProductsByCategory() helper method using the exact same code as before:

But unlike before it will be our custom SQL expression that will run against the database - and not dynamic SQL executed in response to using a LINQ query expression.

Custom SQL Expressions and Object Tracking for Updates

By default when you retrieve a data model object using LINQ to SQL, it will track all changes and updates you make to it. If you call the "SubmitChanges()" method on the DataContext class, it will then transactionally persist all of the updates back to the database. I cover this in more depth in Part 4 of this LINQ to SQL series.

One of the cool features of the ExecuteQuery() method is that it can fully participate in this object tracking and update model. For example, we could write the code below to retrieve all products from a specific category and discount their prices by 10%:

Because we typed the return value of our ExecuteQuery call in the GetProductsByCategory method to be of type "Product", LINQ to SQL knows to track the Product objects we returned from it. When we call "SubmitChanges()" on the context object they will be persisted back to the database.

Custom SQL Expressions with Custom Classes

The ExecuteQuery() method allows you to specify any class as the return type of a SQL query. The class does not have to be created using the LINQ to SQL ORM designer, or implement any custom interface - you can pass in any plain old class to it.

For example, I could define a new ProductSummary class that has a subset of Product properties like below (notice the use of the new C# Automatic Properties feature):

We could then create a GetProductSummariesByCategory() helper method on our NorthwindDataContext that returns results based on it. Notice how our SQL statement below requests just the subset of product values we need - the ExecuteQuery method then handles automatically setting these on the ProductSummay objects it returns:

We can then invoke this helper method and iterate over its results using the code below:

Custom SQL Expressions for Inserts/Updates/Deletes

In addition to using custom SQL expressions for queries, we can also execute them to perform custom Insert/Update/Delete logic.

We can accomplish this by creating the appropriate partial Insert/Update/Delete method for the entity we want to change in a partial class on our DataContext. We can then use the ExecuteCommand method on the DataContext base class to write the SQL we want to execute. For example, to override the Delete behavior for Product classes we could define this DeleteProduct partial method:

And now if we write the below code to remove a specific Product instance from our database, LINQ to SQL will call the DeleteProduct method - which will cause our custom SQL to execute in place of the default dynamic SQL that LINQ to SQL would otherwise use:

Summary

The LINQ to SQL ORM automatically generates and executes dynamic SQL to perform queries, updates, inserts and deletes against a database.

For advanced scenarios, or cases where you want total control over the SQL query/command executed, you also have the ability to customize the ORM to use either SPROCs, or your own custom SQL Expressions, instead. This provides you with a great deal of flexibility when building and extending your data access layer.

In future blog posts in this series I'll cover some remaining LINQ to SQL concepts including: Single Table Inheritance, Deferred/Eager Loading, Optimistic Concurrency, and handling Multi-Tier scenarios.

Thursday, October 29, 2009

LINQ to SQL Part 7 - Updating our Database using Stored Procedures

LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

Below are the first six parts in this series:

In part 6 I demonstrated how you can optionally use database stored procedures (SPROCs) and user defined functions (UDFs) to query and retrieve data using your LINQ to SQL data model. In today's blog post I'm going to discuss how you can also optionally use SPROCs to update/insert/delete data from the database.

To help illustrate this - let's start from scratch and build-up a data access layer for the Northwind sample database:

Step 1: Creating our Data Access Layer (without using SPROCs yet)

In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer that is built-in to VS 2008 to create a LINQ to SQL class model like below:

Adding Validation Rules to our Data Model Classes

After defining our data model classes and relationships we'll want to add some business logic validation to our data model. We can do this by adding partial classes to our project that add validation rules to our data model classes (I cover how to-do this in-depth in my Part 4: Updating our Database LINQ to SQL tutorial).

For example, we could add validation rules to enforce that the Customer's phone number follows a valid phone pattern, and that we don't add Orders where the customer's RequiredDate for delivery is before the actual OrderDate of the Order. Once defined in partial classes like below, these validation methods will automatically be enforced anytime we write code to update our data model objects in an application.

VB:

C#:

Adding a GetCustomer() Helper Method to our DataContext

Now that we have our data model classes created, and have applied validation rules to them, we can query and interact with the data. We can do this by writing LINQ expressions against our data model classes to query the database and populate them (I cover how to-do this in my Part 3: Querying our Database LINQ to SQL tutorial). Alternatively we could map SPROCs to our DataContext and use them to populate the data model classes (I cover how to-do this in my Part 6: Retrieving Data using Stored Procedures LINQ to SQL tutorial).

When building a LINQ to SQL data layer you'll usually want to encapsulate common LINQ queries (or SPROC invocations) into helper methods that you add to your DataContext class. We can do this by adding a partial class to our project. For example, we could add a helper method called "GetCustomer()" that enables us to lookup and retrieve a Customer object from the database based on their CustomerID value:

VB:

C#:

Step 2: Using our Data Access Layer (still without SPROCs)

We now have a data access layer that encapsulates our data model, integrates business validation rules, and enables us to query, update, insert, and delete the data.

Let's look at a simple scenario using it where we retrieve an existing customer object, update the customer's ContactName and Phone Number, and then create a new Order object and associate it with them. We can write the below code to do all of this within a single transaction. LINQ to SQL will ensure that our business logic validation rules are clean before saving anything in the database:

VB:

C#:

LINQ to SQL monitors the modifications we make to the objects we retrieve from the DataContext, and keeps track of all of the objects we add into it. When we call DataContext.SubmitChanges() at the end, LINQ to SQL will check that our business logic rules are valid, and if so automatically generate the appropriate dynamic SQL to update our Customer record above, and insert a new record into the Orders table.

Hang on a second - I thought this post was about using SPROCs???

If you are still reading this, you might be feeling confused about where SPROCs fit into this post. Why did I show you above how to write code that works with our data model objects, and then causes dynamic SQL to run? Why haven't I showed you how to call a SPROC for doing inserts/updates/deletes instead yet?

The reason is that the programming model in LINQ to SQL for working with data model objects backed by SPROCs is the same as those updated via dynamic SQL. The way you add data model validation logic is exactly the same (so all the validation rules on our data model classes above still apply when we use SPROCs). The code snippet above where we use our data access layer to retrieve a customer, update it, and then add a new order associated with it is also exactly the same regardless of whether we are using dynamic SQL for updates, or whether we have configured our data model classes to use SPROCs instead.

This programming model symmetry is powerful both in that you don't have to learn two ways of doing things, and also because it means that you don't have to decide up front at the beginning of your project whether you are going to use SPROCs or not. You can start off using the dynamic SQL support provided by the LINQ to SQL ORM for all queries, inserts, updates and deletes. You can then add your business and validation rules to your model. And then later you can optionally update your data mapping model to use SPROCs - or not if you decide you don't want to. The code and tests you write against your data model classes can stay the same regardless of whether you use dynamic SQL or SPROCs.

We'll now spend the rest of this blog post demonstrating how we can update the data model we've built to use SPROCs for updates/inserts/deletes - while still using the same validation rules, and working with the same code snippets above.

How to Use SPROCs for Insert/Update/Delete Scenarios

We can modify the data access layer we've been building to use SPROCs to handle updates, instead of dynamic SQL, in one of two ways:

1) By using the LINQ to SQL designer to graphically configure SPROCs to execute in response to Insert/Update/Delete operations on our data model classes.

or:

2) By adding a NorthwindDataContext partial class in our project, and then by implementing the appropriate Insert/Update/Delete partial methods provided on it (for example: InsertOrder, UpdateOrder, DeleteOrder) that will be called when we insert/update/delete data model objects. These partial methods will be passed the data model instances we want to update, and we can then execute whatever SPROC or SQL code we want to save it within the database.

When we use approach #1 (the LINQ to SQL designer) to graphically configure SPROCs to call, it is under the covers generating the same code (in a partial class it creates) that you'd write when using approach #2. In general I'd recommend using the LINQ to SQL designer to configure the SPROCs for the 90% case - and then in more advanced scenarios go in and custom tweak the SPROC invocation code it generates if you need to.

Step 3: Doing Order Inserts with a SPROC

We'll begin switching our data model to use SPROCs by starting with the Order object.

We'll first go to the Visual Studio "Server Explorer" window, expand into the "Stored Procedures" node of our database, and then right-click and choose "Add New Stored Procedure":

We'll then create a new SPROC that we'll call "InsertOrder" that inserts a new order record into our Orders table:

Notice above how the SPROC defines the "OrderID" parameter as an output param. This is because the OrderID column in the database is an identity column that is set to auto-increment each time a new record is added. The caller of the SPROC will pass in NULL as a value when calling it - and the SPROC then passes back the newly created OrderID value as the output value (by calling the SCOPE_IDENTITY() function at the end of the SPROC).

After creating the SPROC we'll then open up the LINQ to SQL ORM designer for our data access layer. Like I discussed in my last blog post in this series (Part 6: Retrieving Data Using Stored Procedures), we can drag/drop SPROCs from the server-explorer onto the method pane of our DataContext designer. We'll want to-do this with our newly created InsertOrder SPROC:

Our last step will be to tell our data access layer to use the InsertOrder SPROC when inserting new Order objects into the database. We can do that by selecting the "Order" class in the LINQ to SQL ORM designer, and then by going to the property grid and clicking the "..." button to override how Insert operations happen for it:

Clicking the "..." button will bring up a dialog that allows us to customize how insert operations happen:

Notice above how the default mode ("Use Runtime") is to have LINQ to SQL calculate and execute dynamic SQL to handle the insert operations. We can change that by selecting the "Customize" radio button and then pick our InsertOrder SPROC from the list of available SPROCs:

The LINQ to SQL designer will populate a parameter list for the SPROC we picked, and enable us to map properties on our Order class to parameters of our InsertOrder SPROC. By default it is smart and tries to "best match" them based on name. You can go in and override them if you want.

Once we click "ok" on the dialog, we are done. Now whenever a new Order is added to our DataContext and the SubmitChanges() method is invoked, our InsertOrder SPROC will be used instead of executing dynamic SQL.

Important: Even though we are now using a SPROC for persistence, the custom Order "OnValidate()" partial method we created earlier (in step 1 of this blog post) to encapsulate Order validation rules still executes before any changes are saved or the SPROC is invoked. This means we have a clean way to encapsulate business and validation rules in our data models, and can re-use them regardless of whether dynamic SQL or SPROCs are used.

Step 4: Doing Customer Updates with a SPROC

Now let's modify our Customer object to handle updates using a SPROC.

We'll start by creating a new "UpdateCustomer" SPROC like below:

Notice above how in addition to passing in the @CustomerID parameter, we are also passing in a @Original_CustomerID parameter. The CustomerID column in the Customers table is not an auto-increment identity column, and it can be modified as part of an update of the Customer object. Consequently we need to be able to provide the SPROC with both the original CustomerID and the new CustomerID in order to update the record. We'll look at how we map this using the LINQ to SQL designer shortly.

You'll notice above how I'm also passing in a @Version parameter (which is a timestamp) to the SPROC. This is a new column I've added to the Northwind Customers table to help handle optimistic concurrency. I will cover optimistic concurrency in much more depth in a later blog post in this LINQ to SQL series - but the short summary is that LINQ to SQL fully supports optimistic concurrency, and enables you to use either a version timestamp or to supply both original/new values to your SPROCs to detect if changes have been made by another user since you last refreshed your data objects. For this sample I'm using a timestamp since it makes the code much cleaner.

Once we've created our SPROC, we can drag/drop it onto the LINQ to SQL designer to add it as a method on our DataContext. We can then select the Customer class in the ORM designer and click the "..." button to override the Customer object's Update behavior in the property grid:

We'll select the "Customize" radio button and pick our UpdateCustomer SPROC to use:

When mapping our Customer object's properties to the SPROC parameters, you'll notice that we'll want to be deliberate about whether we supply the "current" property value on the data object, or the original value that was in the database when the object was first retrieved. For example, we'll want to make sure we map the "current" value of the Customer.CustomerID property for the @CustomerID SPROC parameter, and that we map the original value for the @original_customerID SPROC parameter.

Once we click "ok" on the dialog, we are done. Now whenever a new Customer is updated and the SubmitChanges() method is invoked, our UpdateCustomer SPROC will be used instead of executing dynamic SQL.

Important: Even though we are now using a SPROC for persistence, the Customer "OnPhoneChanging()" partial method we created earlier (in step 1 of this blog post) to encapsulate Phone Number validation rules still executes before any changes are saved or the SPROC is invoked. We have a clean way to encapsulate business and validation rules in our data models, and can re-use them regardless of whether dynamic SQL or SPROCs are used.

Step 5: Using our Data Access Layer Again (this time with SPROCs)

Once we've updated our data layer to use SPROCs instead of dynamic SQL for persistence, we can re-run the exact same code we wrote in Step 2 earlier against our data model classes:

Now the updates for the Customer object, and the insert for the Order object, are executing via SPROCs instead of dynamic SQL. The validation logic we defined still executes just like before, though, and the data access code we write to use the data model classes is exactly the same.

Some Advanced Notes When Using SPROCs

A few quick notes that you might find useful for more advanced SPROC scenarios with LINQ to SQL:

Usage of SPROC Output Params:

In the Insert scenario (Step 3) above I showed how we could return back the new OrderID value (which is an auto-increment identity column in the Orders table) using an output parameter of the SPROC. You aren't limited to only returning back identity column values when using SPROCs with LINQ to SQL - in fact you can update and return back output values for any parameter of your SPROC. You can use this approach both for Insert and Update scenarios. LINQ to SQL will then take the return value and use it to update the property value of your data model object without you having to-do any second queries against the database to refresh/populate them.

What Happens if the SPROC Throws an Error?

If a SPROC raises an error when doing an Insert/Update/Delete operation, LINQ to SQL will automatically cancel and rollback the transaction of all changes associated with the current SubmitChanges() call on the DataContext. This ensures that your data is always kept in a clean, consistent state.

Can you write code instead of using the ORM designer to call SPROCs?

As I mentioned earlier in this post, you can use either the LINQ to SQL ORM designer to map your insert/update/delete operations to SPROCs, or you can add partial methods on your DataContext class and programmatically invoke them yourself. Here is an example of the explicit code you could write in a partial class for the NorthwindDataContext to override the UpdateCustomer behavior to call a SPROC:

The code above was actually what was generated by the LINQ to SQL ORM designer when we used the designer to map the SPROC and then associate the update behavior of the Customer object with it. You can use it as a starting point and then add any additional logic you want to it to make it more advanced (for example: use the return value of the SPROC to raise custom exceptions for error conditions, optimistic concurrency, etc).

Summary

LINQ to SQL is a very flexible ORM. It enables you to write clean object-oriented code to retrieve, update and insert data.

Best of all - it enables you to cleanly design data model classes independent of how they are persisted and loaded from a database. You can use the built-in ORM engine to efficiently retrieve and update data in the database using dynamic SQL. Or alternatively you can configure your data layer to use SPROCs. The nice thing is that your code consuming the data layer, and all of the business logic validation rules you annotate it with, can be the same regardless of which persistence approach you use.

In future blog posts in this series I'll cover some remaining LINQ to SQL concepts including: Single Table Inheritance, Deferred/Eager Loading, Optimistic Concurrency, and handling Multi-Tier scenarios. I'm on vacation next week, and so will hopefully have some free time to get a few of them written then.

Wednesday, October 28, 2009

LINQ to SQL Part 6 - Retrieving Data Using Stored Procedures

LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

Below are the first five parts of my LINQ to SQL series:

In these previous LINQ to SQL blog posts I demonstrated how you could use LINQ query expressions to programmatically retrieve data from a database.

In today's blog post I'll cover how you can also use database stored procedures (SPROCs) and user defined functions (UDFs) with your LINQ to SQL data model. Today's blog post will specifically cover how to call SPROCs to query and retrieve data from the database. In my next blog post in this series I'll then show how you can optionally also use SPROCs to update/insert/delete data from the database.

To SPROC or not to SPROC? That is the question....

The question of whether to use Dynamic SQL generated by an ORM or instead use Stored Procedures when building a data layer is a topic that generates endless (very passionate) debate amongst developers, architects and DBAs. A lot of people much smarter than me have written on this topic, so I won't rehash the arguments for and against each side here.

The LINQ to SQL ORM that ships in .NET 3.5 is pretty flexible, and can be used to create data model classes whose object model can be independent of the underlying database schema, and which can encapsulate business logic and validation rules that work regardless of whether the data model is populated/persisted via dynamic SQL or via SPROCs.

In my LINQ to SQL Part 3: Querying our Database post I discussed how you can write LINQ query expressions against a LINQ to SQL data model using code like below:

When you write LINQ query expressions like this the LINQ to SQL ORM will execute the necessary dynamic SQL for you to retrieve Product objects that matches your query.

As you'll learn in this post, you can also optionally map SPROCs in the database to your LINQ to SQL DataContext class, which allows you to alternatively retrieve the same Product objects by calling a stored procedure instead:

This ability to use both dynamic SQL and SPROCs with a clean data model layer is pretty powerful, and provides a great deal of flexibility when working on projects.

The Steps to Map and Call a SPROC using LINQ to SQL

In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer to create a LINQ to SQL class model like below:

Notice above how there are two panes on the LINQ to SQL ORM designer surface. The left pane enables us to define data model classes that map to our database. The right method pane allows us to optionally map SPROCs (and UDFs) to our LINQ to SQL DataContext object, which we can then use in-place of dynamic SQL to populate the data model objects.

How to Map a SPROC to a LINQ to SQL DataContext

To map SPROCs to our DataContext class, let's first go to the VS 2008 Server Explorer window and look at the SPROCs within our database:

We can double click any of the SPROCs above to open and edit them. For example, below is the "CustOrderHist" SPROC in Northwind:

To map the above SPROC to our LINQ to SQL DataContext, we can drag/drop it from the Server Explorer onto our LINQ to SQL ORM designer. This will automatically create a new method on our LINQ to SQL DataContext class like below:

By default the method name created on the DataContext class will be the same as the SPROC name, and the return type of the method will be an automatically created type that follows the "[SprocName]Result" naming pattern. For example: the SPROC above would return a sequence of "CustOrderHistResult" objects. We could optionally change the name of the method by selecting it in the designer and then use the property grid to rename it.

How to Call our Newly Mapped SPROC

Once we've done the steps above to map a SPROC onto our DataContext class, it is easy to use it to programmatically retrieve data. All we need to-do is call the new method we mapped on our DataContext class to get back a sequence of strongly typed results from the SPROC:

Calling the SPROC in VB:

Calling the Sproc in C#:

In addition to programming looping over the result like in the code samples above, I could also obviously bind the results to any UI control to display them. For example, the below code databinds the result of our SPROC to a control:

Which then displays the product history of our customer on a page like so:

Mapping the Return Type of SPROC Methods to Data Model Classes

In the "CustOrderHist" SPROC example above the stored procedure returned a sequence of product history results containing two columns of data: the ProductName of the product, and the Total Number of orders the customer has made for that product. The LINQ to SQL designer automatically defined a new "CustOrderHistResult" class to represent this result.

We could alternatively choose to have the return result of a SPROC map to an existing data model class we have already defined in the LINQ to SQL designer (for example: an existing Product or Order entity class).

For example, assume we have a "GetProductsByCategory" SPROC in our database that returns product information like so:

Like before we can create a "GetProductsByCategory" method on our DataContext that calls this SPROC by dragging it onto our LINQ to SQL designer. Rather than just dropping the SPROC anywhere on the designer, though, we'll instead drop the SPROC on top of the existing "Product" class in our data model designer:

This gesture of dropping the SPROC onto the Product class tells the LINQ to SQL designer to have the "GetProductsByCategory" method return a sequence of "Product" objects as a return result:

One of the cool things about having our SPROC return "Product" objects like above is that LINQ to SQL will automatically track the changes made to the returned Product objects just like it would Product objects returned via LINQ queries. When we call the "SubmitChanges()" method on our DataContext, the changes we have made to these objects will automatically be saved back to the database.

For example, we could write the code below to retrieve (using a SPROC) and change the price of all products within a specific Category to be 90% of their current value:

When we call SubmitChanges() at the end it will transactionally update all of the product prices. To understand more about how change tracking and the SubmitChanges() method work, as well as about how Validation Business Logic can be added to data model entities, please read my LINQ to SQL Part 4: Updating our Database tutorial.

In my next blog post in this LINQ to SQL series I'll also cover how you can replace the dynamic insert/update/delete SQL generated by the ORM with custom SPROCs that handle the database updates instead. The nice thing is that the code above wouldn't change at all if I configured my DataContext to use SPROCs for updates - it would purely be a mapping layer change and the code written against my data model would be oblivious to it.

Handling SPROC Output Parameters

LINQ to SQL maps "out" parameters in SPROCs as reference parameters (ref keyword), and for value types declares the parameter as nullable.

For example, consider the below "GetCustomerDetails" SPROC which takes a CustomerID as an input parameter, and which returns the company name as an output parameter in addition to its order history as a query result:

If we drag the above SPROC onto our "Order" class in the LINQ to SQL designer, we could then write the below code to call it:

VB:

C#:

Notice in the code above how the SPROC helper method returns back a sequence of Order objects - but also then returns the CompanyName as an output parameter to the helper method.

Handling Multiple Result Shapes from SPROCs

When a stored procedure can return multiple result shapes, the return type of the SPROC method on the DataContext cannot be strongly typed to a single class shape. For example, consider the SPROC below which returns either a product result or an order result depending on the input parameter:

LINQ to SQL supports the ability to create SPROC helper methods that can return either a Product or Order shape by adding a partial "NorthwindDataContext" class to the project that defines a method (which in this case we'll call "VariablesShapeSample") that invokes the SPROC and returns an IMultipleResult object like so:

VB:

C#:

Once this method is added into our project we can then call it and convert the result to be either a Product or Order sequence when we are using it:

VB:

C#:

Supporting User Defined Functions (UDFs)

In addition to SPROCS, LINQ to SQL also supports both scalar-valued and table-valued user defined functions (UDFs), as well as the in-line counterpart to both. Once added to your DataContext as a method, you can use these UDF functions within your LINQ queries.

For example, consider a simple scalar user defined function called "MyUpperFunction":

We can drag/drop it from the Visual Studio Server Explorer onto our LINQ to SQL Designer to add it as a method on our DataContext:

We can then use this UDF function inline within our LINQ expressions when writing queries against our LINQ to SQL data model (notice it is being used within the "where" clause below):

VB:

C#:

Now you can see how LINQ to SQL transforms the above expression queries into raw SQL that execute the UDF inside the database at runtime:

Summary

LINQ to SQL supports the ability to call Stored Procedures and UDFs within the database and nicely integrate them into our data model. In this blog post I demonstrated how you can use SPROCs to easily retrieve data and populate our data model classes. In my next blog post in this series I'll cover how you can also use SPROCs to override the update/insert/delete logic when you SubmitChanges() on your DataContext to persist back to the database.