Best approach for inner joining to lookup tables

When you have 2 tables:

Product (ProductID, ProductTypeID)

ProductType(ProductTypeID, Description)

What is the best approach to display the ProductType(Description) field in the Product GridView?

I'm currently using TypedDataSource(s) and binding them declaratively to the GridView using the GetPaged method.  This works fine, except that the Product table displays the columns ProductID, ProductTypeID.  What I'd like to display is ProductID and ProductType.Description (from the lookup table) based on the ID.

Do I have to write a custom sproc for each table that requires this join, and then bind the GridView to the returned dataset instead?  Or am I missing something altogether?  Maybe I should be DeepLoading the Products children in C# instead of declaratively via GetPaged, and then Bind the GridView to the TList?

Any insights are appreciated.

Thanks,

Scott K.
 

 

I guess one other option would be to manually add a ProductTypeDescription field to Product entity, and then I could have that lazy load based on the ProductTypeID that was auto-generated.  This would save me having to write a custom joined get procedure for every table in the system, as I could still use the TypedDataSource controls and rely on the Product entity itself to hunt for the TypeDescription at runtime.  This seems awfully inefficient though, when binding to a grid with multiple rows ...

 Scott.
 

You can create a view called "ViewProduct" for example,  and get NetTiers to generate entities for you if you specify the view in the "SourceViews" property. You can then bind your grid to the VList collection.

Yup, that would work too.  The main problem though is I don't want to give up the auto-generation.  I'm not one of those programmers who thinks software should entirely write itself, but here's an idea for a feature add.  If the Product table has a relationship to an Enum table, then autogenerate a means of joining to those tables for descriptions. (such as create ProductView) etc... Considering what .NET tiers is already doing, this shouldn't be too difficult.

I might take a shot at putting this into the Codesmith template files, but deadlines are deadlines I'm afraid...:(
 

skbach,

You could declaratively deepload your master table and display referenced table fields, check this thread out for an example http://community.codesmithtools.com/forums/thread/20343.aspx

 

Mike,

That looks like the exact solution I was looking for.  Thanks a lot man!

As a side question, these issues would be less time consuming if there was even basic documentation about the Web UI stuff.  I've been through the wiki extensively but that leaves quite a lot of room for learning the hard way :).  Either way, I shouldn't *** too much because .NETTiers is awesome, and the team is probably too busy to get to the docs, but they sure would be worth their weight in gold.

Thanks again. 

Well Mike, I think I spoke too soon.  After playing with it for quite some time I still can't get it to display the Description field from the lookup table.  Thought I'd repost here to see if you have a minute to take a look at what might be wrong.  I copied the code from the other thread for ease, but it's still not displaying.  The deep load is working fine in the code behind, so I know my relationships are setup correctly and everything should be working.  This is the code that WORKS (in C#)

 ProductionService service = new ProductionService();
 Production production = service.GetByProductionID(1);
 Type[] typesToDeepLoad = new Type[] { typeof(PropstarStatus)};
 service.DeepLoad(production, false, DeepLoadType.IncludeChildren, typesToDeepLoad);

 With that code, I can call

    production.PropstarStatusIDSource.Description

and get the description just fine.  However, on the ASP.NET side, it comes back null, or empty string.  This is the code, similar to the other post, that I can't seem to make work:

<data:EntityGridView ID="egvSearch" runat="server" AutoGenerateColumns="False"                  
            DataSourceID="dsProduction"
            DataKeyNames="ProductionID"
            AllowMultiColumnSorting="False"
            DefaultSortDirection="Ascending"  
            ExcelExportFileName="Export_Orders.xls"
            AllowSorting="True"
            AllowPaging="True" AllowExportToExcel="True" ExportToExcelText="Excel" PageSelectorPageSizeInterval="10">
        <Columns>
            <asp:BoundField DataField="Title" HeaderText="Title" />
            <asp:TemplateField HeaderText="Status">
                <ItemTemplate>                       
                    <%# Eval("PropstarStatusIDSource.Description")%>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </data:EntityGridView>
    
    <data:ProductionDataSource ID="dsProduction" runat="server" SelectMethod="GetPaged">
    <DeepLoadProperties Method="IncludeChildren" Recursive="false">
        <Types>
             <data:ProductionProperty Name="PropstarStatus" />
        </Types>
    </DeepLoadProperties>    
    <Parameters>
            <asp:ControlParameter Name="WhereClause" ControlID="__Page" PropertyName="WhereClause" Type="String" />
            <asp:ControlParameter Name="OrderByClause" ControlID="egvSearch" PropertyName="SortExpression" Type="String" />
            <asp:ControlParameter Name="PageIndex" ControlID="egvSearch" PropertyName="PageIndex" Type="Int32" />
            <asp:ControlParameter Name="PageSize" ControlID="egvSearch" PropertyName="PageSize" Type="Int32" />
            <data:CustomParameter Name="RecordCount" Value="0" Type="Int32" />
        </Parameters>
    </data:ProductionDataSource>

 

Any ideas? 

Fixed it!  I didn't have EnableDeepLoad="true" set in the ProductDataSource.  That wasn't in the original post (or maybe I missed it).  There may have been a default setting in older versions?  Either way, it's working great.

One thing I noticed though, is that sorting won't work against the description field, or will it?  SortExpression="PropstarStatusSourceID.Description" will throw an error.

 

 

Scott,

Glad you were able to resolve it! As far as sorting SourceID columns take a look here for ideas http://community.codesmithtools.com/forums/thread/19895.aspx

skbach:

Fixed it!  I didn't have EnableDeepLoad="true" set in the ProductDataSource.  That wasn't in the original post (or maybe I missed it).  There may have been a default setting in older versions?  Either way, it's working great.

One thing I noticed though, is that sorting won't work against the description field, or will it?  SortExpression="PropstarStatusSourceID.Description" will throw an error.

 

 

Hi!

After reading this thread, I still think that  Scott's idea having an option to automatically load LookUp Tables is the best. The extra properties generated by the lookup table could be read-only. Having to perform extra queries and create extra objects just to be able to get some lables tied to some IDs is not my idea of efficiency. I hope someone with the required knowledge implements it. 

Cheers!

JF