Thursday, November 13, 2008

LINQ to SQL: Force update on entity

In my last project, we used LINQ to SQL for both the data access layer and the business logic layer. The entities generated in a DBML file (database markup language) are partial classes. This allowed for implementing business logic directly into the objects by extending the classes' partial definitions. This allowed for rapid development once the data model was established in SQL Server 2005. However, during the project, there were quite a few problems with using LINQ to SQL generated entities for the business objects as I have mentioned in some of my previous blog posts. The most recent problem I ran into involved the lack of support for change tracking properties defined outside of the designer generated file.

In my solution, I ran into a situation in which I wanted to force an Update on an entity. Since I used the DBML designer generated entities to store my business logic, in several of these objects I added new properties to the partial class definitions. I then used stored procedures to save the changes in these new properties to the database. Since all of the CRUD (create, update, delete) operations are handled by the DBML, I designed the system that the update stored procedures for custom properties would be fired in the parent objects Update method (for instance, partial void UpdateAddress(Address instance)). However, I quickly discovered that LINQ to SQL's change tracking is only done on the properties implemented in the designer file.

For instance, if I have an Order table in my database, the designer generates a partial Order class. I extend my order class by adding a CustomerAddress object property. Due to databinding and some UI features I want to implement, I want the Customer and Address information to be displayed and databound to a single object. Therefore, a CustomerAddress object contains all of the properties found in the Customer and Address designer generated classes. When a user makes a change to a property in the CustomerAddress object, ideally this would trigure that the parent object, the Order, would be marked as "dirty" and it would require an update. However, LINQ to SQL change tracking does not support this functionality.

At first, I attempted to fix this problem by having my externally defined properties raise SendPropertyChanged("PropertyName") in the set definition of the property. For instance, when a property in the CustomerAddress property in the Order object is changed, the Order object should raise that the CustomerAddress changed. Therefore, an Update for the Order object should appear in the DataContext.GetChangeSet(). However, this does not work.

The second thought that came to mind was that perhaps exists a method such as "UpdateOnSubmit", similar to InsertOnSubmit and DeleteOnSubmit. However, LINQ to SQL does not support provide a forced Update method.

Ultimately, the solution that worked involved modifying the data model and adding a "LastChange" DateTime column to my Order table. Whenever the CustomerAddress property is modified, the LastChange property is set to the current DateTime. This will trigger an Update on the Order object in the DataContext change set. This enabled the effect I desired: the Order object would be marked as "dirty", causing UpdateOrder(Order instance) to be called. My stored procedures that implemented saving / updating CustomerAddress objects were then executed from within UpdateOrder(Order instance).

If anyone has any questions, or any alternative suggestions, I would appreciate the feedback in the comments!

No comments:

Post a Comment