I'm using the following code to create a query that joins two tables together and then displays the result of this query in a datagrid. The problem that I'm having is that I can display all the fields from the BottomBracket table, but not for the ProductVersion table. Does anyone know how I can make this work. One thing that's happening that I think is odd is that there is no error thrown when I use this code:

<asp:TemplateColumn HeaderText="ActiveState">
    <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "ProductVersion.ActiveState") %>
    </ItemTemplate>
</asp:TemplateColumn>

The code above works, but doesn't return any values for the ProductVersion.ActiveState column.

Here is the code I use in the aspx page:

<asp:DataGrid
    AutoGenerateColumns="False"
    BorderStyle="Solid"
    CellPadding="2"
    CellSpacing="0"
    Width="100%"
    DataKeyField="ProductVersionID"
    ID="bottomBracketsGrid"
    Runat="server">
    <HeaderStyle BackColor="#ffffff" Font-Bold="True"/>
    <ItemStyle BackColor="#ffffff"/>
    <AlternatingItemStyle BackColor="WhiteSmoke"/>
<Columns>
    <asp:BoundColumn DataField="ProductVersionID" ReadOnly="True" Visible="False"/>
    <asp:TemplateColumn HeaderText="ActiveState">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "ProductVersion.ActiveState") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="IsFixedCup">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "IsFixedCup") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="SpindleLength">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "SpindleLength") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="SpindleSplines">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "SpindleSplines") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="Width">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "Width") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="WidthMax">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "WidthMax") %>
        </ItemTemplate>
    </asp:TemplateColumn>
</Columns>
</asp:DataGrid>

And the code for the aspx.cs class:

public class Detail : System.Web.UI.Page
    {
        protected DataGrid bottomBracketsGrid;
        private int productID;

        private void Page_Load(object sender, System.EventArgs e)
        {
            if ( !Page.IsPostBack )
            {
                this.productID = Convert.ToInt32(Request.QueryString["productID"]);
                if ( this.productID > 0 )
                {
                    this.DataBindGrid();
                }
            }
        }

        private void DataBindGrid()
        {
            bottomBracketsGrid.DataSource = this.GetBottomBrackets();
            bottomBracketsGrid.DataBind();
        }

        private EntityCollection GetBottomBrackets()
        {
            DataAccessAdapter adapter = new DataAccessAdapter();
            EntityCollection bottomBrackets = new EntityCollection( new BottomBracketEntityFactory() );
            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add( BottomBracketEntity.Relations.ProductVersionEntityUsingProductVersionId );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(BottomBracketFieldIndex.BottomBracketId, ComparisonOperator.Equal, this.productID) );
            adapter.FetchEntityCollection( bottomBrackets, filter );
            return bottomBrackets;
        }
more class code...

Using SQL Server Profiler I've found that the join is being created using the code in my previous post, but the fields from the ProductVersion table are not being added to the select statement. Does anyone know how to change the code to get access to the ProductVersion table's attributes?

tprohas wrote:

I'm using the following code to create a query that joins two tables together and then displays the result of this query in a datagrid. The problem that I'm having is that I can display all the fields from the BottomBracket table, but not for the ProductVersion table. Does anyone know how I can make this work. One thing that's happening that I think is odd is that there is no error thrown when I use this code:

<asp:TemplateColumn HeaderText="ActiveState">
    <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "ProductVersion.ActiveState") %>
    </ItemTemplate>
</asp:TemplateColumn>

The code above works, but doesn't return any values for the ProductVersion.ActiveState column.

Here is the code I use in the aspx page:

<asp:DataGrid
    AutoGenerateColumns="False"
    BorderStyle="Solid"
    CellPadding="2"
    CellSpacing="0"
    Width="100%"
    DataKeyField="ProductVersionID"
    ID="bottomBracketsGrid"
    Runat="server">
    <HeaderStyle BackColor="#ffffff" Font-Bold="True"/>
    <ItemStyle BackColor="#ffffff"/>
    <AlternatingItemStyle BackColor="WhiteSmoke"/>
<Columns>
    <asp:BoundColumn DataField="ProductVersionID" ReadOnly="True" Visible="False"/>
    <asp:TemplateColumn HeaderText="ActiveState">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "ProductVersion.ActiveState") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="IsFixedCup">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "IsFixedCup") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="SpindleLength">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "SpindleLength") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="SpindleSplines">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "SpindleSplines") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="Width">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "Width") %>
        </ItemTemplate>
    </asp:TemplateColumn>
    <asp:TemplateColumn HeaderText="WidthMax">
        <ItemTemplate>
            <%# DataBinder.Eval(Container.DataItem, "WidthMax") %>
        </ItemTemplate>
    </asp:TemplateColumn>
</Columns>
</asp:DataGrid>

And the code for the aspx.cs class:

public class Detail : System.Web.UI.Page
    {
        protected DataGrid bottomBracketsGrid;
        private int productID;

        private void Page_Load(object sender, System.EventArgs e)
        {
            if ( !Page.IsPostBack )
            {
                this.productID = Convert.ToInt32(Request.QueryString["productID"]);
                if ( this.productID > 0 )
                {
                    this.DataBindGrid();
                }
            }
        }

        private void DataBindGrid()
        {
            bottomBracketsGrid.DataSource = this.GetBottomBrackets();
            bottomBracketsGrid.DataBind();
        }

        private EntityCollection GetBottomBrackets()
        {
            DataAccessAdapter adapter = new DataAccessAdapter();
            EntityCollection bottomBrackets = new EntityCollection( new BottomBracketEntityFactory() );
            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add( BottomBracketEntity.Relations.ProductVersionEntityUsingProductVersionId );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(BottomBracketFieldIndex.BottomBracketId, ComparisonOperator.Equal, this.productID) );
            adapter.FetchEntityCollection( bottomBrackets, filter );
            return bottomBrackets;
        }
more class code...

You're fetching entities, of type BottomBracketEntity. This means that the entity collection you're fetching will contain the fields of BottomBracketEntity, and no other entity. The join you specify is not changing that.

If you want to include fields of other entities, either create a typed list in the designer, or in code create a dynamic list (see documentation of typed list in the latest release)

Otis wrote:

You're fetching entities, of type BottomBracketEntity. This means that the entity collection you're fetching will contain the fields of BottomBracketEntity, and no other entity. The join you specify is not changing that.

If you want to include fields of other entities, either create a typed list in the designer, or in code create a dynamic list (see documentation of typed list in the latest release)

Thanks for the reply.

I finally figured out that I needed to create a prefetch path like this.

EntityCollection bottomBrackets = new EntityCollection( new BottomBracketEntityFactory() );
IPrefetchPath2 prefetchPath = new PrefetchPath2( (int)EntityType.BottomBracketEntity );
prefetchPath.Add( BottomBracketEntity.PrefetchPathProductVersion );
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add( PredicateFactory.CompareValue(BottomBracketFieldIndex.BottomBracketId, ComparisonOperator.Equal, this.productID) );
this.adapter.FetchEntityCollection( bottomBrackets, filter, prefetchPath );

Since there is a one to one relationship between BottomBracket and ProductVersion I was able to solve the problem using the prefetch path. I don't know if this is the recommended way of doing this, but it worked.

I then used the code below to display the field I want from the ProductVersion table in my datagrid.

<asp:TemplateColumn HeaderText="ActiveState">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "ProductVersion.ActiveState") %>
</ItemTemplate>
</asp:TemplateColumn>

If you have any suggestions for improving this I would sure appreciate it.

You could use a typed list, which only uses 1 query. But if you want to work with entities, the prefetch paths are the way to go as they will require the least amounts of queries.