Disconnected LINQ to SQL(转)

原文：http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/09/14/9811.aspx

It's a common scenario to build an application with multiple physical or logical tiers and a very common example would be where we have something like;

If we imagine that our "web service" just does CRUD on some entity type (say Customer) then we've got a situation where.

1. Initially we have a Customer row in the DB - call it C1.
2. Web Service queries for it.
3. Web Service returns it.
4. Client gets a copy of it. Call it CC1.
5. Client modifies it to produce C2.
6. Client returns C2 to web service.

Now, at step 6 we have a few options;

1. Client returns C2 and only C2. In the middle tier, we have kept no state so we don't know whether there are changes between C1 and C2 and so the best we can hope for is to update all the columns in the DB. Additionally, we don't know whether the row in the DB has been modified since the client read it so we can't check for concurrency violations.
2. Client returns C2 and only C2. In the middle tier, we have kept a copy of C1 and so we can perform a comparison and only update the columns in the DB that have changed. Additionally, because we have the original values we can check for concurrency violations.
3. Client returns C2 and CC1. This is just like case (2) above in that we can perform a comparison and only update the columns in the DB that have changed. Additionally, because we have the original values we can check for concurrency violations.

Case 2 and 3 are essentially the same thing with a tweak as to whether it's the client that "remembers" the original values (which I tend to lean towards) or whether it's the service that "remembers" the original values (which I tend to lean against although it's easy enough to do).

So, really - we only have 2 cases. One where you have the original values and one where you don't.

1 - Where We Don't Have the Original Values

Here's a WCF Service;

[ServiceContract]
class Service
{
[OperationContract]
public Customer GetCustomer(string customerId)
{
Customer c = null;
using (Northwind ctx = new Northwind(Settings.Default.NorthwindConnectionString))
{
ctx.ObjectTrackingEnabled = false;
c = ctx.Customers.Single(cust => cust.CustomerID == customerId);
}
return (c);
}
[OperationContract]
public void InsertCustomer(Customer c)
{
using (Northwind ctx = new Northwind(Settings.Default.NorthwindConnectionString))
{
ctx.Customers.Add(c);
ctx.SubmitChanges();
}
}
[OperationContract]
public void DeleteCustomer(Customer c)
{
using (Northwind ctx = new Northwind(Settings.Default.NorthwindConnectionString))
{
ctx.Customers.Attach(c);
ctx.Customers.Remove(c);
ctx.SubmitChanges();
}
}
[OperationContract]
public void UpdateCustomer(Customer cNow)
{
using (Northwind ctx = new Northwind(Settings.Default.NorthwindConnectionString))
{
ctx.Customers.Attach(cNow, true); // true means "modified".
ctx.SubmitChanges();
}
}
static void Main(string[] args)
{
ServiceHost host = new ServiceHost(typeof(Service),
new Uri("http://localhost:9091/customerService"));
BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.None);
host.AddServiceEndpoint(
typeof(Service),
binding,
string.Empty);
ServiceMetadataBehavior behaviour = new ServiceMetadataBehavior();
behaviour.HttpGetEnabled = true;
host.Description.Behaviors.Add(behaviour);
host.Open();
Console.WriteLine("Waiting for messages...");
Console.ReadLine();
}
}

