Linq Table Attach

xblues

原文链接 http://geekswithblogs.net/michel ... 7/12/17/117791.aspx

The ability to use live Linq queries rightin your UI makes for great demo's, but it doesn't bear a strikingresemblance to a real-world, professional application which usestiers.  In traditional n-tier applications, you want to have a strong"separation of concerns" and encapsulate your business layer, your datalayer, and your UI layer distinctly.  One of the nice things about Linqis that the flexibility is huge.  If you want to do live queries inyour UI, fine.  If you want to encapsulate Linq queries in your datalayer, that's fine too.

实时Linq查询可以被直接应用在UI层，做出的演示是相当强大的，但却不实际。传统的N层应用，需要把应用分成多个层，商业层，数据层，界面层等等。Linq强大的灵活性使其能够满足这一要求，你既可以把Linq直接写在界面中，也可以把Linq查询写在数据层中。

Having said that, the biggest problem I faced when using the RTM for the first time was trying to update an object that had been created by a "different" data contact.  I continually ran into one of these dreaded exceptions:  

"System.Invalid Operation Exception: An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy." The other one was: "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext."

Microsoft has documentation here that is meant to describe how to properly implement this scenario.  The key to the update are these bullet points:  

LINQ to SQL supports updates in these scenarios involving optimistic concurrency:
- Optimistic concurrency based on timestamps or Row Version numbers.
- Optimistic concurrency based on original values of a subset of entity properties.
- Optimistic concurrency based on the complete original and modified entities.

But they never really gave any concrete example of implementation.  So here is a quick example of how to avoid this.
OK, here is my (ridiculously simple) table:

CREATE TABLE Contacts(
ContactID int IDENTITY(1,1) NOT NULL,
FirstName varchar(50),
LastName varchar(50),
[Timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)
)

Next, drag out the table from the Server Explorer onto a dbml surface:

                               
登录/注册后可看大图

If right-click on the Timestamp column in the dbml above and select "Properties", you'll see this Properties window:

                               
登录/注册后可看大图

Notice the Auto Generated Value and Time Stamp properties are both set to true. This is key.
Now let's suppose I create a ContactManager class that is going tobe my public API that will encapsulate all of my CRUD functionality. (In fact, I can make my Linq data context classes all Internal so my UI truly does not know about them)

1:  public static class ContactManager   
2:  {   
3:      public static Contact GetContact(int contactID)   
4:      {   
5:          using (ContactsDataContext dataContext = new ContactsDataContext())   
6:          {   
7:              return dataContext.Contacts.SingleOrDefault(c => c.ContactID == contactID);   
8:          }   
9:      }  
10:     
11:      public static void SaveContact(Contact contact)  
12:      {  
13:          using (ContactsDataContext dataContext = new ContactsDataContext())  
14:          {  
15:              if (contact.ContactID == 0)  
16:              {  
17:                  dataContext.Contacts.InsertOnSubmit(contact);  
18:              }  
19:              else  
20:              {  
21:                  dataContext.Contacts.Attach(contact, true);  
22:              }  
23:              dataContext.SubmitChanges();
24:          }  
25:      }  
26:  }

Notice that I'm disposing my data context each time so I truly can support a stateless n-tier service.  Also, notice I am calling the Attach() method (line 21 above) and giving the second parameter as"true" - meaning, attach as modified.  I have to call Attach() here because the original data context that created my object isn't around anymore.  I have to attach it as modified so that the framework will understand that my intent is to perform an update here.  Additionally,a look at the data context's Log property shows the SQL that was actually emitted during run-time execution:

1:  UPDATE [dbo].[TempContacts]   
2:  SET [FirstName] = @p2, [LastName] = @p3   
3:  WHERE ([ContactID] = @p0) AND ([Timestamp] = @p1)

So, the timestamp is taken into account as well so that full Optimistic concurrency is supported.

xblues

原文链接 http://geekswithblogs.net/michel ... 7/12/18/117823.aspx

如果不想使用timestamps字段，其他可以选用的有last updated字段，或者行号字段。比较实用timestamp字段稍微复杂一点，因为需要设计一个触发器，在每次数据更新的时候更新字段值。

In a previous post here,I showed an example of using the Attach() method in conjunction with a Timestamp column in your database table.  In listing options that are supported, Microsoft's documentation states: "Optimistic concurrency based on timestamps or Row Version numbers."  So what are some alternatives to using a Timestamp column in your SQL Server database? It turns out, this is pretty simple.  Two other alternatives are using a DateTime or a unique identifier column.
DateTime Last Updated

The key here is to create a LastUpdated DateTime column with a default value of getdate() and an AFTER UPDATE trigger which inserts the current getdate() any time there is a modification.

