Using the LinqDataSource: A practical look

One of the new controls coming in the .NET 3.5/Visual Studio 2008 releases later this year is the LinqDataSource. Much has already been written about this new control, and by most accounts this is going to be a very cool addition to the ASP.NET framework. I don't think anyone could aspire to write more about using the LinqDataSource than Scott Guthrie did on his blog, so if you're looking for in-depth LinqDataSource 101 start there. Instead, today I'll present the answers to a few of the questions I had when I first started using the control and condense some of the information you can find across the 'Nets into one (hopefully) easy to read blog post.

What is the LinqDataSource control?

As fellow blogger and DeKlarit cheif software architect Andres Aguiar put it, the LinqDataSource control really should be called the LinqToSqlDataSource control. Among the new features in Visual Studio 2008 is an integrated O/RM (object-relational mapping) tool called LinqToSql. If you are familiar with O/RM tools, LinqToSql is simply Microsoft's flavor of O/RM that is based heavily on the new Linq data querying language. If you're not familiar, the easiest way to describe LinqToSql is a tool that automatically creates objects (or classes) in your code based on the database you point it at. This LinqToSql data mapping is central to the LinqDataSource's functionality, thus the suggested "LinqToSqlDataSource" rename.

Beyond that nuance, the LinqDataSource is like most other data source controls. You can easily point data bound controls (that support declarative binding) to a LinqDataSource via their DataSourceID property and they'll bind to the data just like they do with SqlDataSource or ObjectDataSource controls. The real power of the LinqDataSource lies in the LinqToSql data mapping (supplied via the ContextTypeName property) that "knows" a lot about your data source (unlike the Sql and Object data source controls). That makes the data source control significantly smarter than its predecessors, enabling paging, sorting, and data editing "out of the box" without writing any extra code.

How do I use the LinqDataSource control?

I should note at this point that the LinqToSql tools are new in Visual Studio 2008, so if you want to use the new data source control you'll need the beta for now and then an upgrade when VS 2008 ships.

To begin using the LinqDataSource, you need to start by creating a LinqToSql data context. The data context lives in your application's App_Code directory and usually has the ".dbml" file extension. You can manually create a data context by adding a new "LINQ to SQL Class" to your application and then drag and drop tables from the Server Explorer onto the context diagram. For a complete review of that process, Mr.Guthrie has once again provided a thorough blog post.

Once your data context is built, drag and drop a LinqDataSource on to your page from the Visual Studio Toolbox. Configure your LinqDataSource to point to your data context and then use the LinqDataSource SmartTag to define the data you want to select. A simple LinqDataSource might look something like this:

<asp:LinqDataSource ID="LinqDataSource1" runat="server" 
   ContextTypeName="ForumsDataContext"
   TableName="Posts"
   Select="new (postContent, dateCreated, fd_thread, aspnet_User)"
   OrderBy="dateCreated">
</asp:LinqDataSource>

You can see that I've specified a data context called "ForumDataContext" (this code is based off of the data model used in my "Build your own ASP.NET Forums in 60 minutes" web cast, if you're interested in the meaning of the fields I'm referencing). I've set the "TableName" property to the name of a table within my context, specifically "Posts". I then set a Select statement that defines the new objects with fields from my database that I want to display in my data bound control (which in this case will be a RadGrid from Telerik).

When this code runs, the LinqDataSource automatically creates new dynamic classes with properties based on my select statement and then binds them to my data control. The "auto" classes are named "DynamicClassX", where "X" is a number starting at 1 and incrementing as necessary to handle multiple LinqDataSources. You should never need to interact with these dynamic classes, but it is good to know how .NET handles Linq selects when you're debugging.

Finally, I can use my configured LinqDataSource by setting my Grid's DataSourceID property, like this:

<radG:RadGrid ID="RadGrid1" runat="server"
   AllowSorting="True"
   AllowPaging="True"
   PageSize="5" Width="480px"
   DataSourceID="LinqDataSource1"
   AutoGenerateColumns="true">
</radG:RadGrid>

Producing results like this:

How do I display values from related tables?

The last example looks good, but where did my "fd_thread" and "aspnet_User" values go? As it turns out, those "values" in my select statement represent objects (or in database terms, tables) related to the table "Posts". To display those values, I need to manually configure TemplateColumns in my Grid, like this:

<radG:GridTemplateColumn HeaderText="Thread" SortExpression="fd_thread.subject">
   <ItemTemplate>
      <asp:Label ID="Label3" runat="server" Text='<%# Eval("fd_thread.subject") %>'>
      </asp:Label>
   </ItemTemplate>
</radG:GridTemplateColumn>
<radG:GridTemplateColumn HeaderText="Username" SortExpression="aspnet_User.Username"> 
   <ItemTemplate> 
      <asp:Label ID="Label4" runat="server" Text='<%# Eval("aspnet_User.Username") %>'>
      </asp:Label>
   </ItemTemplate>
</radG:GridTemplateColumn>

