Hierarchical Databinding with Linq to SQL

Question

I am attempting what I think is a rather simple databinding scenario with Linq To SQL.

I have a table of FacultyMembers, whose schema looks something like this:

• FacultyMemberID - int PK
• Name - nvarchar
• UniversityID - int FK to University table

and so forth. There are various other string properties.

I generate LTS DataClasses. I drop a LinqDataSource and a GridView on a page, enable update and delete for both, and I'm on my merry way. No code, and I'm able to update my string properties. A little manipulation with a DropDownList on the UniversityID and I'm able to update that one-to-many relationship, too. Yay.

Now, lets say I throw in a many-to-many mapping table. Let's say DivisionMemberships, which maps FacultyMembers to Divisions. DivisionMembership uses the simple and obvious schema:

• FacultyMemberID - int PK, FK to FacultyMembers table
• DivisionID - int PK, FK to Divisions table

Now, when I take a row of my GridView into EditMode, I run into a problem because I don't know how to update the many-to-many relationship. I mucked around with a few alternatives, and right now I'm trying to get a ListView to work in there. I'm doing something like this:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    AllowPaging="True" AllowSorting="True" PageSize="25" DataKeyNames="FacultyMemberID" >
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        <asp:TemplateField HeaderText="University" SortExpression="UniversityID">
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Eval("University.Name") %>' />
            </ItemTemplate>
            <EditItemTemplate>
                <asp:DropDownList ID="DropDownList2" runat="server" 
                    DataSourceID="LinqDataSourceUniversities" DataTextField="Name" 
                    DataValueField="UniversityID" SelectedValue='<%# Bind("UniversityID") %>'>
                </asp:DropDownList>
                <asp:LinqDataSource ID="LinqDataSourceUniversities" runat="server" 
                    ContextTypeName="NYDERHE.NYDERHEDataClassesDataContext" 
                    Select="new (UniversityID, Name)" TableName="Universities">
                </asp:LinqDataSource>
            </EditItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Division">
            <EditItemTemplate>
                <asp:ListView ID="ListView1" runat="server"
                    InsertItemPosition="LastItem" DataSource='<%# Eval("DivisionMemberships") %>'><ItemTemplate>
                        <li style="">FacultyMemberID:
                            <asp:Label ID="FacultyMemberIDLabel" runat="server" 
                                Text='<%# Eval("FacultyMemberID") %>' />
                            <br />
                            DivisionID:
                            <asp:Label ID="DivisionIDLabel" runat="server" 
                                Text='<%# Eval("DivisionID") %>' />
                            <br />
                            Division:
                            <asp:Label ID="DivisionLabel" runat="server" Text='<%# Eval("Division") %>' />
                            <br />
                            FacultyMember:
                            <asp:Label ID="FacultyMemberLabel" runat="server" 
                                Text='<%# Eval("FacultyMember") %>' />
                            <br />
                            <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
                                Text="Delete" />
                        </li>
                    </ItemTemplate>
                    </asp:ListView>
                </EditItemTemplate>

and so forth. Some of the chatter above is removed, but ListView is pretty verbose as-is, so I don't want to overload the page.

Things to note here:

• For my University association, I use a new LinqDataSource and query for items WHERE the UniversityID matches, then bind the new UniversityID (DDL value) to the FacultyMember, whereas for the DivisionMemberships, I bind directly to the property (as described here)
• I use DataBinder.Bind() for UniversityID, whereas I use DataBinder.Eval() for DivisionMemberships.

If I switch to Bind() for DivisionMemberships, I get a NotSerializableException for EntitySet. If I stick with Eval(), I have to write the OnDelete and OnInsert methods for the ListView myself, and I don't want to Delete or Insert the DivisionMemberships until the entire FacultyMember row exits EditMode. I'd probably create a DataContext and stick it in the session for this, because I don't have another way to mark the DivisionMemberships for updates.

I would think that this scenario would be pretty easy to enable out of the box, but I'm lost. Any advice on where to go from here? Specifically, should I wrestle with Bind() and try to make EntitySet serializable, should I bite the bullet and write the somewhat hackish code described above for storing a DataContext in session until the OnRowUpdating event fires, or am I going the wrong way completely?

Answer 1

You're right, it is long, but kudos on including some code, JIC.

It does seem apparent that you're not going to be able to do this with ordinary DataBinding. I'm not too keen on storing this in session, but I do understand that you don't want a postback.

What you might want to do is set up an Ajax call so that you can stick your new record (or your record change) into the DataContext. When the form is submitted, you can call SubmitChanges on the DataContext.

Answer 2

Seems to me Josh that trying to manage your many-to-many relationship from within the Faculty Member grid is not the right approach.In fact, I can't really visualise how this would work inside a grid, I am not surprised that you are lost :)

Better to use a 'detail' page where the context is = to a single Faculty member and provide a 'double list' interface for division membership maintenence. This would consist of 2 lists.

The righthand list shows all the divisions to which the faculty member is already a member(DivisionMembership rows where FacultymemberId = the context id) , the left hand list would show all the remaining Divisions. You could then provide '>>' and '<<' buttons in between the lists to add/remove the Faculty member from the divisions (Inserting/deleting DivisionMemberships rows)

Michael