1:  CREATE TABLE Contacts(   
2:   ContactID int IDENTITY(1,1) NOT NULL,   
3:   FirstName varchar(50),   
4:   LastName varchar(50),   
5:   LastUpdated datetime NOT NULL default (getdate()),  
6:   CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)   
7:  )   
8:  GO   
9:     
10:  CREATE TRIGGER trig_ContactsVersion  
11:  ON Contacts  
12:  AFTER UPDATE  
13:  AS  
14:  BEGIN  
15:      UPDATE    Contacts  
16:      SET    LastUpdated = getdate()  
17:      WHERE    ContactID IN (SELECT ContactID FROM inserted);  
18:  END;

And the corresponding properties must be configured:

                               
登录/注册后可看大图

  

Unique Identifier
The key here is to create a Version unique identifier column with a default value of newid() and an AFTER UPDATE trigger which inserts anew guid any time there is a modification.

1:  CREATE TABLE Contacts(   
2:   ContactID int IDENTITY(1,1) NOT NULL,   
3:   FirstName varchar(50),   
4:   LastName varchar(50),   
5:   Version uniqueidentifier NOT NULL default (newid()),   
6:   CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)   
7:  )   
8:      
9:  CREATE TRIGGER trig_ContactsVersion  
10:  ON Contacts  
11:  AFTER UPDATE  
12:  AS  
13:  BEGIN  
14:      UPDATE    Contacts  
15:      SET        Version = newid()  
16:      WHERE    ContactID IN (SELECT ContactID FROM inserted);  
17:  END;

And the corresponding properties must be configured:

                               
登录/注册后可看大图

So you actually have a lot of flexibility here.  If you don't like the SQL Server Timestamp data type, no problem.  Just use yourOptimistic concurrency implementation of choice.  Of course, these implementations can all be used with a stored procedure approach as well.

xblues

如果上述三种中的任何一种字段出现在用户编辑界面，应该使用 Eval 代替 Bind，否则会发生辅助表示字段值被改变，而产生更新错误。

不用
<asp: Label ID="Label3" runat="server" Text='<%# Bind("LastUpdated") %>'></asp: Label>

用
<asp: Label ID="Label1" runat="server" Text='<%# Eval("LastUpdated") %>'></asp: Label>

xblues

原文链接  http://geekswithblogs.net/michelotti/archive/2007/12/27/118022.aspx

注意看下图，Addresses表实际上还链接者两个字典表，这样更新Addresses表的时候，由于关联的关系，Linq会自动去更新那两个字典表（而实际上字典表是不需要，也不能够在这里被更新的），所以Linq会报错。
解决方法是，把这些字典表单独放到另外一个DataContext文件中。

In a previous post here,I discussed implementation of Attaching Linq entities to aDataContext.  In that post, I showed an implementation of utilizing aDetach() method that I originally based on this post here. The implementation boils down to the need to reset EntityRef<>references back to their default - otherwise, it will try to attach theparent objects (which is often not the goal of your code as this isoften just reference data). Consider the DataContext below:

                               
登录/注册后可看大图

The fundamental problem here is that State and AddressType shouldNOT be in this data context at all!  Doing so causes the auto-generatedclasses to include EntityRef<> references to them in the Addressclass.  In my actual Address business objects, all I really care aboutit the StateID.  The database takes care of enforcing the foreign keyto the State table.  My application does need to query the State tabledirectly for populating the State drop down list but it should be inits own data context.  So I need to have 2 differentDataContexts.  The first should contain only Contact and Address fromthe diagram above. The second (call it ReferenceDataContext for myState and AddressType reference data) should simply look like this:

                               
登录/注册后可看大图

Bottom line: don't stuff everything in your database into a singleDataContext dumping ground - be mindful of the context.  If you have aDataContext where the only items you're updating contain businessentity and not their reference data then do not include them - this ismuch simplier and allows you to avoid having to write Detach() methods at all!

xblues

Attach an entity that is not new, perhaps having been loaded from another DataContext.

原文链接： http://geekswithblogs.net/michel ... 7/12/25/117984.aspx

当然如果一定要把字典表放在同一个DataContext文件中，那就只有动用Detach()方法了，在更新之前使用Detach方法先把字典表Detach掉以后，再更新。

This exception using the Linq Attach() method is somewhat perplexing at first:

System.Not Supported Exception: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.
This blog post here *sortof* pointed me in the right direction. But I found the *WHY* confusing and I found the example confusing.  The following is my implementation of the suggested solution from the previous post.  First, consider the following diagram:

                               
登录/注册后可看大图

The Contact is the primary object and it has a collection of Addresses.  The State and AddressType tables are simply master tablesfor reference data. Next we create a public method to handle database modifications:

