Get value from relationed database

Question

Hi SO, I have a question:

I have two MSSQL tables, items and states, that are linked together via a stateid:

    STATES                  ITEMS
-------------    ---------------------------
| id | name |    | id | name | ... | stateid
  V                                     ^
  |_____________________________________|

So Items.StateID is related to State.ID. In my current situation I have a GridView that is databound to a LinqDataSource, which references the Items table. The GridView has two columns, one is Name and the other is StateID. I want to be able to pull the name of the state associated with the StateID out from the state table so that is displayed instead of the StateID.

Thanks in advanced!

EDIT

Here is the grid/datasource:

<asp:LinqDataSource ID="ItemViewDataSource" runat="server" ContextTypeName="GSFyi.GSFyiDataClassesDataContext"  EnableDelete="true" TableName="FYI_Items" />

<h2 class="gridTitle">All Items</h2>
<telerik:RadGrid ID="ItemViewRadGrid" runat="server" AutoGenerateColumns="False" DataSourceID="ItemViewDataSource" GridLines="None" AllowAutomaticDeletes="True" EnableEmbeddedSkins="False" OnItemDataBound="itemsGrid_ItemDataBound">
<HeaderContextMenu>
 <CollapseAnimation Type="OutQuint" Duration="200"></CollapseAnimation>
</HeaderContextMenu>
<MasterTableView DataKeyNames="id" DataSourceID="ItemViewDataSource" CommandItemDisplay="None" CssClass="listItems" Width="98%">
 <RowIndicatorColumn>
  <HeaderStyle Width="20px" />
 </RowIndicatorColumn>
 <ExpandCollapseColumn>
  <HeaderStyle Width="20px" />
 </ExpandCollapseColumn>
 <Columns>
  <telerik:GridTemplateColumn ItemStyle-CssClass="gridActions edit" UniqueName="Edit">
   <ItemTemplate>
    <asp:HyperLink ID="edit" runat="server" Text="Edit"></asp:HyperLink>
   </ItemTemplate>
<ItemStyle CssClass="gridActions edit"></ItemStyle>
  </telerik:GridTemplateColumn>
  <telerik:GridButtonColumn ConfirmText="Are you sure you want to delete this item?" ConfirmDialogType="RadWindow" ButtonType="LinkButton" ItemStyle-CssClass="gridActions delete"  CommandName="Delete" >
<ItemStyle CssClass="gridActions delete"></ItemStyle>
  </telerik:GridButtonColumn>
  <telerik:GridBoundColumn DataField="name" HeaderText="Item Name" SortExpression="name"
   UniqueName="name">
  </telerik:GridBoundColumn>
                    <telerik:GridTemplateColumn HeaderText="State" UniqueName="state">
                        <ItemTemplate>
                            <asp:Label ID="stateLbl" runat="server" Text='<%# Eval("stateid") %>' />
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
 </Columns>
</telerik:RadGrid>

And the current code-behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Telerik.Cms.Web.UI;
using Telerik.Web.UI;

public partial class Custom_Modules_GSFyi_Backend_Views_ItemsListView : ViewModeUserControl<ItemsView>
{
    protected void Page_Load(object sender, EventArgs e)
    {
        addNewItem.NavigateUrl = CreateHostViewCommand("ItemsInsertView",null,null);
    }
    protected void itemsGrid_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
    {
        if (e.Item.ItemType == GridItemType.Item || e.Item.ItemType == GridItemType.AlternatingItem)
        {
            var item = (GSFyi.FYI_Item)e.Item.DataItem;
            HyperLink edit = (HyperLink)e.Item.FindControl("edit");
            edit.NavigateUrl = CreateHostViewCommand("ItemsEditView", item.id.ToString(), null);

        }
    }
}

Does this help at all?

Answer 1

Try something like this:

var query = from s in States
            join i in Items on s.ID equals i.StateID
            select i.Name;

The LINQ to SQL provider will take this query and convert it to a SQL query that will look something like this:

select i.Name
from Items i
    inner join States s on i.StateID = s.ID;

Suggested reading: SQL INNER JOIN Keyword

Answer 2

Try it:

var query = from i in Items
            join s in States on s.id equals i.stateid
            select s.name, i.name

Answer 3

Assuming you've defined the relationship in your LINQtoSQL data classes and the LINQDataSource control is pointing at your Items entity you should be able to use the following databinding syntax directly in your GridView markup:

<asp:GridView ID="ItemsGridView" runat="server" DataKeyNames="id" DataSourceID="ItemsLinqDataSource">
    <Columns>
     <asp:TemplateField>
                    <ItemTemplate>
                            <%# Eval("states.name") %>
                    </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

<asp:LinqDataSource ID="ItemsLinqDataSource" runat="server" ContextTypeName="DataClassesDataContext" TableName="items"></asp:LinqDataSource>