Selecting Hierarchical data with WCF Data Services

In this post I’ll describe how you can use hierarchical data with WCF Data Services as it’s not immediately obvious how to do this, and there’s also an issue (which I believe to be a bug) that needs to be addressed in order for it to work.

In this example I have a simple table shown below, with a hierarchical link between employees. The keys are integers, defined as IDENTITY columns in SQL server. All the code for this example is attached to this post if you should wish to look into it further.

I’ve also created a stored procedure that selects people in a hierarchical manner using a CTE. This is as follows…

-- Create an SP that selects a hierarchical set of data
CREATE PROCEDURE SelectPersonHierarchy(@personId int) AS
  SET NOCOUNT ON;
  WITH PersonHierarchy AS
  (
    SELECT PersonId, Name, ManagerId
      FROM Person
      WHERE PersonId = @personId
    UNION ALL
      SELECT p.PersonId, p.Name, p.ManagerId
        FROM Person p
          INNER JOIN PersonHierarchy ph
          ON p.ManagerId = ph.PersonId
  )
  SELECT PersonId, Name, ManagerId
    FROM PersonHierarchy
GO

So far so good. Next I created an EF model that includes the table above and the stored procedure too. Then I created a simple WCF data service as shown below – the extra method is used to expose the hierarchical stored procedure, so that clients can call it directly.

public class PersonService : DataService<PersonEntities>
{
    public static void InitializeService(DataServiceConfiguration config)
    {
        config.SetEntitySetAccessRule("*", EntitySetRights.All);
        config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);

        // And set the protocol version used
        config.DataServiceBehavior.MaxProtocolVersion = 
            DataServiceProtocolVersion.V2;
    }

    [WebGet]
    public IQueryable<Person> GetFullPerson(int personId)
    {
        return this.CurrentDataSource.
            SelectPersonHierarchy(personId).AsQueryable();
    }
} 

Next I created the client for this service using Add Service Reference. Then I created a method that would call the GetFullPerson method on the server, the outline of which is shown below…

public Person GetFullPerson(int personId, bool makeItWork)
{
    Person root = null;

    this.CreateQuery<Person>("GetFullPerson")
        .AddQueryOption("personId", personId).ToList().ForEach(
    t =>
    {
        if (null == t.ManagerId)
            root = t;
        else
        {
            Person parent = this.FindManager(root, t.ManagerId.Value);
            parent.Subordinates.Add(t);
        }
    });

    return root;
}

This method converts the data returned from the WCF operation to a list, iterates through that list and builds the client Person hierarchy (using a small helper function FindManager which, given a tree of objects, looks for the manager of the current person).

So, with all that in place we can write some unit tests to test the service. In my tests I’ve just used Boss and Employee as my two people. First off we’ll insert a Boss and an Employee & link them together…

Uri geller = new Uri("http://localhost:11341/PersonService.svc");
ServerService.PersonEntities context = 
    new ServerService.PersonEntities(geller);

// Insert hierarchical data to test with
var boss = new ServerService.Person { Name = "Boss" };
context.AddToPeople(boss);

var employee = new ServerService.Person { Name = "Employee", Manager = boss };
context.AddToPeople(employee);

// Important to add this - otherwise EF won't record the parent/child 
// link in the database
context.AddLink(boss, "Subordinates", employee);

// Now save this to the server
context.SaveChanges();

Apologies for the awful pun in the Uri variable, I just can’t help myself. Here I create a boss, add an employee and then save the changes to the database. The important thing to mention is that you must also call context.AddLink() to ensure that there is a link between the parent and child objects. Here “Subordinates” is the name of the EF navigation property that is mapped from the parent of the relationship to the child. After saving these changes in the database you’ll see the following in the DB (Id’s will differ)…

OK, so far so good, we can create and save data to the server.

The Problem

The next thing I wanted to do was to verify that I could reload a person (in a hierarchical manner). So, my unit test was basically as follows…

• Create a Boss and an Employee


• Save these to the database


• Reload these and test that all data was correct, including the link between employee and boss. This is where it all started to go wrong.

The reload code wasn’t working properly, and I tracked it down to the ManagerId field (after a fair amount of time). First I used Fiddler to log what was coming down from the server. My request looked OK…

And the data coming back from the server also looked OK…

However in my code that was looping through the data that came from the server the ManagerId was always null, for each and every row returned from the server.

In another test however it was fine – what gives?

The Solution

My two unit tests were identical – well, nearly. One verified what it got inline, another called a method to do that verification and the crucial difference was that the one that worked used a new service context. So, if you create a hierarchy of Boss & Employee on the client, save changes and reload (using the same client proxy), the hierarchical link is broken. If however you create a new client proxy and call the server everything works as expected.

My solution to this was to include the following in the method that calls the server…

public Person GetFullPerson(int personId)
{
    Person root = null;

    var currentMergeOption = this.MergeOption;
    this.MergeOption = MergeOption.OverwriteChanges;

    this.CreateQuery<Person>("GetFullPerson")
        .AddQueryOption("personId", personId).ToList().ForEach(
    t =>
    {
        if (null == t.ManagerId)
            root = t;
        else
        {
            Person parent = this.FindManager(root, t.ManagerId.Value);
            parent.Subordinates.Add(t);
        }
    });

    this.MergeOption = currentMergeOption;

    return root;
}

This ensures that the data read from the server overwrites the data on the client, and in this instance that’s enough for the ManagerId to be set correctly rather than being Null.

Example Code

The following is a link to a .zip file that contains the same code. You’ll find a database script in the EFHierarchyProblem project called SchemaAndLogic.sql. Run this against your database.

You should also update the database connection strings in each project to reference your database. There are 3 projects in the solution, a class library that contains the EF model and the service, a Web project that exposes that service, and a Test project that runs the unit tests. When you run the tests you should see the following in the test results window…

The Test_ServerService_WillFail should work but doesn’t, as the ManagerId is overwritten when reloading the data. The _WillWorkSameContext test uses the merge option to overwrite local data to ensure that the ManagerId isn’t overwritten, and the _WillWorkNewContext test inserts data but then requests it from a new client proxy which will always work.

The .zip can be downloaded from here.