Repository Pattern in LINQ to SQL (Disconnected)


I have been working on a LINQ to SQL data access layer. This kind of goes against my grain, as I am not overly thrilled about LINQ to SQL as a DAL, but it solves an immediate problem. First, let’s explain the parameters that are important to this DAL.

  1. DAL must be generic or code generated. Do not desire to create a DAL for each type of object, only exceptions
  2. Objects have to be generated without a lot of work (code gen or drag and drop). This is just a time measure.
  3. Objects have to be expressed simply so they can be duplicated in other languages (Java in this case),
  4. Objects have to be served up via web services.

There are more, but these three will help weed things out. Here are some things I have tried.

  • SubSonic and SubStage – SubStage generates the code properly, both for the DAL and the objects. It is okay on point 3, if it would properly run on a web service. Unfortunately, the way null fields are expressed cause a failure.
  • ADO.NET Entity Framework – This is fine on all requirements, except 3. The Entity model uses a strange construct (from a web service standpoint) for joins. Instead of exposing the foreign key field, it creates an object. This is great for multi-field keys, but fails miserably for keeping it simple for those using another language.
  •  DataSets – Fails on requirement #1 at present. I am sure I could work up a generic implementation, but it would take a bit too much time to figure it out. There are examples with LINQ to SQL to work from (mentioned later).
  • Objects loaded with DataSets. A bit easier to genericize, but I have to either write a code gen for the objects or it fails requirement #2.
  • LINQ to SQL with all tables in a single LINQ to SQL DBML. This also fails on point 3, as it adds properties for the join. It is possible the developers in Java could avoid these, however.

I have now opted for a single DBML per table in the database. In many ways, this is like the Active Record pattern, although I am using a Repository (a "well duh" for those who have read the title of this blog entry) to serve up the objects. This is primary for testability.

The Repository Pattern

For those not familiar, the Repository Pattern is designed to wrap a data source in a common interface, allowing you to do your CRUD against a very simple model. In general, you will create a single interface and multiple repositories, one for each class type. With generics, however, you have the option to create a generic repository and only create specific repositories where absolutely needed.

Here is an interface for my Repository.

public interface IRepository<T> where T : class
{
    List<T> All();
    List<T> FindAll(Func<T, bool> exp);
    List<T> Where(Func<T, bool> exp);

    T Single(Func<T, bool> exp);
    T First(Func<T, bool> exp);
    T Last(Func<T, bool> exp);
    T Save(T entity);

    T Insert(T entity);
    void Destroy(T entity);
    void Delete(T entity);
    void UnDelete(T entity);

    void MarkForDeletion(T entity);
    T CreateInstance();

    /// <summary>Persist the data context.</summary>
    void SaveAll();
}
There are a few constructs here that are purely LINQ to SQL, but you can see the basics of how an interface can be used across multiple types of entities. If you get rid of the Func<T, bool> portions  and change your query methodology, you can use the same type of repository pattern for other types of objects.

Repository Pattern with LINQ to SQL (one DBML per table)

First, let me explain the one DBML per table. This simple means I have created a DBML for each table and then dragged only that table on the surface. It would be much easier to create a single DBML for the database, or at least one per set of functionality, but that adds complexity to the objects that would have to be expressed in other languages, even though they would never be used in the other language. Thus, I created one DBML for each table.

 From Code Project, I found Liam McClennan’s entry on how to create a Repository in LINQ to SQL. Here is his repository.