By referencing the properties (in database terms, fields or columns) of these related objects, I can easily display the relational data in my Grid. Furthermore, any property in the related objects is available to me to display in my Grid at this point. I don't have to rewrite my query if I decide later that I want to display (let's say) the "LoweredUsername" field from the aspnet_User table. This flexibility is definitely one of the cooler aspects of LinqToSql and the LinqDataSource. In any event, the addition of my template columns enables me to display all of my data nicely in the Grid:

A0

Do I need to optimize my Linq queries?

Generally speaking, no. LinqToSql by default operates in a "lazy load" mode, which means data is only queried from your DB when it is actually used in your code. In most cases this is a good behavior that prevents your application from loading unnecessary data, but there are times when you don't want to use lazy loading. I'll examine some of the performance optimization techniques in future blog posts, but in the mean time you can check out a good series of posts on the topic created by C# MVP David Hayden.

For now, let's just look at the SQL statements generated by Linq to handle my select clause. Using SQL Server Profiler, we see that Linq generated and executed three SQL statements to load our grid on the first page load:

SELECT [t0].[postContent], [t0].[dateCreated], [t1].[threadId], [t1].[topicId],
   [t1].[subject], [t1].[dateCreated] AS [dateCreated2], [t2].[ApplicationId], 
   [t2].[UserId], [t2].[UserName], [t2].[LoweredUserName], 
   [t2].[MobileAlias], [t2].[IsAnonymous], [t2].[LastActivityDate] 
FROM [dbo].[fd_posts] AS [t0] 
   INNER JOIN [dbo].[fd_threads] AS [t1] ON [t1].[threadId] = [t0].[threadId] 
   INNER JOIN [dbo].[aspnet_Users] AS [t2] ON [t2].[UserId] = [t0].[userId] 
ORDER BY [t0].[dateCreated]

SELECT COUNT(*) AS [value] 
FROM [dbo].[fd_posts] AS [t0] 
   INNER JOIN [dbo].[fd_threads] AS [t1] ON [t1].[threadId] = [t0].[threadId] 
   INNER JOIN [dbo].[aspnet_Users] AS [t2] ON [t2].[UserId] = [t0].[userId]

SELECT TOP 5 [t0].[postContent], [t0].[dateCreated], [t1]. [threadId], [t1].[topicId], 
   [t1].[subject], [t1].[dateCreated] AS [dateCreated2], [t2].[ApplicationId], 
   [t2].[UserId], [t2].[UserName], [t2].[LoweredUserName], 
   [t2].[MobileAlias], [t2].[IsAnonymous], [t2].[LastActivityDate] 
FROM [dbo].[fd_posts] AS [t0] 
   INNER JOIN [dbo].[fd_threads] AS [t1] ON [t1].[threadId] = [t0].[threadId] 
   INNER JOIN [dbo].[aspnet_Users] AS [t2] ON [t2].[UserId] = [t0].[userId] 
ORDER BY [t0].[dateCreated]

Whenever we page our RadGrid- forwards or backwards- Linq only executes one query:

exec sp_executesql N'SELECT TOP 5 [t3].[postContent], [t3].[dateCreated], 
   [t3].[threadId], [t3].[topicId], [t3].[subject], [t3].[dateCreated2], 
   [t3].[ApplicationId],[t3].[UserId],[t3].[UserName], [t3].[LoweredUserName], 
   [t3].[MobileAlias], [t3].[IsAnonymous], [t3].[LastActivityDate] 
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[dateCreated]) AS [ROW_NUMBER], 
   [t0].[postContent], [t0].[dateCreated], [t1].[threadId], [t1].[topicId], 
   [t1].[subject], [t1].[dateCreated] AS [dateCreated2], [t2].[ApplicationId], 
   [t2].[UserId], [t2].[UserName], [t2].[LoweredUserName], [t2].[MobileAlias], 
   [t2].[IsAnonymous], [t2].[LastActivityDate] 
FROM [dbo].[fd_posts] AS [t0] 
   INNER JOIN [dbo].[fd_threads] AS [t1] ON [t1].[threadId] = [t0].[threadId] 
   INNER JOIN [dbo].[aspnet_Users] AS [t2] ON [t2].[UserId] = [t0].[userId] ) AS [t3] 
WHERE [t3].[ROW_NUMBER] > @p0 
ORDER BY [t3].[dateCreated]',N'@p0 int',@p0=5

We won't dive in to how these queries are generated or how to optimize them in this post, but the point is to see that at the end of the day LinqToSql is generating standard- and relatively efficient- SQL statements to pull your data out the database. In a future post, I'll show you how we can avoid executing unnecessary queries and avoid selecting unnecessary data.

Should I be impressed?

If you've ever spent much time creating data driven applications in ASP.NET, then you know how monotonous the process of creating data access code can become. Even if you use advanced data helper classes, you're still left dealing with ambiguous table and column names in your code. LinqToSql and the LinqDataSource bring a whole new level of productivity to Visual Studio and are sure to make data driven applications easier than ever to build. Sure O/RM is nothing new, but as with anything that gets direct integration with Visual Studio (eh..hem...unit testing) it makes the lives of many developers easier.

So yes, you should be impressed.