Friday 12 December 2008

Astoria and LINQ-to-SQL; creating data

Previously, we looked at how to get LINQ-to-SQL talking to ADO.NET Data Services to query data - but how about editing data? This again just works for Entity Framework via the metadata model. So what about LINQ-to-SQL? Lets give it a whirl... we'll start with some simple code at the client to create a new Employee:

Employee newEmp = new Employee {
FirstName = "Fred",
LastName = "Jones"};

Give that a go, and boom! "The data source must implement IUpdatable to support updates.". IUpdatable is the ADO.NET Data Services interface that allows a REST service to talk back to the model... and it isn't trivial:


Hmm... lots to do. We should also note that the IUpdatable model uses two types of object:

  1. resources - an opaque, implementation-defined notion of an instance (anything you want that allows you to map to an instance)
  2. instances - actual entity objects (such as Customer)

LINQ-to-SQL is already highly object oriented (and to do anything useful with the instance we'll need the actual object from the DataContext), so for simplicity I'll simply treat these as one-and-the-same; our opaque resource will be the instance itself.

Defining our own Data Context

This is one of those cases where the simplest approach is to use inheritance - i.e. create a WebDataContext class (that uses explicit implementation to satisfy the interface) from which we can inherit our own DataContext. Note that since people may already have their own inheritance trees, I'll keep the implementation code separate, allowing us to use the code even if we haven't subclassed. Here's a very small part of it:


Luckily, LINQ-to-SQL allows you to control the base-class of the data-context, so change the dbml to point at our new WebDataContext (using the namespace-qualified name), and it will update the generated code accordingly. In the designer this is the "Base Class" when you don't have any table selected. In the xml, it is the "/Database/@BaseType" property.

Remembering that we haven't actually coded any of it yet, fire it up. This time, we get an error at the client: "The method or operation is not implemented.", which we expected; the debugger conveniently breaks at the offending methods, letting us locate them.

Implementing Create

The first thing that breaks is CreateResource; we need to implement this to create the new object to use. So we'll add a static method to our WebUpdateable class, and point our base-class at it. CreateResource supplies a "containerName" - this is essentially a property name for the data we are adding. Creating an object of a known type is easy enough - but what type? We could use the provided "fullTypeName" (and I'll come back to this later), but a simple approach might be simply to look at the container itself: if the container is an IQueryable<T>, we probably want to create a T. I say probably because LINQ-to-SQL supports discriminator-based inheritance, but we'll park that issue for now...

For re-use, it is useful to write a method that uses reflection to identify the type from IQueryable<T>:

static Type GetContainedType(
DataContext context, string containerName)
PropertyDescriptor property = TypeDescriptor
if(property == null) throw new ArgumentException(
"Invalid container: " + containerName);

return (
from i in property.PropertyType.GetInterfaces()
where i.IsGenericType
&& i.GetGenericTypeDefinition() ==
typeof (IQueryable<>)
select i.GetGenericArguments()[0]

With this in place, we can now create objects, and (importantly) add them to the table; fortunately, DataContext allows you to work with tables by type:

public static object CreateResource(
DataContext context, string containerName,
string fullTypeName)
Type type = GetContainedType(context,
// TODO: anything involving inheritance
ITable table = context.GetTable(type);
object obj = Activator.CreateInstance(type);
return obj;

Then all we need to do is hook that into our WebDataContext base-class:

object IUpdatable.CreateResource(
string containerName, string fullTypeName)
return WebUpdatable.CreateResource(
this, containerName, fullTypeName);

Note that all of the interface implementations in WebDataContext will be shallow methods that forward to the static methods in WebUpdatable, so I won't repeat them.

Run that, and this time it breaks at SetValue; ADO.NET Data Services wants us to update the object-model with the values it supplies. Fortunately, this is very simple via reflection (or TypeDescriptor), so I'll show the implementation and the interface implementation together (and we'll cover GetValue at the same time):

public static void SetValue(
DataContext context, object targetResource,
string propertyName, object propertyValue)

public static object GetValue(
DataContext context, object targetResource,
string propertyName)
return TypeDescriptor.GetProperties(

The next time we try the service, it breaks at SaveChanges; again, this is simple to implement with LINQ-to-SQL:

public static void SaveChanges(
DataContext context)

After this, ResolveResource breaks; for data inserts, this can be implemented trivially:

public static object ResolveResource(
DataContext context, object resource)
return resource;


Finally, we can validate (at the client) that our data is saved:


And hoorah! It did indeed save; we can look in the database to see the new records.


We've now truly dipped our toe in the ADO.NET Data Services pool; we can create data! Always useful. Next, we'll look at modifying the records we have just added.