When learning LINQ, I was almost struck down by a difficulty, which is the update database operation you see in the title. Now I will take you step by step into this quagmire. Please prepare your bricks and saliva, follow me.
Let's start with the simplest case. Let's take the Northwind database as an example. When you need to modify the ProductName of a product, you can directly write the following code on the client:
// List 0NorthwindDataContext db = new NorthwindDataContext();
Product product = db.Products.Single(p => p.ProductID == 1);
product.ProductName = "Chai Changed";
db.SubmitChanges();
Test it and the update is successful. However, I believe that such code will not appear in your projects, because it is simply impossible to reuse. Okay, let's refactor it and extract it into a method. What should the parameters be? is the new product name and the product ID to be updated. Well, that seems to be the case.
public void UpdateProduct(int id, string productName)
{
NorthwindDataContext db = new NorthwindDataContext();
Product product = db.Products.Single(p => p.ProductID == id);
product.ProductName = productName;
db.SubmitChanges();
}In actual projects, we cannot just modify the product name. Other fields of Product are also subject to modification. Then the signature of the UpdateProduct method will become as follows:
public void UpdateProduct(int id,
string productName,
int suplierId,
int categoryId,
string quantityPerUnit,
decimal unitPrice,
short unitsInStock,
short unitsOnOrder,
short reorderLevel) Of course, this is just a simple database. In actual projects, it is not uncommon to have twenty, thirty or even hundreds of fields. Who can tolerate such methods? If you write like this, what does the Product object do?
That's right, use Product as a parameter of the method and throw the annoying assignment operation to the client code. At the same time, we extracted the code for obtaining the Product instance to form the GetProduct method, and put the methods related to database operations into a ProductRepository class that is specifically responsible for dealing with the database. Oh yeah, SRP!
// List 1
// ProductRepository
public Product GetProduct(int id)
{
NorthwindDataContext db = new NorthwindDataContext();
return db.Products.SingleOrDefault(p => p.id == id);
}
public void UpdateProduct(Product product)
{
NorthwindDataContext db = new NorthwindDataContext();
db.Products.Attach(product);
db.SubmitChanges();
}
//Client code
ProductRepository repository = new ProductRepository();
Product product = repository.GetProduct(1);
product.ProductName = "Chai Changed";
repository.UpdateProduct(product);
Here I use the Attach method to attach an instance of Product to other DataContext. For the default Northwind database, the result of this is the following exception:
// Exception 1 NotSupportException:
Attempted Attach or Add entity, the entity is not a new entity and may have been loaded from another DataContext. This operation is not supported.
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 Looking at MSDN we know that when serializing entities to the client, these entities are detached from their original DataContext. The DataContext no longer tracks changes to these entities or their associations with other objects. If you want to update or delete data at this time, you must use the Attach method to attach the entity to the new DataContext before calling SubmitChanges, otherwise the above exception will be thrown.
In the Northwind database, the Product class contains three related classes (ie, foreign key associations): Order_Detail, Category and Supplier. In the above example, although we Attach the Product, there is no Attach class associated with it, so a NotSupportException is thrown.
So how to associate classes related to Product? This may seem complicated, even for a simple database like Northwind. It seems that we must first obtain the original classes of Order_Detail, Category, and Supplier related to the original Product, and then Attach them to the current DataContext respectively, but in fact, even if we do this, NotSupportException will be thrown.
So how to implement the update operation? For simplicity, we delete other entity classes in Northwind.dbml and only keep Product. In this way, we can start the analysis from the simplest case.
After deleting other classes due to problems, we executed the code in List 1 again, but the database did not change the name of the product. By looking at the overloaded version of the Attach method, we can easily find the problem.
The Attach(entity) method calls the Attach(entity, false) overload by default, which will attach the corresponding entity in an unmodified state. If the Product object has not been modified, then we should call this overloaded version to attach the Product object to the DataContext in an unmodified state for subsequent operations. At this time, the status of the Product object is "modified", and we can only call the Attach(entity, true) method.
So we changed the relevant code in List 1 to Attach(product, true) and see what happened?
// Exception 2 InvalidOperationException:
If an entity declares a version member or does not have an update check policy, it can only be attached as a modified entity without the original state.
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.
LINQ to SQL uses the RowVersion column to implement the default optimistic concurrency check, otherwise the above error will occur when attaching entities to the DataContext in a modified state. There are two ways to implement the RowVersion column. One is to define a timestamp type column for the database table, and the other is to define the IsVersion=true attribute on the entity attribute corresponding to the primary key of the table. Note that you cannot have the TimeStamp column and the IsVersion=true attribute at the same time, otherwise an InvalidOprationException will be thrown: The members "System.Data.Linq.Binary TimeStamp" and "Int32 ProductID" are both marked as row versions. In this article, we use the timestamp column as an example.
After creating a column named TimeStamp and type timestamp for the Products table, drag it back into the designer, and then execute the code in List 1. Thank God, it finally worked.
Now, we drag the Categories table into the designer. I learned the lesson this time and first added the timestamp column to the Categories table. After testing it, it turned out to be the error in Exception 1 again! After deleting the timestamp column of Categories, the problem remains. Oh my god, what exactly is done in the terrible Attach method?
Oh, by the way, there is an overloaded version of the Attach method, let's try it.
public void UpdateProduct(Product product)
{
NorthwindDataContext db = new NorthwindDataContext();
Product oldProduct = db.Products.SingleOrDefault(p => p.ProductID == product.ProductID);
db.Products.Attach(product, oldProduct);
db.SubmitChanges();
} Or Exception 1 error!
I will fall! Attach, Attach, what happened to you?
To explore the LINQ to SQL source code, we use Reflector's FileDisassembler plug-in to decompile System.Data.Linq.dll into cs code and generate project files, which helps us find and locate it in Visual Studio.
When is Exception 1 thrown?
We first find the information described in Exception 1 from System.Data.Linq.resx and get the key "CannotAttachAddNonNewEntities", then find the System.Data.Linq.Error.CannotAttachAddNonNewEntities() method, find all references to this method, and find that there are two This method is used in three places, namely the StandardChangeTracker.Track method and the InitializeDeferredLoader method.
We open the code of Table.Attach(entity, bool) again, and as expected we find that it calls the StandardChangeTracker.Track method (the same is true for the Attach(entity, entity) method):
trackedObject = this.context.Services.ChangeTracker.Track(entity, true); In the Track method, the following code throws Exception 1:
if (trackedObject.HasDeferredLoaders)
{
throw System.Data.Linq.Error.CannotAttachAddNonNewEntities();
}So we turn our attention to the StandardTrackedObject.HasDeferredLoaders property:
internal override bool HasDeferredLoaders
{
get
{
foreach (MetaAssociation association in this.Type.Associations)
{
if (this.HasDeferredLoader(association.ThisMember))
{
return true;
}
}
foreach (MetaDataMember member in from p in this.Type.PersistentDataMembers
where p.IsDeferred && !p.IsAssociation
select p)
{
if (this.HasDeferredLoader(member))
{
return true;
}
}
return false;
}
} From this we can roughly deduce that as long as there are lazy loaded items in the entity, the Attach operation will throw Exception 1. This is exactly in line with the scenario where Exception 1 occurs - the Product class contains lazy-loaded items.
Then a way to avoid this exception has emerged - remove the items that need to be delayed loaded in the Product. How to remove it? You can use DataLoadOptions to load immediately, or set items that require lazy loading to null. But the first method didn't work, so I had to use the second method.
// List 2
class ProductRepository
{
public Product GetProduct(int id)
{
NorthwindDataContext db = new NorthwindDataContext();
return db.Products.SingleOrDefault(p => p.ProductID == id);
}
public Product GetProductNoDeffered(int id)
{
NorthwindDataContext db = new NorthwindDataContext();
//DataLoadOptions options = new DataLoadOptions();
//options.LoadWith<Product>(p => p.Category);
//db.LoadOptions = options;
var product = db.Products.SingleOrDefault(p => p.ProductID == id);
product.Category = null;
return product;
}
public void UpdateProduct(Product product)
{
NorthwindDataContext db = new NorthwindDataContext();
db.Products.Attach(product, true);
db.SubmitChanges();
}
}
//Client code
ProductRepository repository = new ProductRepository();
Product product = repository.GetProductNoDeffered(1);
product.ProductName = "Chai Changed";
repository.UpdateProduct(product);
When is Exception 2 thrown?
Following the method in the previous section, we quickly found the code that threw Exception 2. Fortunately, there was only this one in the entire project:
if (asModified && ((inheritanceType.VersionMember == null) && inheritanceType.HasUpdateCheck))
{
throw System.Data.Linq.Error.CannotAttachAsModifiedWithoutOriginalState();
}
As you can see, when the second parameter asModified of Attach is true, does not contain the RowVersion column (VersionMember=null), and contains an update check column (HasUpdateCheck), Exception 2 will be thrown. The code of HasUpdateCheck is as follows:
public override bool HasUpdateCheck
{
get
{
foreach (MetaDataMember member in this.PersistentDataMembers)
{
if (member.UpdateCheck != UpdateCheck.Never)
{
return true;
}
}
return false;
}
}This is also consistent with our scenario - the Products table does not have a RowVersion column, and in the code automatically generated by the designer, the UpdateCheck properties of all fields are the default Always, that is, the HasUpdateCheck property is true.
The way to avoid Exception 2 is even simpler, add a TimeStamp column to all tables or set the IsVersion=true field on the primary key fields of all tables. Since the latter method modifies the automatically generated classes and can be overwritten by new designs at any time, I recommend using the former method.
How to use the Attach method?
After the above analysis, we can find out two conditions related to the Attach method: whether there is a RowVersion column and whether there is a foreign key association (that is, items that need to be lazy loaded). I summarized these two conditions and the usage of several overloads of Attach into a table. When looking at the table below, you need to be fully mentally prepared.
serial number
Attach method
Whether the RowVersion column has an associated description
1 Attach(entity) No No No modification
2 Attach(entity) No Yes NotSupportException: An Attach or Add entity has been attempted. The entity is not a new entity and may be loaded from other DataContexts. This operation is not supported.
3 Attach(entity) Whether there is no modification
4 Attach(entity) is not modified. Same as 2 if the subset does not have a RowVersion column.
5 Attach(entity, true) No No InvalidOperationException: If the entity declares a version member or has no update check policy, it can only be attached as a modified entity without original state.
6 Attach(entity, true) No Yes NotSupportException: An Attach or Add entity has been attempted. The entity is not a new entity and may be loaded from other DataContexts. This operation is not supported.
7 Attach(entity, true) Whether the modification is normal (forcibly modifying the RowVersion column will report an error)
8 Attach(entity, true) Yes NotSupportException: An Attach or Add entity has been attempted. The entity is not a new entity and may be loaded from other DataContexts. This operation is not supported.
9 Attach(entity, entity) No No DuplicateKeyException: Cannot add an entity whose key is already in use.
10 Attach(entity, entity) No Yes NotSupportException: An Attach or Add entity has been attempted. The entity is not a new entity and may be loaded from other DataContexts. This operation is not supported.
11 Attach(entity, entity) DuplicateKeyException: Cannot add an entity whose key is already in use.
12 Attach(entity, entity) Yes NotSupportException: An Attach or Add entity has been attempted. The entity is not a new entity and may be loaded from other DataContexts. This operation is not supported.
Attach can only be updated normally in the 7th situation (including the RowVersion column and no foreign key association)! This situation is almost impossible for a database-based system! What kind of API is this?
Summary Let’s calm down and start summarizing.
If you write LINQ to SQL code directly in the UI like List 0, nothing unfortunate will happen. But if you try to abstract away a separate data access layer, disaster strikes. Does this mean that LINQ to SQL is not suitable for the development of multi-layer architecture? Many people say that LINQ to SQL is suitable for the development of small systems, but small size does not mean that it is not layered. Is there any way to avoid so many exceptions?
This article has actually given some clues. In the next essay in this series, I will try to provide several solutions for everyone to choose from.