A generic implementation of the Repository Pattern for LINQ to SQL

Back in 2008 I started working on a project at home which never really went anywhere. It’s still lingering around in the back of my mind but I just gotta find the time to work on it. At the time I figured I could use the project as a learning experience.

It was going to be an ASP.NET site using .NET 3.5 and SQL Server 2008, all tools which I use everyday and know pretty well. To get something more out of it, I chose to use LINQ to SQL as an ‘ORM’, something I hadn’t used before. Then one of my work colleagues recommended that I read up on Domain Driven Design and pointed me in the direction of the ‘Repository Pattern’, so off I went!

The Repository Pattern

Microsoft's vision of the Repository Pattern

Image sourced from http://msdn.microsoft.com

According to Microsoft, a repository should be used to separate the logic that retrieves the data and maps it to the entity model from the business logic that acts on the model. In essence, the repository acts as a mediator between the data source layer and the business layers of the application. It serves several purposes – it retrieves data from data source, maps it to business entities and persists any changes back to the data source. Sounds pretty useful, doesn’t it?

My generic repository class

After setting up the basics and creating a few tables in my database, I started off on my quest to use the Repository Pattern. I started implementing strongly-typed Repository classes for each table in my database and quickly came to the conclusion that this didn’t feel right… I already had half a dozen tables in my database and all my Repository classes were almost identical. It seemed what I actually needed was a generic repository class!

I used my mega-searching-skills on the Google but couldn’t find anyone who had written a generic repository class for LINQ to SQL that returned interfaces of entities at the time. So I got my hands dirty and wrote my own…

?View Code CSHARP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
using System;
using System.Collections.Generic;
using System.Linq;
using MyProject.DataContexts;
 
namespace MyProject.Repositories
{
    public class Repository<T, K> : IRepository<T>
        where T : IEntity
        where K : class, T
    {
        protected MyProjectDataContext DataContext;
 
        public Repository()
        {            
            DataContext = new MyProjectDataContext();
        }
 
        public List<T> All()
        {
            return GetTable.Cast<T>().ToList();
        }
 
        public virtual T CreateInstance()
        {
            return Activator.CreateInstance<K>();
        }
 
        public void Save()
        {
            try
            {
                DataContext.SubmitChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
 
        public virtual T Save(T entity)
        {
            try
            {
                if (entity.Id == 0)
                    DataContext.GetTable<K>().InsertOnSubmit((K)entity);
 
                DataContext.SubmitChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
 
            return entity;
        }
 
        public virtual T Retrieve(int id)
        {
            return GetTable.Cast<T>().Single(e => e.Id == id);
        }
 
        public virtual T Single(Func<T, bool> exp)
        {
            return GetTable.Cast<T>().Single(exp);
        }
 
        public T First(Func<T, bool> exp)
        {
            return GetTable.Cast<T>().First(exp);
        }
 
        public virtual void Delete(T entity)
        {
            try
            {
                GetTable.DeleteOnSubmit((K)entity);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
 
        public virtual List<T> Find(Func<T, bool> exp)
        {
            return GetTable.Cast<T>().Where(exp).ToList();
        }
 
        public virtual List<T> Find(Func<T, bool> exp, Func<T, IComparable> order, bool desc)
        {            
            if (desc)
                return GetTable.Cast<T>().Where(exp).OrderByDescending(order).ToList();
            else
                return GetTable.Cast<T>().Where(exp).OrderBy(order).ToList();
        }
 
        private System.Data.Linq.Table<K> GetTable
        {
            get { return DataContext.GetTable<K>(); }
        }
    }
}

That’s all! One generic repository class for all my tables… I love it!

Using the repository

As it’s supposed to, the repository pattern makes it really easy to access your data in a standardized way. Below is a snippet out of my UserService class…

?View Code CSHARP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
namespace MyProject.Services
{
    public class UserService
    {
        Repository<IUser, User> userRepo;        
 
        public UserService()
        {
            userRepo = new Repository<IUser, User>();
        }
 
        public IUser CreateUserInstance()
        {
            return userRepo.CreateInstance();
        }
 
        public IUser InsertNewUser(IUser user)
        {
            try
            {
                user = userRepo.Save(user);
            }
            catch (Exception ex)
            {
                // Logging
            }
 
            return user;
        }
 
        ...
    }

And so on and so forth…

Conclusion

I’m not saying that LINQ to SQL is awesome, nor am I saying that the Repository Pattern is the way to go. This was just a cool piece of code I wrote a long time ago which I thought I might blog about :)

If you liked this post, share it!
Facebook Twitter Linkedin Delicious Digg Stumbleupon Email

10 thoughts on “A generic implementation of the Repository Pattern for LINQ to SQL

  1. You should be using Expression instead of Func in all the methods, because if you don’t, your expression will not be translated into the SQL, making your statement fetch all rows and executing the filtering expression in memory…

  2. You should be using Expression>Func>T, bool< < instead of Func>T, bool< in all the methods, because if you don’t, your expression will not be translated into the SQL, making your statement fetch all rows and executing the filtering expression in memory…

  3. You should be using Expression > instead of Func in all the methods, because if you don’t, your expression will not be translated into the SQL, making your statement fetch all rows and executing the filtering expression in memory…

  4. Definitely a cool generic approach to this challenge – beats handcoding every repositoryclass. I have to try out this on my brownfield LINQ to SQL-projects soon!

    I am using EF 5 and code first in new projects and found a similar generic repository for that which I am using. I have been looking for the same solution for LINQ to SQL for some time now.

    Thanks for sharing!

Leave a Reply

Your email address will not be published. Required fields are marked *