Todd Anglin's Code Campground

A casual look at the world .NET coding

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:

linqDbStep1

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:

linqDbStep2 

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.

Comments

Dave said:

One thing I'm not following with LINQ - Good practice dictates that you pretty much write your code as Database agnostic as possible.  Sure for a one off RAD app - you don't care, but if you expect to grow or resell your app then you were supposed to be database neutral.  Without seeing good LINQ/Oracle code support - is choosing to go with LINQ that great of an idea at this point? I mean I love SQL Server, but reality tells me that the big boys all eventually end up on Oracle (IMHO anyway).

I would appreciate comments about this. I can't help but feel that LINQ is just some Syntactic Sugar that large volume apps will eventually have to do away with.

# September 19, 2007 10:43 AM

tanglin05 said:

Dave-

You may find the following MSDN forum thread interesting:

forums.microsoft.com/.../ShowPost.aspx

On it, several Microsoft insiders say that LinqToSql will eventually support the other major DBMSs, just not at launch. So for now, LinqToSql (and in turn the LinqDataSource) are mostly for SQL Server shops. Eventually it will be easily consumed in all DB shops.

You can use the LinqDataSource without a LinqToSql conext, too (for querying business objects). Check out this post for details:

weblogs.asp.net/.../digging-into-linqdatasource.aspx

Finally, I must disagree with your statement that all big shops end up using Oracle. Many big IT shops do use Oracle, but you'll usually find SQL Server there, too. Beyond that, some of the largest high volume apps in the world use SQL Server, so it is a perfectly capable player. Just depends on your architecture and needs.

Hope that helps.

-Todd

# September 19, 2007 11:45 AM

GSquared said:

This sounds great, I had no idea what LinQ was, thanks Todd!

# October 29, 2007 10:13 AM

Chyld Medford said:

Very informative article, thanks!

# November 19, 2007 6:12 PM

Adron said:

ACtually I'd bet on stating, that if you are writing SQL code agnostic database you are losing major advantages to using a database in particular.

i.e.

If you don't use T-SQL you'll lose a lot of the advantages of SQL Server, if you don't use PL-SQL you'll lose a bunch of Oracle Database enahancements.  So toss that, "it's gotta work on all databases mess" and just worry about your particular database.

# January 2, 2008 8:33 PM

tanglin05 said:

@Adron-

Well...yes and no. Yes you are writing database agnostic code. No, you -do- get to take advantage of database specific features. How? That's the job of the O/RM tool you use. If you use a provider-based tool that makes you write in an intermediate language (like LINQ), that tool should then generate the SQL best suited for the DB it's talking to.

Why is this helpful? Application maintenance. Before joining Telerik, I was working on huge Fortune 200 financial services software project to "upgrade" a portfolio management app from Informix to Oracle. The effort was going to require hundreds (if not thousands) of hours, all of which could be avoided in the future if the app was upgraded to use a O/RM layer tool. These tools really do have real world value. :)

-Todd

# January 3, 2008 4:54 PM

John Summers said:

Good stuff... I just ran into this. I'm using LinqtoSQL and the linqdatasource. I also am using the latest Telerik RadGrid. This helped me figure out that my GridTemplateColumn was the way to go. The problem I discovered is the RadGrid would not display fields from related tables... until I enabled one or more update, delete, insert from the linqdatasource.

I don't know if this is due to the linqdatasource deciding not to retrieve the data or the grid dropping the ball.

As for value... I don't have lots of experience with ORM tools, but I do know the "old way" of generating the model classes and sql access code by hand. Tedious to implement and maintain, but you can roll out data model classes exactly as you want. The advantage of LinqToSQL is in an environment where the database and applications are changing often and evolving, in this situation, it's damn fast and typo free making changes.

thx.

# June 20, 2008 2:57 PM

Tami Shaw said:

Thanks for the info on displaying values from related tables. What if the relationship in the dbml is a one to many. Many orders to one customer...how do you get that to render using LinqDataSource?

thx for any input!

# July 8, 2009 8:21 PM

Matt said:

Good post... how about whereparameters using related table data...

for example, I want to pull all <entity records>

WHERE

entityrecord.[CompanyID]=@companyID

but also WHERE

<relatedtable.statusdescription> != @statusdescription

How would I access this via linqdatasource?

I know in linq, the statement would work

var record = from entitytable in datacontext.companies

            join status on entitytable.StatusID = status.statusID

            where entitytable.companyId == @companyID && status.description != @statusdescription

            select new

            {

               entitytable.field, entitytable.field2, status.description,

             }

# November 4, 2009 3:06 PM