I’ve been recently using LinqToSQL with a current project and finally got down to business where I needed to work with fields from multiple tables. LinqToSQL is nice, but it does have it’s drawback that it maps objects directly to the schema. Fortunately, it maps their relationships too. Let’s avoid the discussion of LinqToSQL pros and cons for now and figure out handling multi-table fields.
Did I mention I’m using the LinqDataSource? Oops… that too. I’m not a fan of the built in sqldatasource and linqdatasource, but when you’re prototyping they’re a great way to get something up and running (save the business logic layer for when you actually HAVE logic). The catch is, they tend to do a bit more than what you want them to. This is the case I stumbled into.
Getting to fields from related tables requires that you understand the underlying LinqToSQL classes. Once you have those, it’s just a matter of typing in the <%# Eval("blah.blah”) %> statement.
An example of this has been done and written up nicely by Todd Anglin- Todd's blog
In my case I have a primary key, PackageTestSystemID, for a join table holding PKs to 2 other tables – PackageVersion and TestSystem. Displaying PKs is not user friendly, so I use the Eval statement to get the PackageName, Revision, and TestSystemSerial from the related tables. Shown below.
<telerik:GridTemplateColumn HeaderText="Package" UniqueName="Package"
<asp:Label id="PackageLabel" runat="server"
Text='<%# Eval("PackageVersion.PackageName") %>'>
<telerik:GridTemplateColumn HeaderText="Revision" UniqueName="Revision"
<asp:Label id="RevisionLabel" runat="server"
Text='<%# Eval("PackageVersion.Revision") %>'>
<telerik:GridTemplateColumn HeaderText="Tester" UniqueName="Tester"
<asp:Label id="TesterLabel" runat="server"
Text='<%# Eval("TestSystem.TestSystemSerial") %>'>
This is done in Telerik’s RadGrid control. For those who are working with the standard gridview, it’s a snap too. Scott Guthrie shows how- Scott’s blog
The catch is, I got all of this to work with the LinqDataSource, TestPackageLDS, shown below-
<asp:LinqDataSource ID="TestPackagesLDS" runat="server"
EnableDelete="True" EnableInsert="True" EnableUpdate="True">
Which produces the Grid-
However, I didn’t start that way. Since I was going to just display this information. I initially had my TestPackageLDS with insert, update, delete Not enabled. Without these enabled, I’m guessing the TestPackageLDS decided to not get them and you see the following grid and get to wonder… where’d it all go.
Enabling just one of the update, insert, delete options and voila, problem fixed. I have not attempted this with the Gridview, but I suspect the issue lies in the LinqDataSource and the results will be the same. The LinqDataSource (and SqlDataSource) are useful tools for prototyping and simple applications. You could say they let you cheat, and you know cheaters never prosper. In this case, they just had me work a little bit to pay my dues. Have fun, I hope this helps.