Wednesday 10 December 2008

Astoria and LINQ-to-SQL; getting started

ADO.NET Data Services (previously known as "Astoria") is one the new .NET 3.5 SP1 tools for offering CRUD data access over the web.

In this article, I'll discuss using ADO.NET Data Services, looking first at Entity Framework, but (since I'm a firm believer in parity across implementations) more specifically, looking at LINQ-to-SQL.

Although ADO.NET Data Services uses WCF, it is at heart a RESTful stack (not a SOAP operation stack), that offers:

  • Simple (REST/CRUD) platform-independent access to data, including filtering etc, but limited to homogenous data and full object representations [no custom projections at the point of query]
  • A .NET client and object model, including change-tracking support, making it easy to make complex changes
  • Support for LINQ at the client, which is translated into requests that the REST layer understands

A very powerful tool!

Exposing data via Entity Framework

So: how can we use it? Unsurprisingly, it plays very nicely with Entity Framework: you simply create a data-service endpoint (using the "ADO.NET Data Service" template in VS 2008), and tell it about your object-context:

    public class EntityService : DataService<NorthwindEntities> // your object-context here...
public static void InitializeService(IDataServiceConfiguration config)
// specify permissions for entity-sets and operations
config.SetEntitySetAccessRule("*", EntitySetRights.All); // debug only; don't do this!

And that is it! Navigate to the .svc in a browser, and you should see the REST/ATOM-based response:

<service [snip]>
<collection href="Categories">
<collection href="CustomerDemographics">

This shows the navigable entity-sets - so adding /Categories to our request would return the categories, etc. So: that's Entity Framework sorted... how about LINQ-to-SQL?

Exposing data via LINQ-to-SQL

You might be slightly surprised that things aren't nearly so tidy. It transpires that ADO.NET Data Services will respect either a metadata-model (ala Entity Framework), or it will support a per-implementation fallback model otherwise. And since LINQ-to-SQL pre-dates ADO.NET Data Services, LINQ-to-SQL doesn't qualify (out of the box) for either. We need to wire a few things together... we'll look at exposing data in this article, and then consider updating data as a separate piece of work.

So - lets be optimistic, and change NorthwindEntities (our Entity Framework object-context) to NorthwindDataContext (our LINQ-to-SQL data-context). Boom! We get a big fat "Request Error", "The server encountered an error processing the request. See server logs for more details.":


So the first thing we need is some better debugging... as a debugging aid (i.e. not on a production server), we can get a lot more information by tweaking our DataService:

    [ServiceBehavior(IncludeExceptionDetailInFaults = true)] // ### show error details in the response
public class EntityService : DataService<NorthwindDataContext> // your data-context here...
public static void InitializeService(IDataServiceConfiguration config)
config.UseVerboseErrors = true; // ### and tell us as much as you can
// specify permissions for entity-sets and operations
config.SetEntitySetAccessRule("*", EntitySetRights.All); // debug only; don't do this!

We've enabled exception-details in the response, and enabled verbose errors. It still doesn't work, but at least we get a clue why: "On data context type 'NorthwindDataContext', there is a top IQueryable property 'CustomerDemographics' whose element type is not an entity type. Make sure that the IQueryable property is of entity type or specify the IgnoreProperties attribute on the data context type to ignore this property.":


Hmm... we don't want to ignore the property (or we cant get at the data), and it isn't an EntityObject in the Entity Framework sense, so what do we do?

It turns out that what this message actually means here is "I can't find the primary key"; outside of metadata-models, it supports a few ways of doing this:

  • via the new [DataServiceKey] attribute
  • by looking for a "FooID" property, where the type is called "Foo"
  • by looking for an "ID" property

As it happens, CustomerDemographic uses CustomerTypeID for the primary key, so no surprise. Fortunately, the designers seem to have considered the (likely) scenario of generated code, and so we can specify [DataServiceKey] at the type-level in a partial class. This is fortunate, since you can't use a partial class to add an attribute to a member (such as a property) defined in a separate portion of a partial class. So we add (in our data-context namespace):

partial class CustomerDemographic {}

And likewise for any other types that it isn't happy with. In general, the "FooID" pattern is so ubiquitous that it catches most use-cases. One minor gripe here is that it is case-sensitive: "FooId" won't work without a [DataServiceKey]. Something else to remember for that monthly "ID vs Id" squabble...

Interestingly, note that we haven't done anything LINQ-to-SQL specific yet; everything is just hanging off the exposed IQueryable<T> propertieson our data-context, so everything here would also work with LINQ-to-Objects.

Consuming the Data

Our service now shows in a browser without a big error - so  we can look at creating a client. We can do this in VS2008 simply using the "Add Service Reference..." dialog; it recognises REST/ATOM endpoints, and behaves accordingly:


Alternatively, you can use "datasvcutil" at the command-line, but (unusually, compared to wsdl.exe and svcutil.exe) you get a very limited set of command-line options here. After generating the client, you can use it very similarly to a LINQ-to-SQL data-context:

        static void Main()
var ctx = new NorthwindDataContext(BaseUri); // note not IDisposable
// enable crude logging
ctx.SendingRequest += (sender, e) => Debug.WriteLine(
e.Request.Method + ": " + e.Request.RequestUri);

ShowEmployees(from emp in ctx.Employees
where emp.FirstName.StartsWith("M")
select emp);
ShowEmployees(from emp in ctx.Employees
orderby emp.FirstName descending
select emp);
            Employee tmp = ctx.Employees.Where(x=>x.EmployeeID == 9).Single();
static void ShowEmployees(IQueryable<Employee> query)
foreach(var emp in query)
Console.WriteLine("{0}: {1}, {2}",
emp.EmployeeID, emp.LastName, emp.FirstName);

Which displays the correct employees, and also shows the HTTP request uris:

GET: http://localhost:28601/Restful.svc/Employees()?$top=20
GET: http://localhost:28601/Restful.svc/Employees()?$filter=startswith(FirstName,'M')
GET: http://localhost:28601/Restful.svc/Employees()?$orderby=FirstName desc
GET: http://localhost:28601/Restful.svc/Employees(9)

These queries give a lot of insight into how it all hangs together. Some points:

  • Count() is not supported, which is a shame as it is so universally useful.
  • Single(predicate) is not supported - you must use Where(predicate).Single(); this is doubly LOLA, since it contrasts with a bug in LINQ-to-SQL, where the identity-manager can only short-circuit primary-key lookups (avoid hitting the database) if you use .Single(predicate). Oh well; in Entity Framework it doesn't work *at all*, so perhaps I should be grateful.
  • We are limited to homogenous results - i.e. we can't do complex projections over the wire. We can, of course, do simple queries, then use LINQ-to-Objects (with .AsEnumerable() if necessary) to do the complex bits at the client - just remember that all the entity properties are coming back, even if you ignore \most of them.


Exposing LINQ-to-SQL isn't particularly hard; with a few simple tweaks, it generally works. The client tools provide a rich (but not quite complete) set of query tools for consuming ADO.NET Data Services.

Next, we'll see how deep the hole goes, by trying to submit changes to our LINQ-to-SQL ADO.NET Data Service.