Note that it's arguable as to whether you'd really want to pass the "whole" Customer into that DeleteCustomer method (actually, it's arguable as to whether you'd want a CRUD service like this at all but that's another story).

Note also that our UpdateCustomer method takes only the current Customer values. That means that it's not going to be able to tell whether the Customer has been changed or not and it's not going to be able to perform concurrency violation checks at the back-end.

That means that I've got to switch the default policy of optimistic concurrency off.

By default LINQ to SQL can generate some pretty long WHERE clauses because it assumes that you want to check every single column in your table for optimistic concurrency violations. This is probably not what you want so (unless you have a timestamp where the default will be to check only that one column) so you probably want to tweak this.

I ran sqlmetal.exe /server:. /database:northwind /code:northwind.cs /pluralize /serialization:Unidirectional and got myself some entity classes that'll play nicely with WCF.

I tweaked the resulting code because I want to switch concurrency checking off completely so I went and visited every property on my generated Customer type and added the [(UpdateCheck=UpdateCheck.None)] to their TableAttribute because, it's not possible for LINQ to SQL to do update checks here when it doesn't have any original values to look at.

I wrote a client that does;

ServiceClient proxy = new ServiceClient();
Customer newCustomer = new Customer()
{
CustomerID = "DISTR",
CompanyName = "My Company",
Address = "My Street",
City = "My City",
ContactName = "My Contact",
ContactTitle = "My Title",
Country = "My Country",
Fax = "My Fax",
Phone = "My Phone",
PostalCode = "My Code"
};
proxy.InsertCustomer(newCustomer);
Console.WriteLine("Inserted customer");
Console.ReadLine();
newCustomer = proxy.GetCustomer("DISTR");
Console.WriteLine("Read customer");
Console.ReadLine();
newCustomer.Phone = "Modified";
proxy.UpdateCustomer(newCustomer);
Console.WriteLine("Updated customer");
Console.ReadLine();
proxy.DeleteCustomer(newCustomer);
Console.WriteLine("Deleted customer");

 

and that all works fine. In terms of the SQL that goes to the database I see;

and then I get my select;

 

and then my update (note that every column is being updated and the WHERE clause is purely based on primary key here - i.e. "Last Write Wins", no concurrency checking);

 

and then my delete;

 

so, that all seems entirely reasonable to me.

2 - Where We Have Original Values

Now, here's a second situation where we have original values. I'm going to ask the client to keep them for me and pass them back to me. Now, you could debate about how much this matters for deletes because you could argue that a delete might be a delete regardless of whether someone else has changed the record in the meantime or not but I'll go with the model where this also matters for deletes.

Here's the service (I've left out the hosting code as it's the same);

  [OperationContract]
public Customer GetCustomer(string customerId)
{
Customer c = null;
using (Northwind ctx = new Northwind(Settings.Default.NorthwindConnectionString))
{
ctx.ObjectTrackingEnabled = false;
c = ctx.Customers.Single(cust => cust.CustomerID == customerId);
}
return (c);
}
[OperationContract]
public void InsertCustomer(Customer c)
{
using (Northwind ctx = new Northwind(Settings.Default.NorthwindConnectionString))
{
ctx.Customers.Add(c);
ctx.SubmitChanges();
}
}
[OperationContract]
public void DeleteCustomer(Customer oldCustomer, Customer newCustomer)
{
using (Northwind ctx = new Northwind(Settings.Default.NorthwindConnectionString))
{
ctx.Customers.Attach(newCustomer, oldCustomer);
ctx.Customers.Remove(newCustomer);
ctx.SubmitChanges();
}
}
[OperationContract]
public void UpdateCustomer(Customer oldCustomer, Customer newCustomer)
{
using (Northwind ctx = new Northwind(Settings.Default.NorthwindConnectionString))
{
ctx.Customers.Attach(newCustomer, oldCustomer);
ctx.SubmitChanges();
}
}

and then the client is the same except I've had to write a function to copy a customer record so that I can maintain a "current" value and an "original" value (I didn't include this code as it's tedious - sometimes I find myself wishing that ICloneable was a bit more central in the .NET world);

   static void Main(string[] args)
{
Console.WriteLine("Waiting for keypress...");
Console.ReadLine();
ServiceClient proxy = new ServiceClient();
Customer newCustomer = new Customer()
{
CustomerID = "DISTR",
CompanyName = "My Company",
Address = "My Street",
City = "My City",
ContactName = "My Contact",
ContactTitle = "My Title",
Country = "My Country",
Fax = "My Fax",
Phone = "My Phone",
PostalCode = "My Code"
};
proxy.InsertCustomer(newCustomer);
Console.WriteLine("Inserted customer");
Console.ReadLine();
newCustomer = proxy.GetCustomer("DISTR");
Customer originalCustomer = CopyCustomer(newCustomer);
Console.WriteLine("Read customer");
Console.ReadLine();
newCustomer.Phone = "Modified";
proxy.UpdateCustomer(newCustomer, originalCustomer);
Console.WriteLine("Updated customer");
Console.ReadLine();
proxy.DeleteCustomer(newCustomer, newCustomer);
Console.WriteLine("Deleted customer");
Console.ReadLine();
}

Ok, now the first time I ran this I left it with all the optimistic concurrency checking switched off. So, from my SQL Profiler trace I get to see the insert;

then we do the select;

then we do the update. Note that because we have original values we now get an update statement that is only changing the value of the "Phone" column but, because we're not doing any concurrency checking, the WHERE clause is purely based on the primary key.

and the delete;

So, that all looks perfectly reasonable to me.

Now, what if I want to switch on checking for concurrency violations? I have my original values in play so that's definitely possible. All I have to do is decide which column (or combination of columns) I want to have checked for optimistic concurrency purposes.

The simplest (but not always most pragmatic) way of doing this is to add a timestamp to the table and then tell the framework about it by appying the IsVersion property to the TableAttribute that is applied to that property.

I won't do that here.

For Customer, I'll say that if the CompanyName and ContactName haven't changed then as far as I'm concerned the record hasn't been changed by anyone else.

So, I go and attribute those columns to add the Table[UpdateCheck=UpdateCheck.Always] to the CompanyName and ContactName properties of my Northwind generated types. I don't have to use UpdateCheck.Always. I can also use UpdateCheck.WhenChanged which means "Hey, only add this value to the WHERE clause if I've actually updated it myself, otherwise don't worry about it".

So, I re-run with those things in place and my SQL looks exactly the same until we hit the UPDATE;

We're now including the CompanyName and the ContactName along with the primary key because I've asked for those to be checked for concurrency violations. Same deal with the DELETE;

although, as I said previously, you could argue about whether you care for a DELETE.

So, that all seems entirely reasonable to me.