ADO.NET Data Services on Silverlight: Using the generated key within the same transaction

Question

We have a Silverlight application that uses WCF Data Services. We want to add logging functionality: when a new row is generated, the primary key for this new row is also recorded in the logging table. The row generation and the logging should occur within the same transaction. The primary keys are generated via the database (using the IDENTITY keyword).

This might best be illustrated with an example. Here, I create a new Customer row, and in the same transaction I write the Customer's primary key to an AuditLog row. This example uses a thick client and the Entity Framework:

    using (var ts = new TransactionScope())
    {
        AuditTestEntities entities = new AuditTestEntities();
        Customer c = new Customer();
        c.CustomerName = "Acme Pty Ltd";
        entities.AddToCustomer(c);
        Debug.Assert(c.CustomerID == 0);
        entities.SaveChanges();
        // The EntityFramework automatically updated the customer object
        // with the newly generated key
        Debug.Assert(c.CustomerID != 0);
        AuditLog al = new AuditLog();
        al.EntryDateTime = DateTime.Now;
        al.Description = string.Format("Created customer with customer id {0}", c.CustomerID);
        entities.AddToAuditLog(al);
        entities.SaveChanges();
        ts.Complete();
    }

It's a trivial problem when developing a thick client using Entity Framework.

However, using Silverlight and ADO.NET data services:

• SaveChanges can only be invoked asynchronously
• I'm not sure TransactionScope is available
• I'm not sure if generated keys can be reflected in the client Edit: According to Alex James they are indeed reflected in the client

So, will this even be possible?

Answer 1

Short Answer: No this is not even possible

Okay... so:

1. Generated Keys are reflected in the client.
2. You can transact one SaveChanges operation by using DataServiceContext.SaveChanges(SaveChangesOption.Batch)

But unfortunately you can't do anything to tie one request to the response of another, and wrap them both in one transaction.

However...

If you change the model by making a CustomerAuditLog method that derives from AuditLog:

// Create and insert customer ...
// Create audit log and relate to un-insert customer
CustomerAuditLog al = new CustomerAuditLog(); 
al.EntryDateTime = DateTime.Now; 
al.Description = string.Format("Created customer with {Customer.ID}");
// assuming your entities implement INotifyPropertyChanging and you are using
// the Data Services Update to .NET 3.5 SP1 to use DataServiceCollection 
// to notify the DataServiceContext that a relationship has been formed. 
//
// If not you will manually need to tell Astoria about the relationship too.
al.Customer = c; 
entities.AddToAuditLog(al); 
entities.SaveChanges();

And having some sort of logic deep in your underlying DataSource or maybe even the database to replace {Customer.ID} with the appropriate value.

You might be able to get it to work, because if two inserts happen in the same transaction and one (CustomerAuditLog) depends on another (Customer) they should be ordered appropriately by the underlying data source.

But as you can see this approach is kind of hacky, you don't want a Type for each possible audit message do you! And ... it might not even work.

Hope this helps

Alex

Data Services Team, Microsoft