In a previous article, I presented several problems you may encounter when using LINQ to SQL for update operations. In fact, this is not a problem I encountered alone. When I looked for answers on the Internet, I found that many people had published similar articles on this topic. But what I am not satisfied with is that although they raised the problem, they did not conduct a detailed analysis. They only gave solutions (such as adding RowVersion columns, removing associations, etc.), but did not explain why they must do this. This is also the original intention of writing the previous article. I hope to find out the solution to the problem step by step through the analysis of LINQ to SQL source code. This article will discuss these methods one by one.
Option 1: Reassignment In the open source framework Ezsocio by TerryLee, Anytao, Ding Xue and others, reassignment is adopted in some places. Inside the Update method, obtain the entities in the database based on the primary key, and then assign values to their properties one by one with the entities in the parameters.
public void UpdateProfile(Profile p)
{
using (RepositoryContext db = new RepositoryContext())
{
var profile = db.GetTable<Profile>().First<Profile>(u => u.ID == p.ID);
profile.Birthday = p.Birthday;
profile.Gender = p.Gender;
profile.Hometown = p.Hometown;
profile.MSN = p.MSN;
profile.NickName = p.NickName;
profile.PhoneNumber = p.PhoneNumber;
profile.QQ = p.QQ;
profile.State = p.State;
profile.TrueName = p.TrueName;
profile.StateRefreshTime = p.StateRefreshTime;
profile.Avatar = p.Avatar;
profile.Website = p.Website;
db.SubmitChanges();
}
}
Brother Yang Guo also provided a reflection method for this solution to achieve automatic copying of attribute values.
But I personally think this is a scheme that avoids reality and avoids reality. It does not use the API provided by LINQ to SQL for update operations, but adopts a roundabout strategy. This is actually a compromise. Is it because the Attach method is "not easy to use", so we don't use it? hehe.
Option 2: Disable object tracking. In this regard, lea proposed that the correct update can be achieved by setting the ObjectTrackingEnabled property of DataContext to false.
public Product GetProduct(int id)
{
NorthwindDataContext db = new NorthwindDataContext();
db.ObjectTrackingEnabled = false;
return db.Products.SingleOrDefault(p => p.ProductID == id);
}
No other code changes.
Why can it update normally after disabling object tracking? Let’s find the answer from the source code.
public bool ObjectTrackingEnabled
{
get
{
this.CheckDispose();
return this.objectTrackingEnabled;
}
set
{
this.CheckDispose();
if (this.Services.HasCachedObjects)
{
throw System.Data.Linq.Error.OptionsCannotBeModifiedAfterQuery();
}
this.objectTrackingEnabled = value;
if (!this.objectTrackingEnabled)
{
this.deferredLoadingEnabled = false;
}
this.services.ResetServices();
}
}
It turns out that when ObjectTrackingEnabled is set to false, DeferredLoadingEnabled will be set to false at the same time. In this way, when executing the query, no data that requires delayed query will be loaded for the entity, so no exception will be thrown during Attach (see the analysis in the previous article).
In MSDN we also get the following useful information: Setting the ObjectTrackingEnable property to false can improve retrieval performance because it reduces the number of items to be tracked. This is a very tempting feature.
However, when disabling object tracking, special attention should be paid to two points: (1) It must be disabled before executing the query. (2) After being disabled, the Attach and SubmitChanges methods can no longer be called. Otherwise, an exception will be thrown.
Option 3: Remove the association. A lame method has been introduced in the previous article, which is to manually set the Category associated with Product to null in the GetProduct method. We can extract this part of the code and put it into a Detach method. Because this Detach is an entity method, partial classes can be used:
public partial class Product
{
public void Detach()
{
this._Category = default(EntityRef<Category>);
}
}
public partial class Category
{
public void Detach()
{
foreach (var product in this.Products)
{
product.Detach();
}
}
}But this method of defining Detach for each entity is too cumbersome. As the number of entities increases, the relationships become more and more complex, and it is easy for missing attributes to appear. Zhang Yi proposed a very elegant method to abstract this logic using reflection:
private void Detach(TEntity entity)
{
foreach (FieldInfo fi in entity.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance))
{
if (fi.FieldType.ToString().Contains("EntityRef"))
{
var value = fi.GetValue(entity);
if (value != null)
{
fi.SetValue(entity, null);
}
}
if (fi.FieldType.ToString().Contains("EntitySet"))
{
var value = fi.GetValue(entity);
if (value != null)
{
MethodInfo mi = value.GetType().GetMethod("Clear");
if (mi != null)
{
mi.Invoke(value, null);
}
fi.SetValue(entity, value);
}
}
}
}
Some people also think that the PropertyChanging and PropertyChanged events should be set to null during Detach, but the overall idea is the same.
Option 4: Use delegation. This is the method given by ZC29 in the comments of my last article. I personally think it is worth learning from.
public void UpdateProductWithDelegate(Expression<Func<Product, bool>> predicate, Action<Product> action)
{
NorthwindDataContext db = new NorthwindDataContext();
var product = db.Products.SingleOrDefault(predicate);
action(product);
db.SubmitChanges();
}
//Client code
ProductRepository repository = new ProductRepository();
repository.UpdateProductWithDelegate(p => p.ProductID == 1, p =>
{
p.ProductName = "Changed";
});
Use Lambda expressions to embed GetProduct logic into UpdateProduct, and use delegates to defer execution of the update logic. This cleverly puts the search and update into a DataContext, thereby bypassing Attach. However, the API of this method is a bit too complex and requires too high levels of client programmers. Moreover, the Get logic must be executed again in Update. Although the performance loss is minimal, it always seems to give people a feeling that it is not DRY enough.
Option 5: Use the UPDATE statement In Ezsocio's source code, I found the RepositoryBase.UpdateEntity method. The splicing of SQL statements is done inside the method, and only the changed columns will be updated. Since ITable is no longer used here and full framework support is required, no further comments will be made. Please refer to Ezsocio's source code for details.
Summary This article lists several solutions that I have found on the Internet in recent days. They all have pros and cons. Which one is better or worse depends on the opinions of different people. In the next article, I will compare the performance of these methods to find the optimal solution.