1:  public static void SaveContact(Contact contact)   
2:  {   
3:      using (PimDataContext dataContext = CreateDataContext())   
4:      {   
5:          if (contact.ContactID == 0)   
6:          {   
7:              dataContext.Contacts.InsertOnSubmit(contact);   
8:          }   
9:          else  
10:          {              
11:              dataContext.Contacts.Attach(contact, true);  
12:          }  
13:          dataContext.SubmitChanges();  
14:      }  
15:  }

This code works fine for inserts of a Contact with or without any addresses in its child list of addresses.  However, it only works for updates if the contact does not have any child addresses.  If it does have addresses, then it throws the dreaded:"System.Not Supported Exception: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported."  When I blindly followed the example from the previously referenced post, I essentially set everything in the Contact object to the default.  For example, this._Addresses = default(EntitySet<Address>);. But this doesn't do me much good because if I actually made a modification to any addresses, then those changes are now lost as I'msetting the collection of Addresses back to a default empty Entity Set reference.  Additionally, I found myself asking the question, "WHICH entity is it complaining about?" Everything works fine when it's a stand-alone Contact object so it didn't seem feasible for it to be complaining about the Contact object. So I concluded it must be*something* in the Address object - but what?
A close examination of the generated code for the Address object showed that their were actually 3 EntityRef<> members - Contact,AddressType, and State.  So it seemed it was actually trying to attach my AddressType and State entity (which was never my intent).  Using that information, I found I could make everything work by setting just the EntityRef objects back to the default reference:

1:  public partial class Contact   
2:  {   
3:      public void Detach()  
4:      {   
5:          foreach (Address address in this.Addresses)  
6:          {   
7:              address.Detach();  
8:          }   
9:      }  
10:  }  
11:     
12:  public partial class Address  
13:  {  
14:      public void Detach()  
15:      {  
16:          this._AddressType = default(EntityRef<AddressType>);  
17:          this._State = default(EntityRef<State>);  
18:      }  
19:  }

This allowed my calling code to now just look like this and everything now worked perfectly:

1:  public static void SaveContact(Contact contact)   
2:  {   
3:      using (PimDataContext dataContext = CreateDataContext())   
4:      {   
5:          if (contact.ContactID == 0)   
6:          {   
7:              dataContext.Contacts.InsertOnSubmit(contact);   
8:          }   
9:          else  
10:          {  
11:              contact.Detach();  
12:               
13:              dataContext.Contacts.Attach(contact, true);  
14:              dataContext.Addresses.AttachAll(contact.Addresses, true);  
15:          }  
16:          dataContext.SubmitChanges();  
17:      }  
18:  }

Notice on line 11 I am calling the new Detach() method.  Also notice that I'm line 14, I am now explicitly calling the AttachAll() method for the collection of Addresses.
Looking at the final solution, it all now seems simple. But the troubleshooting that went in to getting there was not simple. While trivial, having the write Detach() methods for all my entity objects is not particularly appealing. Using Linq with stored procedures and explicit function calls would have eliminated much of the mystery as to what was going on behind the scenes trying to figure out why this exception was being thrown (though obviously more work writing stored procedures). These are the types of things that, so far, have me concluding two things: 1) Linq is extremely flexible and powerful, and2) I still prefer using Linq with my own Stored Procedures.

Update: check out this post here for an example implementation that is even simplier and does not require Detach() methods at all.

xblues

原文链接： http://geekswithblogs.net/michel ... 7/12/30/118076.aspx

CRUD集大成！

Recently I've done a series of posts all related to using Linq in a tiered application:

• Linq Table Attach()
• Linq Table Attach() based on timestamp or row version
• Handling Attach() with child entity objects
• Exploring DataContext in more depth

The various posts (which have been influenced by this MSDN article)have focused on a DataContext that looks like the diagram below.  TheContact class generated has a child collection property of Addresseswhich is of type EntitySet<Address>.  This distinction isimportant because the example deals with a complex object that has acollection of child objects rather than a single object with a bunch ofprimitives.  You must take care to handle the items in the childcollection properly.

                               
登录/注册后可看大图

The SaveContact() method looks like this:
1:  public static void SaveContact(Contact contact)   
2:  {   
3:      using (PimDataContext dataContext = CreateDataContext())   
4:      {   
5:          if (contact.ContactID == 0)   
6:          {   
7:              dataContext.Contacts.InsertOnSubmit(contact);   
8:          }   
9:          else  
10:          {  
11:              dataContext.Contacts.Attach(contact, true);  
12:              dataContext.Addresses.AttachAll(contact.Addresses, true);  
13:          }  
14:          dataContext.SubmitChanges();  
15:      }  
16:  }

