LINQ to SQL (Part 3 - Querying our Database)
Last month I started a blog post series covering LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapping) framework that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes. You can then use LINQ expressions to query the database with them, as well as update/insert/delete data from it.
Below are the first two parts of my LINQ to SQL series:
In today's blog post I'll be going into more detail on how to use the data model we created in the Part 2 post, and show how to use it to query data within an ASP.NET project.
Northwind Database Modeled using LINQ to SQL
In Part 2 of this series I walked through how to create a LINQ to SQL class model using the LINQ to SQL designer that is built-into VS 2008. Below is the class model that we created for the Northwind sample database:
Retrieving Products
Once we have defined our data model classes above, we can easily query and retrieve data from our database. LINQ to SQL enables you to do this by writing LINQ syntax queries against the NorthwindDataContext class that we created using the LINQ to SQL designer above.
For example, to retrieve and iterate over a sequence of Product objects I could write code like below:
In the query above I have used a "where" clause in my LINQ syntax query to only return those products within a specific category. I am using the CategoryID of the Product to perform the filter.
One of the nice things above LINQ to SQL is that I have a lot of flexibility in how I query my data, and I can take advantage of the associations I've setup when modeling my LINQ to SQL data classes to perform richer and more natural queries against the database. For example, I could modify the query to filter by the product's CategoryName instead of its CategoryID by writing my LINQ query like so:
Notice above how I'm using the "Category" property that is on each of the Product objects to filter by the CategoryName of the Category that the Product belongs to. This property was automatically created for us by LINQ to SQL because we modeled the Category and Product classes as having a many to one relationship with each other in the database.
For another simple example of using our data model's association relationships within queries, we could write the below LINQ query to retrieve only those products that have had 5 or more orders placed for them:
Notice above how we are using the "OrderDetails" collection that LINQ to SQL has created for us on each Product class (because of the 1 to many relationship we modeled in the LINQ to SQL designer).
Visualizing LINQ to SQL Queries in the Debugger
Object relational mappers like LINQ to SQL handle automatically creating and executing the appropriate SQL code for you when you perform a query or update against their object model.
One of the biggest concerns/fears that developers new to ORMs have is "but what SQL code is it actually executing?" One of the really nice things about LINQ to SQL is that it makes it super easy to see exactly what SQL code it is executing when you run your application within the debugger.
Starting with Beta2 of Visual Studio 2008 you can use a new LINQ to SQL visualizer plug-in to easily see (and test out) any LINQ to SQL query expression. Simply set a breakpoint and then hover over a LINQ to SQL query and click the magnify glass to pull up its expression visualizer within the debugger:
This will then bring up a dialog that shows you the exact SQL that LINQ to SQL will use when executing the query to retrieve the Product objects:
If you press the "Execute" button within this dialog it will allow you to evaluate the SQL directly within the debugger and see the exact data results returned from the database:
This obviously makes it super easy to see precisely what SQL query logic LINQ to SQL is doing for you. Note that you can optionally override the raw SQL that LINQ to SQL executes in cases where you want to change it - although in 98% of scenarios I think you'll find that the SQL code that LINQ to SQL executes is really, really good.
Databinding LINQ to SQL Queries to ASP.NET Controls
LINQ queries return results that implement the IEnumerable interface - which is also an interface that ASP.NET server controls support to databind object. What this means is that you can databind the results of any LINQ, LINQ to SQL, or LINQ to XML query to any ASP.NET control.
For example, we could declare an <asp:gridview> control in a .aspx page like so:
I could then databind the result of the LINQ to SQL query we wrote before to the GridView like so:
This will then generate a page that looks like below:
Shaping our Query Results
Right now when we are evaluating our product query, we are retrieving by default all of the column data needed to populate the Product entity classes.
For example, this query to retrieve products:
Results in all of this data being returned:
Often we only want to return a subset of the data about each product. We can use the new data shaping features that LINQ and the new C# and VB compilers support to indicate that we only want a subset of the data by modifying our LINQ to SQL query like so:
This will result in only this data subset being returned from our database (as seen via our debug visualizer):
What is cool about LINQ to SQL is that I can take full advantage of my data model class associations when shaping my data. This enables me to express really useful (and very efficient) data queries. For example, the below query retrieves the ID and Name from the Product entity, the total number of orders that have been made for the Product, and then sums up the total revenue value of each of the Product's orders:
The expression to the right of the "Revenue" property above is an example of using the "Sum" extension method provided by LINQ. It takes a Lambda expression that returns the value of each product order item as an argument.
LINQ to SQL is smart and is able to convert the above LINQ expression to the below SQL when it is evaluated (as seen via our debug visualizer):
The above SQL causes all of the NumOrders and Revenue value computations to be done inside the SQL server, and results in only the below data being retrieved from the database (making it really fast):
We can then databind the result sequence against our GridView control to generate pretty UI:
BTW - in case you were wondering, you do get full intellisense within VS 2008 when writing these types of LINQ shaping queries:
In the example above I'm declaring an anonymous type that uses object initialization to shape and define the result structure. What is really cool is that VS 2008 provides full intellisense, compilation checking, and refactoring support when working against these anonymous result sequences as well:
Paging our Query Results
One of the common needs in web scenarios is to be able to efficiently build data paging UI. LINQ provides built-in support for two extension methods that make this both easy and efficient - the Skip() and Take() methods.
We can use the Skip() and Take() methods below to indicate that we only want to return 10 product objects - starting at an initial product row that we specify as a parameter argument:
Note above how I did not add the Skip() and Take() operator on the initial products query declaration - but instead added it later to the query (when binding it to my GridView datasource). People often ask me "but doesn't this mean that the query first grabs all the data from the database and then does the paging in the middle tier (which is bad)?" No. The reason is that 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 across multiple code statements (which improves code readability). It also enables you to compose queries out of other queries - which enables some very flexible query composition and re-use scenarios.
Once I have the BindProduct() method defined above, I can write the code below in my page to retrieve the starting index from the querystring and cause the products to be paged and displayed in the gridview:
This will then give us a products page, filtered to list only those products with more than 5 orders, showing dynamically computed product data, and which is pageable via a querystring argument:
Note: When working against SQL 2005, LINQ to SQL will use the ROW_NUMBER() SQL function to perform all of the data paging logic in the database. This ensures that only the 10 rows of data we want in the current page view are returned from the database when we execute the above code:
This makes it efficient and easy to page over large data sequences.
Summary
Hopefully the above walkthrough provides a good overview of some of the cool data query opportunities that LINQ to SQL provides. To learn more about LINQ expressions and the new language syntax supported by the C# and VB compilers with VS 2008, please read these earlier posts of mine:
- Automatic Properties, Object Initializer and Collection Initializers
- Extension Methods
- Lambda Expressions
- Query Syntax
- Anonymous Types
In my next post in this LINQ to SQL series I'll cover how we can cleanly add validation logic to our data model classes, and demonstrate how we can use it to encapsulate business logic that executes every time we update, insert, or delete our data. I'll then cover more advanced lazy and eager loading query scenarios, how to use the new <asp:LINQDataSource> control to support declarative databinding of ASP.NET controls, optimistic concurrency error resolution, and more.
Hope this helps,
Scott