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:
public class EmployeeAccessor:AccessorBase<Employee,NorthwindDataContext>
{
}
Now, you can add, update, delete, or select the data object with it. Please refer to the Unit Test Method:
[TestMethod()]
public void UpdateEmployee()
{
EmployeeAccessor accessor = new EmployeeAccessor();
IList<Employee> entities = accessor.Where(e => e.EmployeeID == 1);
if (entities != null && entities.Count > 0)
{
entities[0].FirstName = "Bruce";
entities[0].LastName = "Zhang";
accessor.Update(entities[0],true,true);
}
}
You may even let the Employee entity derive my base class directly:
public partial class Employee : AccessorBase<Employee, NorthwindDataContext>
{
}
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:
public IList<TEntity> Where(Func<TEntity, bool> predicate)
{
InitDataContext();
return m_context.GetTable<TEntity>().Where(predicate).ToList<TEntity>();
}
We can also expose the method which accepts the condition clause using a dynamic query:
public IList<TEntity> Where(string predicate, params object[] values)
{
InitDataContext();
return m_context.GetTable<TEntity>().Where(predicate, values).
ToList<TEntity>();
}
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:
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);
}
}
}
}
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:
public void Update(TEntity changedEntity, bool isModified, bool hasRelationship)
{
InitDataContext();
try
{
if (hasRelationship)
{
Detach(changedEntity);
}
m_context.GetTable<TEntity>().Attach(changedEntity, isModified);
SubmitChanges(m_context);
}
catch (InvalidCastException ex)
{
throw ex;
}
catch (NotSupportedException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateWithTimeStamp(TEntity changedEntity)
{
Update(changedEntity, true);
}
public void UpdateWithNoCheck(TEntity changedEntity)
{
Update(changedEntity, false);
}
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:
public void Update(TEntity originalEntity,
Action<TEntity> update, bool hasRelationship)
{
InitDataContext();
try
{
if (hasRelationship)
{
Detach(originalEntity);
}
m_context.GetTable<TEntity>().Attach(originalEntity);
update(originalEntity);
SubmitChanges(m_context);
}
catch (InvalidCastException ex)
{
throw ex;
}
catch (NotSupportedException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
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:
protected virtual void SubmitChanges(TContext context)
{
try
{
context.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
context.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
You may override the method in your subclass if you want to change the policy to handle the concurrency conflicts.
Others
Maybe you have noticed that the InitDataContext method is invoked in all methods to access the data. Its implementation is like this:
private TContext m_context = null;
private TContext CreateContext()
{
return Activator.CreateInstance<TContext>() as TContext;
}
private void InitDataContext()
{
m_context = CreateContext();
}
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.
