LINQ is a series of new features provided by Visual Studio 2008, which will expand C# and Visual Basic and can provide powerful query capabilities. As part of LINQ, LINQ to SQL can provide a framework on which the relational data is used as object to run. In one way, it is equal to NHibernate and Castle provided by Microsoft. When we need to access the database, LINQ to SQL will become our first choice.
In LINQ to SQL，all varies in relational database model are strongly typed ,which can provide verification and intelisense when they are being complied .And we can use Query expression including query syntax and method syntax, to access data from the database.
1. Achieve where method
However，the strongly-typed is not beneficial to do abstract operation on the data .So, the developer had to define some particular class for each entity object, which will cause to a lot of duplicate code .If we can achieve a common BaseClass and encapsulate Public Data operation such as Select ,Where ,Add ,Update and Delete ,it can be useful for us to develop the N-tier application development.
Fortunately, Generics Types can help us make it .The way is to call GetTable<T>() method of DataContext .For example ,we can achieve where method by passing a Lambda expression to find the result we want to get.
public IList<TEntity> Where(Func<TEntity, bool> predicate)
{
InitDataContext();
return m_context.GetTable<TEntity>().Where(predicate).ToList<TEntity>();
}
And，we can even use Dynamic Query to expose some methods to receive conditional expression：
public static class DynamicQueryable
{
public static IQueryable<T> Where<T>(this IQueryable<T> source, string predicate, params object[] values)
{
return (IQueryable<T>)Where((IQueryable)source, predicate, values);
}
public static IQueryable Where(this IQueryable source, string predicate, params object[] values)
{
if (source == null) throw new ArgumentNullException(“source”);
if (predicate == null) throw new ArgumentNullException(“predicate”);
LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, typeof(bool), predicate, values);
return source.Provider.CreateQuery(
Expression.Call(
typeof(Queryable), “Where”,
new Type[] { source.ElementType },
source.Expression, Expression.Quote(lambda)));
}
}
public IList<TEntity> Where(string predicate, params object[] values)
{
InitDataContext();
return m_context.GetTable<TEntity>().Where(predicate, values).ToList<TEntity>();
}
Data Entity operation
Of course, for an AbstractBaseClass, there is no problem to query .Because there is no need to care the properties of the entity or the composition of the expression Lambda when we call these methods.
The key is how to update or delete the data sheet record or log. And we must access the entity to operate when we do those operations. The keyword to query is usually the ID of the record. Besides，Object Identity and Change Tracking in Data Context need the ID of the Object to track their own change. Generally speaking, ID is self-growth or Guid field, and used as the primary key of the table. So, we can access the entity according the key.
public void Update(Employee employee)
{
LinqSampleDataContext context = new LinqSampleDataContext();
Employee emp = this.Where(e => e.EmployeeID == employee. EmployeeID);
emp.FirstName = “First Name”;
emp.LastName = “Last Name”;
context.SubmitChanges();
}
Obviously, when we use Generic for example TEntity, we don’t know the property of the Entity. And there is no keyword, how can we associate the change exist of the records. LINQ call Attach method, which the modified object can be attached to Data Context .Attach method have three overloaded versions bellow:
Attach(Object entity ):to attach the entity to DataContext with unmodified state.
Attach(Object entity,bool asModified):To attach all of entities to DataContext with unmodified or modified state .
Attach(Object entity,Object orginal):To attach the entity to DataContext with unmodified or modified state by defining one entity and its original state.
Attach method is usually used to relate a deserialized entity to a new entity in DataContext. But we can relate one entity from one DataContext to other DataContext.This way is very useful .For example ,we can update the records according to the entities modified in the other DataContext .
public void Update(TEntity changedEntity)
{
InitDataContext();
try
{
m_context.GetTable<TEntity>().Attach(changedEntity, true);
m_context.SubmitChanges();
}
catch (ChangeConflictException)
{
m_context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
m_context.SubmitChanges();
}
}
Such realization seems good, but not nearly enough .We can not attach a modified entity, unless the entity is corresponding to the table with TimeStamp column or IsVersion of the PK true.So, we need to add the column to the database to make its type Timestamp.or IsVersion of ID in LINQ to SQL true. My advice is to create a TimeStamp column for all datasheets, which can improve the system performance when doing concurrent process, the system only need to inspect the PK, TimeStamp and no fields for any changes.
Linq in Action
In fact, we can pass the value of the original entity by Generics Types, which there is no need to add TimeStamp column for each table by .The way is to use Action<T>, and the code is as follows:
public void Update(TEntity originalEntity, Action<TEntity> update)
{
InitDataContext();
try
{
m_context.GetTable<TEntity>().Attach(originalEntity);
update(originalEntity);
m_context.SubmitChanges();
}
catch (ChangeConflictException)
{
m_context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
m_context.SubmitChanges();
}
}
Now ,we can call this method by passing Lambda expression :
[TestMethod()]
public void UpdateWithAction()
{
LinqSampleDataContext context = new LinqSampleDataContext ();
EmployeeAccessor accessor = new EmployeeAccessor();
Employee employee = context.Employees.Single(e => e.EmployeeID == 1);
accessor.Update(employee, t => { t.FirstName = “First”; t.LastName = “Last”; });
}
To be disappointed, the test like this can not work well, sometimes NotSupportedException exception will appear. The exception information is as follows:
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.
So, why did the exception appear? What is wrong? The reason is that the entity processed is related with other entities .We can get to know that from the following figure.
If all relations in Employee table can be removed and then the data model is regenerated ,the test will work well .
So, how should we resolve this problem? Obviously, It is not best way to explicitly remove the association of the table .because，it will affect the whole data model. Steve Michelotti give a resolution which is to use partial class to provide a Detach method for each data to remove the relation of the entities 。
public partial class Contact
{
public void Detach()
{
foreach (Address address in this.Addresses)
{
address.Detach();
}
}
}
public partial class Address
{
public void Detach()
{
this._AddressType = default(EntityRef<AddressType>);
this._State = default(EntityRef<State>);
}
}
Yeah，It is a good way ,but not the best .firstly ,with this way ,we need to define Detach method for every data entity .which is too complicated .Secondly ,we can not abstract over this logic with this method .We can not know the detailed type of the TEntity in the BaseClass .Now ,we should use reflection technique.Let us look the following implementation.
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> field , we can call the SetValue method of FieldInfo to assign null to it ,in order to remove the association .However ,for EntitySet field we can’t use the same way because it is set. And if it was set as null, an exception would be thrown. So, we call the clear method of this field through reflection to clean all elements of the set. Finally ,the update method is as follows:
public void Update(TEntity originalEntity, Action<TEntity> update, bool hasRelationship)
{
InitDataContext();
try
{
if (hasRelationship)
{
//Remove the relationship between the entitis
Detach(originalEntity);
}
m_context.GetTable<TEntity>().Attach(originalEntity);
update(originalEntity);
m_context.SubmitChanges();
}
catch (ChangeConflictException)
{
m_context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
m_context.SubmitChanges();
}
}
The delete method is similar, except that there is no need to call the 2nd edition of Attach (Attach (object entity, bool asModified)).The Code snippet is as follows:
public void Delete(TEntity entity, bool hasRelationship)
{
InitDataContext();
try
{
if (hasRelationship)
{
//Remove the relationship between the entities;
Detach(entity);
}
m_context.GetTable<TEntity>().Attach(entity);
m_context.GetTable<TEntity>().DeleteOnSubmit(entity);
m_context.SubmitChanges();
}
catch (ChangeConflictException)
{
m_context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
m_context.SubmitChanges();
}
}
public void Delete(IList<TEntity> entities, bool hasRelationship)
{
InitDataContext();
try
{
if (hasRelationship)
{
//Remove the relationship
foreach (TEntity entity in entities)
{
Detach(entity);
}
}
m_context.GetTable<TEntity>().AttachAll(entities);
m_context.GetTable<TEntity>().DeleteAllOnSubmit(entities);
m_context.SubmitChanges();
}
catch (ChangeConflictException)
{
m_context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
m_context.SubmitChanges();
}
}
Although the association of the entities has been removed, there is no worry whether the final result is right. Attach method is just only to relate the entity with a new entity of DataContext and track the change of the entities 。When submitted and modified ,DataContext will inspect the actual value in the map database and tehn update or delete the record according to the entities passed in .Especially ,we need to set the action of the cascade. We can see the figure 2
Without action,when the entities were deleted ,the exception System.Data.SqlClient.SqlException will be thrown out .The exception is as follows:
The DELETE statement conflicted with the REFERENCE constraint “FK_Orders_Employees”. The conflict occurred in database “Northwind”, table “dbo.Orders”, column ‘EmployeeID’.
Maybe, you have seen InitDataContext method was called in every method as follows:
private TContext m_context = null;
private TContext CreateContext()
{
return Activator.CreateInstance<TContext>() as TContext;
}
private void InitDataContext()
{
m_context = CreateContext();
}
Why we need to create a DataContext instance for every method? The reason is DataContext’ have a cache mechanism. If we create a new instance of DataContext ,and then query the data of the database, and modify the value .Suppose that we do the next query with the same instance ,DataContext will return the data stored in internal cache and not remap the column of the datasheets.More information ,you can see Linq in Acition.
So ,the best practice is to create a new DataContext instance for every operation .Don’t worry about the performance .Because DataContext belong to Lightweight Resources。
Let’s look at the Concurrency .the default option is optimistic Concurrency .Once the value is stored .DataContext will check whether the previous value changed .If a conflict occurs ,DataContext need to know whether to automatically rewrite or store the previous changes or other ways .
This article won’t discuss the Concurrency problem .And we can not know which way is best or worse .Generally speaking, I will use last submit win strategy to deal with concurrency. So, I encapsulate the SubmitChanges method and define it as virtual method. If necessary, subclass can rewrite this method:
public class AccessorBase<TEntity, TContext>
where TEntity : class, new()
where TContext : DataContext, new()
{
private TContext m_context = null;
protected virtual bool SubmitChanges(TContext context)
{
try
{
context.SubmitChanges(ConflictMode.ContinueOnConflict);
return true;
}
catch (ChangeConflictException)
{
context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
context.SubmitChanges();
return true;
}
catch (Exception ex)
{
LogService.Error(“Submit Changes error.”, ex);
return false;
}
finally
{
context.Dispose();
}
}
public bool Update(TEntity originalEntity, Action<TEntity> update, bool hasRelationship)
{
InitDataContext();
try
{
if (hasRelationship)
{
//Remove the relationship between the entitis
}
m_context.GetTable<TEntity>().Attach(originalEntity);
update(originalEntity);
SubmitChanges(m_context);
}
catch (InvalidCastException ex)
{
LogService.Error(“Update Entity error.”, ex);
return false;
}
catch (NotSupportedException ex)
{
LogService.Error(“Update Entity error.”, ex);
return false;
}
catch (Exception ex)
{
LogService.Error(“Update Entity error.”, ex);
return false;
}
}
}
Now, we have a common class, which can be inherited by access class of the entity object. For example:
public class EmployeeAccessor:AccessorBase<Employee,NorthwindDataContext>
{
}
You don’t need to achieve any method and easily do operation on entity object:
[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);
}
}
Even, you can make the Entity class Employee inherit BaseClass directly.
public partial class Employee:AccessorBase<Employee,NorthwindDataContext>
{
}
This method is similar to the way mentioned in “Anemic Domain Model “of Martin Fowler.