Entity Framework and Foreign Keys: Part 2


Hi, so this is the next logical sequence in my series on the ADO Entity Framework.  If you read the previous article/entry you would be familiar with the replacement of ugly foreign key data with nice friendly text (or whatever).

This time, we’re going to look at how you can modify the data!

Edit: There will be more to come in this series on the Entity Framework.  As usual, if you found this helpful, please leave a comment :)

Introduction

We’re going to start with a DetailsView, just to simplify the example.  In our example scenario, we’re inserting or editing a single record/entity/row.  Borrowing from the previous article, we’ll stick with the “Link” sample.

We will assume you’ve added a DetailsView or FormView control to a web form and also added an EntityDataSource (much as in the previous article).

Once you’ve correctly configured the EntityDataSource via the Designer smart tags, and set the data source of the DetailsView to be the EntityDataSource, you should have a generated schema representation.  Edit the columns (through the designer or the source) and remove anything you don’t want displayed.

Our example looks like this:
image

Note: except the Drop List control, the rest are plain old asp:BoundFields.

See the last control?  It’s a drop down list and contains the friendly names of the associated section.  It didn’t get that way on its own though.  There are at least two ways to successfully populate the control.  I’ve chosen this approach for example purposes, but there are other techniques.

Here’s our DetailsView:

<asp:DetailsView ID="linkDetails" runat="server" AutoGenerateRows="False"
DataKeyNames="LinkId" DataSourceID="linkSource"
Width="435px" OnDataBound="LinkDetailsDataBound">
<Fields>

Here’s our EntityDataSource (note the inclusion of the Navigation Property – SectionListLink):

<asp:EntityDataSource ID="linkSource" runat="server" Include="SectionListLink"
ConnectionString="name=DataEntities" DefaultContainerName="DataEntities"
EnableInsert="True" EnableUpdate="True" EntitySetName="Link"
OnInserting="LinkSourceInserting" OnUpdating="LinkSourceUpdating">
</asp:EntityDataSource>

Let’s examine the source for our template field (drop list):

<asp:TemplateField HeaderText="Section">
    <
ItemTemplate>
        <asp:DropDownList ID="sectionList" runat="server" />
    </ItemTemplate>
</asp:TemplateField>

Nothing too fancy here.  Note that we don’t have any data binding, we’ll need to add it to the control’s OnDataBound event.  Let’s take a look at the OnDataBound event.

* Disclaimer *

Admittedly, there are a number of approaches I could take to make this a better solution – I’m trying to keep this fairly straightforward for explanation purposes, so please stay with me.

First, we need a local variable to store the entity’s section Id (it’s referenced when we populate the drop list):

#region Private Variables 

private int _selectedSectionId = Int32.MinValue; 

#endregion

///
<summary>
/// Select the proper name for the associated Section
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkDetailsDataBound(object sender, EventArgs e)
{
   
Link contentItem = EntityDataSourceExtensions.GetItemObject<Link>(linkDetails.DataItem);
   
if (contentItem == null)
       
return;

    if (contentItem.SectionListLink != null)
   
{
       
_selectedSectionId = contentItem.SectionListLink.SectionId;
   
}
}

We need to add some code to populate the Drop List control.  We’ll add it in the CreateChildControls event.

/// <summary>
/// Load the Reference Data
/// </summary>
protected override void CreateChildControls()
{
    LoadSectionList();
   
base.CreateChildControls();
}

You might be wondering why this looks a little out of sequence?  The call to FindControl() causes the control to Data Bind, thus LinkDetailsDataBound is called prior to LoadSectionList() – see below.

I’ve moved the implementation out into a static function for neatness.  It’s nothing fancy, see below.  Again, written simply for demonstration purposes.

///
<summary>
/// Load the Section List into the Droplist
/// </summary>
private void LoadSectionList()
{
    using (DataEntities de = new DataEntities())
    {
        var sectionEntity = from sl in de.SectionList
                            select sl;

        if (sectionEntity.Count() > 0)
        {
            DropDownList sectionDropList = linkDetails.FindControl("sectionList") as DropDownList;

            if (sectionDropList == null)
                return;  // Log or throw an exception – it’s up to you what to do

                         // in the event the control can’t be found

            foreach (SectionList sl in sectionEntity)
            {
                ListItem li = new ListItem();
                li.Text = sl.DisplayName;
                li.Value = sl.SectionId.ToString(CultureInfo.InvariantCulture);
                if (sl.SectionId == _selectedSectionId)
                    li.Selected = true;

                sectionDropList.Items.Add(li);
            }
        }
    }
}

