Click here to Skip to main content
Click here to Skip to main content

A Common Base Class for LINQ to SQL

, 29 Jul 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
A common base class for LINQ to SQL with which you can implement code to access database easily.

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:

/// <span class="code-SummaryComment"><summary></span>
/// Update the entity according to the passed entity.
/// If isModified is true, the entity must have timestamp properties
/// (means isVersion attribute on the Mapping is true).
/// If false, the entity's properties must set the UpdateCheck attribute
/// to UpdateCheck.Never on the Mapping (There are some mistakes still)
/// <span class="code-SummaryComment"></summary></span>
/// <span class="code-SummaryComment"><param name="changedEntity">It shoulde be changed</span>
/// in another datacontext<span class="code-SummaryComment"></param></span>
/// <span class="code-SummaryComment"><param name="isModified">It indicates the entity should be considered dirty</span>
/// and forces the context to add the entity to
/// the list of changed objects.<span class="code-SummaryComment"></param></span>
/// <span class="code-SummaryComment"><param name="hasRelationship">Has Relationship between the entitis</param></span>
public void Update(TEntity changedEntity, bool isModified, bool hasRelationship)
{
    InitDataContext();

    try
    {
        if (hasRelationship)
        {
            //Remove the relationship between the entities
            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:

/// <span class="code-SummaryComment"><summary></span>
/// Update the entity which was passed
/// The changedEntity cann't have the relationship between the entities
/// <span class="code-SummaryComment"></summary></span>
/// <span class="code-SummaryComment"><param name="originalEntity">It must be unchanged entity</span>
/// in another data context<span class="code-SummaryComment"></param></span>
/// <span class="code-SummaryComment"><param name="update">It is Action<T>delegate, </span>
/// it can accept Lambda Expression.<span class="code-SummaryComment"></param> </span>
/// <span class="code-SummaryComment"><param name="hasRelationship">Has relationship between the entities</param></span>
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);

        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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bruce Zhang
Architect
China China
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.

Comments and Discussions

 
GeneralMy vote of 1 Pinmemberdegree45120022-Dec-09 17:59 
GeneralPLINQO Implements Detach Automatically PinmemberEric J. Smith4-May-09 12:22 
PLINQO implements Detach functionality on your entities automatically as well as a ton of other features and enhancements. If you are interested, check out http://www.plinqo.com
GeneralChangeConflictException PinmemberRob Brown (UK)18-Feb-09 4:41 
GeneralRethrowing the Exception PinmemberAdam Tibi5-Aug-08 23:34 
AnswerRe: Rethrowing the Exception PinmemberBruce Zhang6-Aug-08 18:50 
QuestionI have no idea what this article is supposed to show :-?? PinmemberLazar Mihai ( Sharp Override )30-Jul-08 2:22 
AnswerRe: I have no idea what this article is supposed to show :-?? Pinmemberxwpxly30-Jul-08 5:03 
AnswerRe: I have no idea what this article is supposed to show :-?? PinmemberBruce Zhang30-Jul-08 19:57 
GeneralRe: I have no idea what this article is supposed to show :-?? PinmemberLazar Mihai ( Sharp Override )30-Jul-08 20:07 
AnswerRe: I have no idea what this article is supposed to show :-?? PinmemberBruce Zhang31-Jul-08 20:30 
GeneralGreat! PinmemberWaitd29-Jul-08 20:24 
GeneralRe: Great! PinmemberAdam Tibi5-Aug-08 23:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.141029.1 | Last Updated 29 Jul 2008
Article Copyright 2008 by Bruce Zhang
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid