ASP.net Development: ADO Entity Framework and Foreign Keys


Here are some lessons learned from using the Entity Framework and the EntityDataSource provider in ASP.net development.

Edit: Hi Everyone,
I’ve been getting some traffic on this topic and I realise I’ve only written half the details (there is more to come).  Please bare with me and I’ll aim to get the second part of this post published shortly.  If you’ve found this or some of the previous posts helpful, please don’t be shy.. add a comment and say hi! :)

Introduction

One of the challenges when dealing with an ASP.net GridView is rendering foreign keys with friendly data such as a display name instead of an ugly or often meaningless number of unique identifier.

Since our preferred implementation is typically to use data binding, this automatically infers we’re going to need some magic on the DataBound event.

To begin with, let’s review our Web Form.  The premise is that we have an Entity Framework data model in our solution.  On our Web Form we’ve placed a ASP GridView and an EntityDataSource.

entitydatasource

Using smart tags in the form designer, we’ve configured the EntityDataSource to use a specific Entity Set which has at least one Navigation property. 

A Navigation Property is basically a relationship, for example “SectionListLink”, which is a one-to-many relationship between a section (one) and a link (many), as in the model below.  We will use the “Link” entity as our example.

framework-model

The first thing is we’re going to need to extend the Query Path on our EntityDataSource.

By default, the EntityDataSource isn’t going to retrieve the foreign key data (such relationships are lazy loaded) which is going to be necessary if we are to walk the relationship hierarchy.  When we obtain this additional information we are referring to loading or retrieving the complete object graph – we retrieve other objects with which our primary object has a relationship with.

This isn’t as hard as it sounds!  To extend the EntityDataSource simply use the “Include” property and name the Navigation Property, e.g.

entitydatasource1

We can also do this programmatically (in the event you do not use a EntityDataSource) by adding the Include query path specification to our LINQ query, like so:

linq-query

A word of warning: use the Include operator sparingly!  As a general rule of thumb, avoid using more than three query paths otherwise resulting queries will be severely bloated.

Below is a snapshot of an Entity Framework query against SQL Server where the Include property has three Navigation Properties (foreign keys) specified (column names blurred):

largerquery

However, back to our example – in order to create a GridView which would look something like the example below, we’re going to have to retrieve the appropriate display value after each row is data bound.  The section highlighted in yellow is the Display Name of the associated section.

grid

Instead of binding the identifier of the associated section (which is not very useful for users), we’re going to render the name of the section instead.  As a result, in the GridView’s columns we’ve added a TemplateItem so we can add the display text once each row has bound.

image

Before we take a look at the DataBinding event for this GridView, we need to take a peek at how we can cast the DataItem to an Entity – which you can not do by simply casting the GridView’s Row.DataItem (“how to get the real entity object in the RowDataBound event of a GridView?”).  Refer to this article for details.

Basically, you need to implement this functionality in order to convert a data bound entity:

/// <summary>
/// Used by many Modules and Pages to Obtain the Correct Entity Framework Data Item
/// from a GridView or DetailView or FormView
/// <typeparam name="TEntity">The original Entity type</typeparam>
/// <param name="dataItem">Data Item to convert</param>
/// <returns>Entity object or null</returns>
public static TEntity GetItemObject<TEntity>(object dataItem)
    where TEntity : class
{
    var entity = dataItem as TEntity;
    if (entity != null)
    {
            return entity;   
   
}
   
var td = dataItem as ICustomTypeDescriptor;
    if (td != null)
    {
            return (TEntity)td.GetPropertyOwner(null);
    }
    return null;
}

Now, let’s review the code from the GridView’s OnRowDataBound event handler:

/// <summary>
/// Update Section Name
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void LinkGridRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e == null || e.Row == null || e.Row.DataItem == null)
       
return; 

    Link linkData = EntityDataSourceExtensions.GetItemObject<Link>(e.Row.DataItem);
    if (linkData == null)
       
return;

    if (linkData.SectionListLink != null) //Ensure the foreign key data is valid
    {
       
Label lbl = e.Row.FindControl("sectionName") as Label; 

  if (lbl != null)
       
{
            lbl.Text = linkData.SectionListLink.DisplayName;
       
}
   
}
}

As you can see, this isn’t overly complex.  However, failing to add and specify the “Include” property means that the Navigation Property (“SectionListLink”) wouldn’t be populated (although we could force a load programmatically), e.g.   

   if (!linkData.SectionListLink.IsLoaded) //Ensure the relationship has loaded 
        linkData.SectionListLink.Load();
       

It’s not a tough concept, but worth remembering if you are in the position of having to deal with rendering friendly names for foreign key relationships.  Apologies for the example code, this has been crafted for a simple example, for explanation purposes only.

Next article, I’ll explain how to actually modify these relationships in the within context for a DetailsView – be warned, it’s not trivial :(


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>