Working with LINQ To SQL many-to-many relationships in ADO.NET Data Services

I recently loaded the Visual Studio 2008 SP1 Beta and have been playing around with all of the new features.  Picture a 6 year old on Christmas Morning.  The major difference being that I did not wake up my wife to ask her if it was ok to download it yet.  Well, I digress. 

If you played with ADO.NET Data Services in the olden days (like 9 months ago) when it was Astoria, you will remember that it had a dependency upon the ADO.NET Entity Framework.  It would only expose Entity Data Models from the EF.  Well, with the latest CTP, as well as the new SP1, that limitation has been lifted.  You now have the ability to expose any data source, as long as there is a class that contains properties that implement IQueryable<T> (IUpdatable is required for writebacks).  So, given these new capabilities, I thought I'd start by exposing a LINQ To SQL data source.

I ran through the usual steps:

1. First, used the LINQ To SQL Template and designer to create a LINQ To SQL Data Source.  This is pretty straightforward:

2. I then added the required tables to the designer (see below).  I am using the Coho database in this example.  I have made it available here.  Notice that I added Wines2Accolade and Accolade tables.  The Wines2Accolade table creates a many-to-many relationship between the Wines table and the Accolades table.  Using the ADO.NET Entity Framework, we would simply model this table away in the entity model.  If you had and instance of a wine, you could navigate to Accolades via an Accolades property.  The EF allows for such higher order modeling.  However, LINQ to SQL does not.  Essentially, it allows you to create a 1:1 model between tables and objects.  I am willing to live with that behavior, so I move on...

3. I then use the ADO.NET Data Services Template to add a new data service:

4. I then set the data source for my Data Service:

5. I should be done and should be able to test my new services.  So that is what I did.  I opened a browser and navigated to my svc file.  What I saw was not the friendly list of all of my entity sets that I had exposed.  Rather, I got an error.  Not only that, ADO.NET Data Services swallowed my exception.  That is by design.  If you have looked into REST, you know that HTTP status codes act as the error handling mechanism in REST. 

6. However, when developing, such as now, I have a need to see the actual error (without looking through log files).  So my next step (thanks to Phani Raj on the Astoria Team) was to decorate my Data Service class with a ServiceBehaviorAttribute and pass in IncludeExceptionDetailInFaults = true:

[System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]

public class CohoDS : DataService<CohoDbModel.CohoDbEntities>

When I refreshed the browser, I saw the actual error:

7. What I found out was that theWines2Accolades table was causing issues.  As it turns out, ADO.NET Data Services requires that each entity have a key defined.  By default, a dual key in a many-to-many table is not supported.  What I needed to do was to give it a hint.  In other words, I needed to explicitly tell ADO.NET Data Services that there is a dual key.  I do this by decorating the appropriate type (in this case my Wines2Accolade type) with a DataServiceKey attribute, passing in either a single key name or a string array containing all of the keys.  I could have simply decorated the Wines2Accolade type in the generated CohoLTS.designer.cs file, but my change would be overwritten each time I regenerated the model.  What I decided to do was to add a partial Wines2Accolade class in a separate file (Wines2Accolade.cs):

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.Services.Common;

[DataServiceKey(new string[] { "WineID", "AccoladeID" })]

public partial class Wines2Accolade

{

}

As you can see, I simply delcared the dual key.

8. Lastly, I was able to test (successfully):

Hope that helps...