Note: we could have kept these entities used to populate the Drop List around for later when a change (insert/update) is made.  For now we’ll just be discarding them, for ease of demonstration.

Now we will end up with the example as displayed at the start of the article.  However, we aren’t really much further beyond where the previous entry finished.  Or are we?  We’re in a position now to actually modify the foreign key.

image

The Big Deal

So this is one of the most frustrating aspects of the Entity Framework in it’s early life.  To change the foreign key you have to actually query for the new entity and attach it.  Sounds nasty, right?  It well could be in a larger, more complex scenario – but not for our current example.

The EntityDataSource plays a critical role in what happens next.  For both Updating and Inserting events, we will want to make our changes.  Add event handlers for both events, as below.  Since we’re going to do the same thing in both scenarios, I’ve moved the functionality out into a common Function.

/// <summary>
/// Set the association with a Section
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkSourceUpdating(object sender, EntityDataSourceChangingEventArgs e)
{
   
SetSectionList(e);
}

/// <summary>
/// Set the association with a Section
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkSourceInserting(object sender, EntityDataSourceChangingEventArgs e)
{
   
SetSectionList(e);
}

The shared Function is going to take the event and use it to obtain the current Entity.  What our aim here to do is to also retrieve the new entity based on the selected value of the Drop List. 

To do so I’ve written some functions to obtain the selected Section List, we’ll need to quickly look at them first.

* Disclaimer *

There’s plenty of options available to you – you could reuse the Entities you used to populate the Drop List in the first place, instead of re-querying for the data again.  This is a simple straightforward solution which hasn’t been tuned or optimised.  Be kind.

/// <summary>
/// Returns the currently selected Section List or null
/// </summary>
/// <param name="sc">Select SectionList or NULL</param>
/// <returns></returns>
private SectionList SetSection()
{
   
DropDownList sectionDropList = linkDetails.FindControl("sectionList") as DropDownList;
   
if (sectionDropList == null || String.IsNullOrEmpty(sectionDropList.SelectedValue))
       
return null; 

    string selectedSection = sectionDropList.SelectedValue;
   
int sectionId = Int32.MinValue;

    if(!Int32.TryParse(selectedSection, out sectionId))
       
return null;

    SectionList newSection = null; 

    using (DataEntities de = new DataEntities())
   
{
       
var sectionList = from s in de.SectionList
                         
where s.SectionId == sectionId
                       
  select s;

        if (sectionList.Count() > 0)
       
{
           
newSection = sectionList.First();
           
de.Detach(newSection);
       
}
   
}
   
return newSection;
}


 

What you might notice here is that once we have obtained the correct entity (yes, I really do bemoan having to do a query to get it!) we actually call Detach() to remove it from the context in which we queried for it – important to note.

Finally, now we can bring it all home..  Our method to set the foreign key relationship.

///
<summary>
/// Assign the new or updated section
/// </summary>
/// <param name="e"></param>
private void SetSectionList(EntityDataSourceChangingEventArgs e)
{
   
Link sc = EntityDataSourceExtensions.GetItemObject<Link>(e.Entity);
   
SectionList newSection = SetSection(); 

    if (newSection != null && sc != null)
   
{
       
e.Context.Attach(newSection);
       
sc.SectionListLinkReference.Value = newSection;
   
}
}

You’ll recall the GetItemObject method from the previous post.   Notice we have to Attach the selected (retrieved) new relationship?  Once it is attached we can assign it to the relationship as you’ll note in the last line of code.  This will now save correctly.

Summary

So hopefully this has all made sense to you.

I’m going to attempt to sum this all up and speak to the essential takeaways.  What we want to do is obtain the Entity related to the new Foreign Key value.  We’ve gone and replaced the normal ugliness with friendly display data (using the insidious Drop List control) and had to retrieve it again when inserting or updating.

The key here is you must detach the entity from its context and attach it to the EntityDataSource’s data context as you’ve seen in the example code.  Once you’ve done so, you can assign it to the reference and let the control do its work.

How you choose to retrieve, store and attach/detach the entities is entirely up to your own imagination.  Caching is a potential solution (especially for reference data which changes rarely).  Storing it in state someplace is another option (but probably not popular).  If the data is small, a few short round trips might be fine without affecting performance, but you need to ensure you properly test and load balance your solution before making any assumptions.

Hopefully in the next version the EntityDataSource may be extended to provide us with a bit more support in this area.  There’s a horribly burdensome amount of work to be done.

Cheers /R


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>