LINQ to SQL (Part 9 - Using a Custom LINQ Expression with the <asp:LinqDatasource> control)
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 eight 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
- Part 8: Executing Custom SQL Expressions
In Part 5 of the series I introduced the new <asp:LinqDataSource> control in .NET 3.5 and talked about how you can use it to easily bind ASP.NET UI controls to LINQ to SQL data models. I also demonstrated how to use it a little more in a follow-up post I did that discusses the new <asp:ListView> control (Part 1 - Building a Product Listing Page with Clean CSS UI).
In both of these articles the queries I performed were relatively straight-forward (the where clause worked against a single table of data). In today's blog post I'll demonstrate how to use the full query expressiveness of LINQ with the LinqDataSource control, and show how you can use any LINQ to SQL query expression with it.
Quick Recap: <asp:LinqDataSource> with a Declarative Where Statement
In these two posts I demonstrated how you can use the built-in filter capabilities of the LinqDataSource control to declaratively express a filter statement on a LINQ to SQL data model.
For example, assuming we had created a LINQ to SQL data model for the Northwind database (which I covered how to-do in Part 2 of this series), we could declare a <asp:LinqDataSource> control on the page with a declarative <where> filter that returns back only those products in a specific category (specified via a querystring "categoryid" value):
We could then point a <asp:gridview> control at the datasource and enable paging, editing, and sorting on it:
When we run the above page we'll then have a GridView with automatic sorting, paging, and editing support against our Product data model:
Using declarative <where> parameters like above works well for many common scenarios. But what happens if you want the Product filtering to be richer or more complex? For example, what if we only wanted to display products made by suppliers based in a dynamic set of countries?
Using the <asp:LinqDataSource> Selecting Event
To handle custom query scenarios you can implement an event handler to handle the "Selecting" event on the <asp:LinqDataSource> control. Within this event handler you can write whatever code you want to retrieve a data model result. You could do this with a LINQ to SQL query expression, or call a Stored Procedure or use a Custom SQL Expression to retrieve the LINQ to SQL data model. Once you retrieve a sequence of data, all you need to-do is to assign it to the "Result" property on the LinqDataSourceSelectEventArgs object. The <asp:LinqDataSource> will then use this sequence as its data to work with.
For example, below is a LINQ to SQL query expression that retrieves only products from suppliers based in a specific set of countries:
VB:
C#:
Note: you do not need to write your query expression in-line within the event handler. A cleaner approach would be to encapsulate it within a helper method that you just call from the event handler. I show how to create one of these helper methods in the beginning of my Part 8 blog post (using a GetProductsByCategory helper method).
Now when we run our page using the custom Selecting event handler, we'll only see those products whose suppliers are located in our array of countries:
One of the really cool things to notice above is that paging and sorting still work with our GridView - even though we are using a custom Selecting event to retrieve the data. This paging and sorting logic happens in the database - which means we are only pulling back the 10 products from the database that we need to display for the current page index in the GridView (making it super efficient).
You might ask yourself - how is it possible that we get efficient paging and sorting support even when using a custom selecting event? The reason is because LINQ uses a deferred execution model - which means that the query doesn't actually execute until you try and iterate over the results. One of the benefits of this deferred execution model is that it enables you to nicely compose queries out of other queries, and effectively "add-on" behavior to them. You can learn more about this in my LINQ to SQL Part 3 blog post.
In our "Selecting" event handler above we are declaring a custom LINQ query we want to execute and are then assigning it to the "e.Result" property. We haven't actually executed it yet though (since we didn't try and iterate through the results or call ToArray() or ToList() on it). The LINQDataSource is therefore able to automatically append on a Skip() and Take() operator to the query, as well as apply an "orderby" expression to it -- all of these values being automatically calculated from the page index and sort preference of the GridView. Only then does the LINQDataSource execute the LINQ expression and retrieve the data. LINQ to SQL then takes care of making sure that the sort and page logic is handled in the database - and that only the 10 product rows required are returned from it.
Notice below how we can also still use the GridView to edit and delete data, even when using a custom LinqDataSource "Selecting" event:
This editing/deleting support will work as long as our Selecting event assigns a Result query whose result sequence is of regular entity objects (for example: a sequence of type Product, Supplier, Category, Order, etc). The LINQDataSource can then automatically handle cases where UI controls perform updates against them.
To learn more about how updates work with LINQ to SQL, please read Part 4 of this series. Then read Part 5 of the series to see Updates in action with the LinqDataSource.
Performing Custom Query Projections with the Selecting Event
One of the powerful features of LINQ is its ability to custom "shape" or "project" data. You can do this in a LINQ to SQL expression to indicate that you want to retrieve only a subset of values from an entity, and/or to dynamically compute new values on the fly using custom expressions that you define. You can learn more about how these LINQ query projection/shaping capabilities in Part 3 of this series.
For example, we could modify our "Selecting" event handler to populate a GridView to display a custom set of Product information. In this Grid we'll want to display the ProductID, Product Name, Product UnitPrice, the Number of Orders made for this Product, and the total Revenue collected from orders placed for the Product. We can dynamically compute these last two values using a LINQ expression like below:
VB:
C#:
Note: The Sum method used in the Revenue statement above is an example of an Extension Method. The function it takes is an example of a Lambda expression. The resulting type created by the LINQ query expression is an anonymous type - since its shape is inferred from the query expression. Extension Methods, Lambda Expressions, and Anonymous Types are all new language features of VB and C# in VS 2008.
The result of our custom LINQ expression when bound to the GridView will be UI like below:
Note that paging and sorting still work above with our GridView - even though we are using a custom LINQ shape/projection for the data.
One feature that will not work with custom shapes/projections, though, is inline editing support. This is because we are doing a custom projection in our Selecting event, and so the LinqDataSource has no way to safely know how to update an underlying entity object. If we want to add editing support to the GridView with a custom shaped type, we'd want to either move to using an ObjectDataSource control (where we could supply a custom Update method method to handle the updates), or have the user navigate to a new page when performing updates - and display a DetailsView or FormView control that was bound to a Product entity for editing (and not try and do inline editing with the grid).
Summary
You can easily perform common query operations against a LINQ to SQL data model using the built-in declarative filtering support of the LinqDataSource.
To enable more advanced or custom filtering expressions, you can take advantage of the LINQDataSource's Selecting event. This will enable you to perform any logic you want to retrieve and filter LINQ to SQL data. You can call methods to retrieve this data, use LINQ Query Expressions, call a Stored Procedures, or invoke a Custom SQL Expression to-do this.
Hope this helps,
Scott