public class Repository<T> : IRepository<T>
  where T : class
{
  protected IDataContextFactory _dataContextFactory;

  public IEnumerable<T> All()
  {
    return GetTable;
  }

  public IEnumerable<T> FindAll(Func<T, bool> exp)
  {
    return GetTable.Where<T>(exp);
  }

  public T Single(Func<T, bool> exp)
  {
    return GetTable.Single(exp);
  }

  public T First(Func<T, bool> exp)
  {
    return GetTable.First(exp);
  }

  public virtual void MarkForDeletion(T entity)
  {
    _dataContextFactory.Context.GetTable<T>().DeleteOnSubmit(entity);
  }

  public virtual T CreateInstance()
  {
    T entity = Activator.CreateInstance<T>();
    GetTable.InsertOnSubmit(entity);
    return entity;
  }
  public void SaveAll()
  {
    _dataContextFactory.SaveAll();
  }

  public Repository(IDataContextFactory dataContextFactory)
  {
    _dataContextFactory = dataContextFactory;
  }

  #region Properties

  private string PrimaryKeyName
  {
    get { return TableMetadata.RowType.IdentityMembers[0].Name; }
  }

  private System.Data.Linq.Table<T> GetTable
  {
    get { return _dataContextFactory.Context.GetTable<T>(); }
  }

  private System.Data.Linq.Mapping.MetaTable TableMetadata
  {
    get { return _dataContextFactory.Context.Mapping.GetTable(typeof(T)); }
  }

  private System.Data.Linq.Mapping.MetaType ClassMetadata
  {
    get { return _dataContextFactory.Context.Mapping.GetMetaType(typeof(T)); }
  }

  #endregion
}

The good here is this Repository contains a lot of methods I need. The bad thing is this type of Repository has to keep an open context. It would be fine for those of you who want to use a Repository on a windows application, but this fails miserably when we need a disconnected type of Repository, like I need for a web service and you might want for a web site.

I found the answer for this issue from Andrew Siemer’s blog entry.

I simply change this routine:

public IEnumerable<T> All()
{
    return context.GetTable<T>().ToList();
}

For this:

public List<T> All()
{
    using (System.Data.Linq.DataContext context = _dataContextFactory.Context)
    {
        return context.GetTable<T>().ToList();
    }
}

Now, it works disconnected. No problems.

There is one thing I have found that does not work. Andrew uses the following to save an object.

public void SaveRespondent(Respondent respondent)
{
    using (ProjectDataContext dc = conn.GetContext())
    {
        if (respondent.RespondentID > 0)
        {
            dc.Respondents.Attach(respondent, true);
        }
        else
        {
            dc.Respondents.InsertOnSubmit(respondent);
        }
        dc.SubmitChanges();
    }
}

If you notice, the Repository here is not generic. I also found that the Attach method does not work when using generics. Here is the error.

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

I am sure this (error on Attach) is why Liam put the SaveAll() method on the DataContext factory instead of on the generic repository, as shown here:

  public void SaveAll()
  {
    _dataContextFactory.SaveAll();
  }

I don’t like this at all, as a factory is not a repository. It breaks the pattern. This gives me two options. The first is to create a repository per object. This adds a lot of maintenance for me. The second is to figure out how to genericize the Save method.

Generic Save

My first run was to create an Insert method, as I got the test that ran an Insert working fairly easily. In Liam’s version, he creates an object off context, so it is seen as a new object when saved back.  Like so:

  public virtual T CreateInstance()
  {
    T entity = Activator.CreateInstance<T>();
    GetTable.InsertOnSubmit(entity);
    return entity;
  }

I am creating my objects out of context, so this will not work. My version is more like this:

public T Insert(T entity)
{
    using (System.Data.Linq.DataContext context = _dataContextFactory.Context)
    {
        //Need something to update instead
        context.GetTable<T>().InsertOnSubmit(entity);
        context.SubmitChanges();

        return entity;
    }
}

This works great for the new entity. It will not work for an update. I then looked back at Andrew’s version, which uses an Attach() and I end up with an error that I have to timestamp the object or set something in the LINQ object. I will have to look at this direction, as my generic implementation looks a bit ugly. As mentioned above, the error message is:

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.

Here is my solution, at least until I can refactor it down. First, I get find the ID column of the table. In order to do this, I am seeking for a column with the name of {TableName} + "Id". This works in my database, as the primary key for Sim is SimId, for SimStatusType is SImStatusTypeId. It will not work for all tables, so my next attempt will be to seek for the Column attribute on the LINQ class and look for IsPrimaryKey = true. Here is how I find the Id column:

//Get types
Type t1 = entity.GetType();