This code works fine when passing a Contact object into your data access tier for any of the following conditions:

• A brand new Contact to be inserted with no child addresses.
• A brand new Contact to be inserted with one or multiple child addresses.
• An existing Contact to be updated with no child addresses.
• An existing Contact to be updated with one or multiple existing child addresses to be updated.

At first, all seems well.  The problem is that there are actually a couple of gaping holes here. Consider this scenario:

• A user of the application creates a brand new Contact (with no addresses) and the code inserts it just fine.
• Theuser then goes to update the existing Contact they previously created.But the change they want to make is the ADD a new address to thisexisting contact.

The above code will throw this exception:  "System.Data.Linq.ChangeConflictException: Row not found or changed."
This is obviously bad.  What is happening is that the AttachAll() onthe contact's Addresses is failing because the Attach methods are meantto be used for updates to rows that are already existing in thedatabase.  In this case, although the Contact is existing, this is abrand new Address that we are trying to insert. So what we need to dois to call AttachAll() if there are existing Addresses or theInsertAllOnSubmit() method if they are brand new addresses. One simpleway to accomplish this is by adding a property to the Address via apartial class:
   1:  public partial class Address   2:  {   3:      public bool IsNew   4:      {   5:          get   6:          {   7:              return this.Timestamp == null;   8:          }   9:      }  10:  }
Here we've utilized our Timestamp column (used for Optimisticconcurrency) to determine if this is brand or or existing (but thiscould alternatively have been done with a simple Boolean). This meansour SaveContact() method can now look like this:
   1:  public static void SaveContact(Contact contact)   2:  {   3:      using (PimDataContext dataContext = CreateDataContext())   4:      {   5:          if (contact.ContactID == 0)   6:          {   7:              dataContext.Contacts.InsertOnSubmit(contact);   8:          }   9:          else  10:          {  11:              dataContext.Contacts.Attach(contact, true);  12:              dataContext.Addresses.AttachAll(contact.Addresses.Where(a => !a.IsNew), true);  13:              dataContext.Addresses.InsertAllOnSubmit(contact.Addresses.Where(a => a.IsNew));  14:          }  15:          dataContext.SubmitChanges();  16:      }  17:  }
Now the code works fine regardless of whether you're adding a new address to an existing contact.
So now, we're surely good to go, right?  Unfortunately, we still have one big gaping hole. Consider this next scenario:

• A user creates a brand new contact and this contact has an address. User saves and everything is fine.
• Theuser then goes to update the existing Contact they previously created.But the change they want to make is the DELETE the existing addressfrom this existing contact.

Now you've got a couple of different choices for how you want tohandle this scenario. IF you have control over the client, you can keeptrack of whether the address was deleted or not. You can add a newIsDeleted property to your Address partial class:
   1:  public partial class Address   2:  {   3:      public bool IsNew   4:      {   5:          get   6:          {   7:              return this.Timestamp == null && !this.IsDeleted;   8:          }   9:      }  10:    11:      public bool IsDeleted { get; set; }  12:  }
But again, the responsibility is on the client to keep track ofcorrectly setting this property. While it is not difficult, it is nottotally trivial either. If you do that, then you can now update yourSaveContact() method to this:
   1:  public static void SaveContact(Contact contact)   2:  {   3:      using (PimDataContext dataContext = CreateDataContext())   4:      {   5:          if (contact.ContactID == 0)   6:          {   7:              dataContext.Contacts.InsertOnSubmit(contact);   8:          }   9:          else  10:          {                      11:              dataContext.Contacts.Attach(contact, true);  12:              dataContext.Addresses.AttachAll(contact.Addresses.Where(a => !a.IsNew), true);  13:              dataContext.Addresses.InsertAllOnSubmit(contact.Addresses.Where(a => a.IsNew));  14:              dataContext.Addresses.DeleteAllOnSubmit(contact.Addresses.Where(a => a.IsDeleted));  15:          }  16:          dataContext.SubmitChanges();  17:      }  18:  }
You now finally have a SaveContact() method that takes care of allpermutations.  And all in all, it's a pretty straightforward 10 linesof code.
If you don't have very much control over the client you might have asituation where the absence of an Address could mean either it wasdeleted or it never existed to begin with. In that scenario, you'regoing to have to blindly do delete/insert on the addresses (i.e., you'dnever run an update on an Address) which would most likely have to relyon looping over the address types and running a line of code like thisfor each iteration:
dataContext.ExecuteCommand("DELETE FROM addresses WHERE ContactID={0} and AddressTypeID={1}", contactID, addressTypeID);While it works, it's pretty clumsy and bordering on a code smellwhereas the first solution was much more graceful. But either way, Icontinue to be impressed with the flexibility of implementation choicesthat Linq provides.