Building a Generic DAO / Repository with LINQ
I have been working on a generic DAO / Repository pattern for LINQ on and off the past couple of days. Being a long time Hibernate user, I was little skeptical that LINQ would provide the flexibility that I always got with Hibernate — but so far I have not been disappointed. I will see what happens in the future when I have to get LINQ to map to a poor designed legacy database =).
In 3-tier design, a repository pattern is necessary because it helps make our service layer easier to test. It is always good to have all of your code that generates your SQL in one place. Imagine making your DataContext available to all tiers of your applications. Have fun tracing through ASPX pages, web services, business layer classes trying to find who used a crazy lambda expression that is causing your database to crash.
Unfortunately, LINQ to SQL’s main class for dealing with data is DataContext — which is a concrete class with non-virtual methods. That means that the generated class is pretty much impossible to unit test. There have been some discussions on how to make LINQ more testable, but they involve editing the generated code. I really do not understand the design decision in not having an interface IDataContext.
Anways, let’s move on to some code. Any generic repository should offer the ability to do a couple of things CRUD methods, find methods, and paging/sorting. Let’s start with the interface IRepository (for brevity, I am removing all of my comments):
1: public interface IRepository<E, K> : IDisposable where E : class
2: {
3:
4: Expression<Func<E, bool>> GetKeyLambda(K id);
5:
6: K Save(E group);
7:
8: void Update(K id);
9:
10: void Delete(K groupId);
11:
12: E Find(K id);
13:
14: IEnumerable<E> FindAll();
15:
16: PagedResult<E> FindAll(int start, int limit);
17:
18: PagedResult<E> FindAll(int start, int limit, string property, string sortDirection);
19:
20: PagedResult<E> FindAll(int start, int limit, string property, Expression<Func<E, bool>> sortLambda);
21:
22: }
23:
PagedResult is a simple class that contains the query results and and the total count:
1: public class PagedResult<T>
2: {
3:
4: public PagedResult(IEnumerable<T> results, int totalCount)
5: {
6: Results = results;
7: TotalCount = totalCount;
8: }
9:
10: public IEnumerable<T> Results { get; set; }
11:
12: public int TotalCount { get; set; }
13:
14: }
Now we have a simple interface that defines a number of our data access methods and can be unit tested. Now let’s create our base repository class. We want to be able to pass in the DataContext we are using to the generic repository:
1: public class Repository<E, K> : IRepository<E, K> where E : class
2: {
3: protected DataContext _dataContext;
4:
5: public Repository()
6: {
7: }
8:
9: public Repository(DataContext dataContext)
10: {
11: _dataContext = dataContext;
12: }
13:
14: ...
15:
16: }
If you want to look to create your DataContext using a connection string, you can you subclass Repository:
1: public abstract class AdventureWorksRepositoryBase<E, K> : Repository<E, K> where E : class
2: {
3:
4: private const string ADVENTUREWORKS_CONNECTION_STRING_KEY = "Adventureworks.SQL";
5:
6: public AdventureWorksRepositoryBase()
7: {
8: if (_dataContext == null)
9: {
10: ConnectionStringSettingsCollection connectionStrings = ConfigurationManager.ConnectionStrings;
11: if (connectionStrings == null || connectionStrings.Count == 0)
12: {
13: throw new Exception("Could not find connection string configuration section");
14: }
15:
16: ConnectionStringSettings connectionStringSection = connectionStrings[INTEGRAL_CONNECTION_STRING_KEY];
17: if (connectionStringSection == null)
18: {
19: throw new Exception(String.Format("Could not find the connection string for the AdventureWorks Database. " +
20: "Please check the App.Config or Web.Config for the Connection string key: {0}", ADVENTUREWORKS_CONNECTION_STRING_KEY ));
21: }
22: _dataContext = new AdventureWorksDataContext(connectionStringSection.ConnectionString);
23: }
24:
25: }
Since we only doing the DataContext lookup on construction, remember that this object should not be treated as a singleton. Now back to our base Repository class. Let’s implemented the simplest method — FindAll():
1: public virtual IEnumerable<E> FindAll()
2: {
3: return _dataContext.GetTable<E>();
4: }
LINQ allows you to retrieve any object by using the generic GetTable method. Most LINQ examples you see show you something like: db.Customers which can also be written as: db.GetTable<Customers>. This is useful since we are writing a generic repository.
For the Find, Delete, and Update, and Save methods, we need to have a way to find our object through a Lambda expression. Using straight LINQ to SQL, we would just write our find method by doing:
1: from c in _dataContext.Customers
2: where c.Id == 1
3: select c;
That does not work with the generic Repository because we have no idea what c is at compile time. So we will provide two methods to generate a lambda expression that lets us select by our primary key:
1: public virtual String GetKeyProperty()
2: {
3: return "Id";
4: }
5:
6: public virtual Expression<Func<E, bool>> GetKeyLambda(K id)
7: {
8: var parameter = Expression.Parameter(typeof(E), "e");
9: var propId = Expression.Property(parameter, typeof(E).GetProperty(GetKeyProperty()));
10: return Expression.Lambda<Func<E, bool>>(Expression.Equal(propId, Expression.Constant(id)), parameter);
11: }
Expression trees are a little outside the scope of what I am covering, but the GetKeyLambda method is equivalent to c => c.Id == id. We are assuming that our tables using Id as the common name for their primary key column, but the methods our virtual so they can be changed. If you have a lot of different primary key names, you might want to implement an attribute to add to your repository subclass. Also, GetKeyLambda can be overrode so that you can create a lambda expression to select a composite key: c => c.Key1 == id1 && c.Key2 == id2.
Now that we have a generic way of select our primary key, we can implement some of our other methods:
1: public virtual E Update(K id)
2: {
3: Find(id));
4: }
5:
6: public void Delete(K id)
7: {
8: var removeQuery = _dataContext.GetTable<E>().Single(GetKeyLambda(id));
9: _dataContext.GetTable<E>().Remove(removeQuery);
10: }
11:
12: public virtual E Find(K id)
13: {
14: return _dataContext.GetTable<E>().Single(GetKeyLambda(id));
15: }
The update method does not really do anything besides a find, because to update we just edit the properties directly on our object. I put it in there so that subclasses could modify the behavior. The save method is a little tougher because we want to return the primary key:
1: protected static Dictionary<Type, MetaDataMember> KEY_CACHE = new Dictionary<Type, MetaDataMember>();
2:
3: public virtual K Save(E entity)
4: {
5: DataContext db = _dataContext;
6: MetaDataMember key = null;
7: if (!KEY_CACHE.ContainsKey(typeof(E)))
8: {
9: ReadOnlyCollection<MetaDataMember> md =
10: db.Mapping.MappingSource.GetModel(db.GetType()).GetMetaType(typeof (E)).DataMembers;
11: int keyCount = 0;
12: foreach (MetaDataMember mdm in md)
13: {
14: if (mdm.IsPrimaryKey)
15: {
16: key = mdm;
17: keyCount++;
18: }
19: }
20:
21: if (keyCount == 0)
22: {
23: throw new Exception(
24: String.Format(
25: "Could not find a primary key for entity {0}. You must override the Save method to persist this entity.",
26: entity));
27: }
28:
29: if (keyCount > 1)
30: {
31: throw new Exception(
32: String.Format("The base Repository does not support composite keys (Your entity {0} has more than one primary key). You must override the save method in your subclass.",
33: entity));
34: }
35: KEY_CACHE[typeof(E)] = key;
36: }
37: else
38: {
39: key = KEY_CACHE[typeof(E)];
40: }
41: _dataContext.GetTable<E>().Add(entity);
42: _dataContext.SubmitChanges();
43:
44: return (K)entity.GetType().InvokeMember(key.Member.Name, BindingFlags.Instance | BindingFlags.Public | BindingFlags.GetProperty, null, entity, null);
45: }
Here we use the metadata provided by LINQ to SQL to find the primary key in our classes. For simplicity, this example only handles one primary key per table, but again you can subclass if you want to use a composite key. I am caching the key lookup in this method, but I would not be suprised to find out it runs faster uncached. I still need to test this out.
Implementing a repository per table now becomes simple:
1: public interface ICustomerRepository : IRepository<Customer, int>
2: {
3:
4: Customer SelectCustomerBySomeComplicatedQuery(...);
5:
6: }
7:
8: public class CustomerRepository : AdventureWorksRepositoryBase<Customer, int>, ICustomerRepository
9: {
10:
11: public void SelectCustomerBySomeComplicatedQuery(...)
12: {
13: ...
14: }
15:
16: }
So, how to we use our repository?
1: using (CustomerRepository repository = new CustomerRepository())
2: {
3: repository.Delete(1);
4: Customer customer = repository.Find(1);
5: customer.FirstName = "Ted"; //This will change the value in the database
6: }
Note that we have not told that database to submit our changes yet anywhere. That is why our Repository interface extends IDisposable and we are using the using keyword. On our Repository, Dispose() handles synchronizing the state of our entities with the database:
1: public void Dispose()
2: {
3: _dataContext.SubmitChanges();
4: }
This post is getting long, so I will leave out the paging methods for now. I would love to see someone take this simple design and make it a lot better as I am certain that it can be greatly enhanced. I am really only supporting the simplest use cases for data access.
Adam Cooper:
Eric,
This is excellent, excellent work. I only wish there were more to read.
I’m in the middle of re-developing and re-architecting an online store using LINQ to SQL, but I’m struggling to know where to draw the line on who should have access to the DataContext. I keep coming back to the conclusion that I should use a repository pattern to shield the application from directly working with the DAL that I’m generating with sqlmetal. But the question I keep coming to is “How do I abstract away the use of the DataContext without having to write a lot of redundant object wrappers around my sqlmetal DAL?”
Your blog post here is the closest thing I’ve found to an answer, though I’m still uncertain about some of the details. Is there anything more that you can share? Could you provide downloadable code samples of what you’re writing about here? Being able to browse the files and see the big picture would help me tremendously.
Thanks much,
December 5, 2007, 5:26 pmAdam Cooper
Where is IDataContext? « maonet technotes:
[...] this interesting blog, building Generic DAO using Linq to SQL, idea is identical to NHibernate’s DAO. By doing this, we will have an option to operate data [...]
January 18, 2008, 5:36 pm