//First find the entity ID
//NOTE: This will only work with primary key = TableName + "Id" (case sensitive)
string idName = t1.Name + "Id";
PropertyInfo pi = t1.GetProperty(idName);
MethodInfo mi = pi.GetGetMethod();
object id = mi.Invoke(entity, null);

I now have the value for the ID I am changing. To get this to update, I update every field on a database version of the entity, except the primary key.

databaseEntity = context.GetTable<T>().First(s => s == entity);

 

Type t2 = databaseEntity.GetType();

foreach (PropertyInfo prop in t1.GetProperties())
{
    if (prop.Name.ToLower() != idName)
    {
        PropertyInfo prop2 = t2.GetProperty(prop.Name);

        //How to get property value
        MethodInfo m1 = prop.GetGetMethod();
        object o = m1.Invoke(entity, null);

        MethodInfo m2 = prop2.GetSetMethod();
        m2.Invoke(databaseEntity, new object[] { o });
    }
}

To make the Update() method a Save() method, I simply query whether the id is 0 or not. This is a bit kludgy, as I can have values in my database that are 0. Unfortunately, I can think of no way around this at present. Since 0 is only found in Type tables and always indicates UNKNOWN, which will never change, there is not too much danger here. Here is my complete Save routine.

public T Save(T entity)
{
  using (System.Data.Linq.DataContext context = _dataContextFactory.Context)
  {
    T databaseEntity;
    //Get types
    Type t1 = entity.GetType();

    //First find the entity ID
    //NOTE: This will only work with primary key = TableName + "Id" (case sensitive)
    string idName = t1.Name + "Id";
    PropertyInfo pi = t1.GetProperty(idName);
    MethodInfo mi = pi.GetGetMethod();
    object id = mi.Invoke(entity, null);

    //Then
    if(0 == (int)id)
    {
      //This is a save
      return Insert(entity);
    }
    else
    {
      databaseEntity = context.GetTable<T>().First(s => s == entity);

      Type t2 = databaseEntity.GetType();

      foreach (PropertyInfo prop in t1.GetProperties())
      {
        bool containsName = prop.Name.Contains(t1.Name);
        bool containsId = prop.Name.ToLower().Contains("id");

        if (prop.Name.ToLower() != idName)
        {
          PropertyInfo prop2 = t2.GetProperty(prop.Name);

          //How to get property value
          MethodInfo m1 = prop.GetGetMethod();
          object o = m1.Invoke(entity, null);

          MethodInfo m2 = prop2.GetSetMethod();
          m2.Invoke(databaseEntity, new object[] { o });
        }
      }
    }

    context.SubmitChanges();
    return databaseEntity;
  }
}

Still TODO:

In order to complete this, I would rather get the primary key using attributes. This allows the model to be used by those who do not use {TableName} + "Id" as their primary key. This will make it more flexible.  I would also like to figure out how to make the Attach() method work using generics. I have the error message, so it is a matter of hammering at it. Thus far, Google did little for the error message, so I am not sure Attach() can be conquered.

Peace and Grace,
Greg

Advertisements

2 Responses to Repository Pattern in LINQ to SQL (Disconnected)

  1. Casey says:

    Greg,On your entity objects, just track state (IE, new, updated, unmodified or deleted). in the constructor…mark as newCreate a method called "OnLoaded" in your classes (cant be an inherited method, blah)..that marks as UnmodifiedImplement change detection to mark unmodified into modifiedand then obviously marking for delete is a manual process.Then in your above Save All Method…..attach or insert or delete approriately. This will prevent the need for all the hacking you did with detecting the primary key…blah.The error you got after trying to reattach is because you havent told the SQL Mapping how to check for concurrency, and since you are disconnected, the data context doesnt know. If you have a concurrency column, just make sure IsVersion is true in its mapping attribute or XML node. If you dont already have a concurrency column, and you dont need one (IE, last write wins is fine), then mark the PK as IsVersion.

  2. Casey says:

    I forgot to mention my comment was around your Generic Save issues. Overall good post, I am using a very similar approach.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: