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:

• Part 1: Introduction to LINQ to SQL
• Part 2: Defining our Data Model Classes
• Part 3: Querying our Database
• Part 4: Updating our Database
• Part 5: Binding UI using the ASP:LinqDataSource Control
• Part 6: Retrieving Data Using Stored Procedures
• Part 7: Updating our Database using Stored Procedures

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.
Hope this helps, Scott

Reference and Resource

https://weblogs.asp.net/scottgu/linq-to-sql-part-8-executing-custom-sql-expressions