Friday 12 December 2008

Astoria and LINQ-to-SQL; modifying data

Our ADO.NET Data Service can now act to create records... but how about changing existing data? Starting simple, we'll now look at removing records from the database. We'll update the test client:

// create it
Employee newEmp = new Employee {
FirstName = "Fred",
LastName = "Jones"};
ctx.AddToEmployees(newEmp);
Console.WriteLine(newEmp.EmployeeID);
ctx.SaveChanges();
Console.WriteLine(newEmp.EmployeeID);

// delete it
ctx.DeleteObject(newEmp);
ctx.SaveChanges();
Console.WriteLine(newEmp.EmployeeID);

Implementing Delete

This time, our DataService<NorthwindDataContext> code (from the previous article) explodes first on the mysterious "GetResource" method. This curious beast is used to resolve a resource from a query. Fortunately, since we are treating "resource" and "instance" as interchangeable, we can simply offload most of this work to the regular provider (LINQ-to-SQL in this case):

public static object GetResource(
DataContext context, IQueryable query,
string fullTypeName)
{
object execResult = query.Provider
.Execute(query.Expression);
return ((IEnumerable) execResult)
.Cast<object>().Single();
}

Here, "Provider.Execute" uses LINQ-to-SQL to build the query; we can't easily predict what data we will get back, but we ultimately expect it to be an enumerable set, with exactly one item - so LINQ-to-Objects can do the rest for us via Cast<T>() and Single().

With this implemented, we now get the largely anticipated error on "DeleteResource"; again this is fairly simple to implement:

public static void DeleteResource(
DataContext context, object targetResource)
{
ITable table = context.GetTable(
targetResource.GetType());
table.DeleteOnSubmit(targetResource);
}

And sure enough, it works; we can verify that the data is removed from the database.

When abstraction attacks

As a minor observation (yet more LOLA), I also notice that attempting (at the client) to query data that has been removed by primary key doesn't behave quite as we might expect:

var ctx = new NorthwindDataContext(BaseUri);
Employee emp = ctx.Employees
.Where(x => x.EmployeeID == id)
.SingleOrDefault();

With an invalid id, this breaks with "Resource not found for the segment 'Employees'."; this applies for any combination of First/Single, with/withot OrDefault, and with/without AsEnumerable() - basically, ADO.NET Data Services sees the primary key, and asserts that it must exist. Something else to remember!

Implementing Update

Interestingly, the combination of "GetResource" (which we implemented for delete) and "SetValue" (which we implemented for create) provides everything we need for simple updates. And sure enough, we can test this from the client. Note that the ADO.NET Data Services client requires us to be explicit about which records we want to update (there is no automatic change tracking):

newEmp.BirthDate = new DateTime(1980,1,1);
ctx.UpdateObject(newEmp); // mark for send
ctx.SaveChanges();

Summary

We're getting there; we now have our 4 fundamental CRUD operations. Next, we'll look at associations between data. We are whittling through the operations, though - we have remaining (not implemented):

  • Association 
    • AddReferenceToCollection
    • RemoveReferenceFromCollection
    • SetReference
  • Batch (rollback) 
    • ClearChanges
    • ResetResource