Working with Many-to-Many Relationships in OData via WCF Data Services

The WCF Data Service implementation of OData makes for fantastic demo-ware, meaning you’re able to quickly knock up sample applications where you publish simple data base schemas and consume them in a Windows Phone, Silverlight or WPF application. However as soon as you start to do anything moderately complex the model seems to break. In my previous post “Windows Phone LINQ to SQL and the INotifyPropertyChanged and INotifyPropertyChanging Interfaces” I highlighted where the strongly typed client proxy classes fail when you start to Expand a number of entity relationships in a Select statement. In this post we’re going to take a quick look at working with Many-to-Many relationships and the failings specifically around updates.

Let’s work with a simple database schema where we have a table of Movies, and each movie can be assigned to multiple Genres. Of course, many different movies may be assigned to a single Genre, hence we have a many-to-many relationship. In our database schema this is represented using a linking table, MovieGenre, which has two columns representing the MovieId and the GenreId. The primary key is both columns.

In our entity model consumes this joining table, simply exposing the relationship as a collection on both Genre and Movie, as illustrated by the many-to-many association in the following entity model image.

image

For the most part working with this data is simple. We can query the movie data in exactly the same way that we do normally, for example:

var movies = from m in movieEntities.Expand(“Genres”)
                        select m;

The issues start to arise when you want to modify the genres that are associated with a movie. Adding a Genre is easy (newGenre is an existing Genre retrieved from the Genre table):

movie.Genres.Add(newGenre);

This is handled correctly by the client library and the appropriate POST is made to WCF Data Services to add the Genre to the Genres collection on the Movie (and hence to the MovieGenres table). What doesn’t work is removing a Genre. For example the following does NOT work:

movie.Genres.Remove(newGenre);

In fact, when you run this the request that is sent back to WCF Data Service attempts to delete the actual Genre from the database (luckily when I discovered this I hadn’t permitted write access to the table I was working with so this failed).

The work around is to call the AddLink and RemoveLink methods to add the appropriate links between the entities. Unfortunately these methods again require the use of string literals:

AddLink(movie, “Genres”, newGenre);

RemoveLink(movie, “Genres”, newGenre);

We can again use the same trick we used in the previous post to create a helper method that can ensure we have strongly typed code. The following codes actually replaces the existing related entities with a new set by iterating through and either adding or removing the appropriate entities.

public static void UpdateRelatedEntities<TSource, TTarget>(this DataServiceContext entities,
    TSource source,
    Expression<Func<DataServiceCollection<TTarget>>> collectionProperty,
    IEnumerable<TTarget> newRelatedEntities) {
    var memberExpression = collectionProperty.Body as MemberExpression;
    string collectionName = memberExpression.Member.Name;
    var sourceProperty = source.GetType().GetProperty(collectionName).GetValue(source, null) as IEnumerable<TTarget>;
    foreach (var genre in sourceProperty) {
        if (!newRelatedEntities.Contains(genre)) {
            //movie.Genres.Remove(genre);
            entities.DeleteLink(source, collectionName, genre);
        }
    }
    //movie.Genres.Clear();
    foreach (var newGenre in newRelatedEntities){
        if (!sourceProperty.Contains(newGenre)) {
            //movie.Genres.Add(newGenre);
            entities.AddLink(source, collectionName, newGenre);
        }
    }
}

The following demonstrates using this code:

movieEntities.UpdateRelatedEntities(movie, () => movie.Genres, newGenres);

Would love to think that I’ve missed an obvious way to achieve the same thing – if I have, please drop me an email and I’ll update the post. Apologies as comments are currently disabled.

Leave a comment