Introduction

Language-Integrated Query (LINQ) is a set of features in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. As a part of LINQ, LINQ to SQL provides a run-time architecture for managing relational data as objects. To some extent, it equals to an ORM tool or framework such as NHibernate and Castle based on the .NET framework. It becomes our preferred choice gradually when we want to access databases.

In LINQ to SQL, all variables in the Data Model of a relational database can be strongly typed, which provides the benefit of compile-time validation and IntelliSense. We can fetch the data from the database using a query expression (it includes query syntax and method syntax).

However, the strongly typed feature is not conducive to abstract the common logic of data operations, so the developer has to define a specific class to handle the entity object. It results in a large number of repeated codes If we can implement the base class which encapsulates common operations such as Select, Where, Add, Update, and Delete, it will be useful for N-tier applications.

Using the code

Using my base class for LINQ to SQL, you can simply implement the class to access a database without a line of code. What you should do is to let your class derive my base class, like this:

Its behavior is very similar to the Rich Domain Model like Martin Fowler said in his article titled Anemic Domain Model.

The implementation of the base class

The implementation of the query function is very simple. We can invoke a method called GetTable<TEntity>() in the DataContext of LINQ, then invoke some LINQ operations of the GetTable<TEntity>() method, and pass the Lambda Expression to it:

The implementation of the Update method (also the Delete method) is more complex. Though we can use the Attach methods LINQ introduces, there are some constraints for them. So, I have provided a couple of Update methods for different situations.

At first, we must consider whether the entity has relationship with other entities or not. If yes, we have to remove the relationship from it. I have defined a Detach method using Reflection technology, like this:

For EntityRef<T> fields, we may set their values to null by calling the SetValue of FieldInfo to remove the relationship. However, we can’t do EntitySet in the same way because it is a collection. If set to null, it will throw an exception. So, I get the method information of the field and invoke the Clear method to clear all the items in this collection.

For the update operation, we can pass the changed entity and update it. The code snippet is shown below:

Notice that the entity which will be updated must have a timestamp, or it will throw an exception.

Don’t worry about the correctness of the final result when we remove the relationship between the entities. The Attach method is just responsible for associating the entity to a new instance of a DataContext to track the changes. When you submit the changes, the DataContext will check the real value in the mapping database and update or delete the record according to the passed entity. Especially, you should take an action such as Cascade in the database if you want to cascade the delete between the foreign key table and the primary key table.

If the entity has no relationship with others, you may pass "false" to the hasrelationship parameter, like this:

accessor.Update(entities[0],true,false);

It's terrible to create the timestamp column for your data table which exists, maybe it will affect your whole system. (I strong recommend you to create the timestamp column for your database, it will improve the performance because it won’t check all columns if they have changed during handling the concurrency.) My solution to this issue is to pass the original entity and update it with the Action<TEntity> delegate, like this:

Concurrency Issue

Considering the concurrency issue, I give the default implementation for it by defining a virtual method called SubmitChanges. It will handle concurrency conflicts by the rule of last submit win. This method is as shown below:

Why do we need to create a new instance of DataContext for each method? The reason is the caching policy in the DataContext. If you create a new instance of the DataContext and query the data from the database with it, then change its value and execute the same query with the same instance, the DataContext will return the data stored in the internal cache rather than remap the row to the table. For more information, please refer to LINQ in Action.

So, the best practice is to create a new instance of the DataContext for each operation. Don’t worry about the performance, the DataContext is a lightweight resource.

Share

About the Author

I am software architect as liberal professions. I am Microsoft MVP focused on the Connected System such as WCF(Windows Communication Foundation), .NET Remoting and Web Service. I expert in C# programming, ASP.NET programming and the design of Software Architecture. I am familiar with .NET framework, Design Patterns, AOP(Aspect-Oriented Programming) and Agile Methodologies etc. At the same time, I act as Agile Coatch with Scrum in many corporation.My blog is http://brucezhang.wordpress.com.

You are right. But actually the code I posted was showing the function how to update, as for the handling exception, the really implementation was completely different in my project, such as: public bool Update(TEntity changedEntity, bool isModified, bool hasRelationship) { TContext context = Context; try { if (hasRelationship) { //Remove the relationship between the entities Detach(changedEntity); }

Test method LinqSample.Test.EmployeeAccessorTest.UpdateEmployee threw exception: System.InvalidOperationException: 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..

Also what is it supposed to do? If I have a class with a 1 to many relationship I'd like for it to update all the entities and sub entities that I changed not only the simplest properties.

I have viewed codeproject for a few months,and find the article from my country for the first time.the author name Bruce Zhang is fimilar to me.some knowledge about c#,.net Remoting,etc from www.cnblogs.com written by Bruce is very helpful to me.I very pride of you.keep working,and so do me!