Unit test LINQ to SQL
The idea to write this post appears in my head just now when I’m starting to create a small application that uses Entity Framework and I need to do unit testing. The solution described here is useful for LINQ to SQL. I plan to try using the same solution also with Entity Framework. I hope to share my experience with Entity Framework soon.
Some time ago I’ve started to use LINQ to SQL and I also had a question how to unit test it. Now a days a lot of resources on this topic can be found in the internet. In this post I would like to share my experience with unit testing LINQ to SQL and the solution that I used for the past year.
This solution has an interface that provides methods needed to create LINQ queries. Its implementation is a wrapper around LINQ to SQL data context. It can be recognized as UnityOfWork pattern by many people. This class allows me to hide LINQ data context from the classes that implement LINQ queries. Having such abstraction it is possible to create an implementation of the interface that will use LINQ data context and one that will use in-memory storage. The interface class is below.
1: public interface IDataContext
2: {
3: IQueryable<TEntity> GetRepository<TEntity>() where TEntity : Model;
4: void Insert<TEntity>(TEntity entity) where TEntity : Model;
5: void Delete<TEntity>(TEntity entity) where TEntity : Model;
6: void Submit();
7: void Submit(ConflictMode mode);
8: }
In the sample above Model is a base class for all LINQ entity objects. You also can see that IQueryable<T> is used. This allows LINQ to SQL to prepare expression trees based on our queries and execute them later on. The implementation of this interface that uses LINQ to SQL data context is listed below.
1: public class SqlDataContext : IDataContext
2: {
3: private MyDataContext dataContext;
4:
5: public SqlDataContext(MyDataContext context)
6: {
7: ParametersGuard.NotNull("context", context);
8: dataContext = context;
9: }
10:
11: public IQueryable<TEntity> GetRepository<TEntity>() where TEntity : model.Model
12: {
13: ITable table = dataContext.GetTable<TEntity>();
14: return table.Cast<TEntity>();
15: }
16:
17: public void Insert<TEntity>(TEntity entity) where TEntity : model.Model
18: {
19: ITable table = dataContext.GetTable<TEntity>();
20: table.InsertOnSubmit(entity);
21: }
22:
23: public void Delete<TEntity>(TEntity entity) where TEntity : model.Model
24: {
25: ITable table = dataContext.GetTable<TEntity>();
26: table.DeleteOnSubmit(entity);
27: }
28:
29: public void Submit()
30: {
31: dataContext.SubmitChanges();
32: }
33:
34: public void Submit(ConflictMode mode)
35: {
36: dataContext.SubmitChanges(mode);
37: }
38: }
Now, we have an interface and an implementation that can be used to access the database. We also need an implementation that will be our in-memory database that we can use for unit testing. Below is the implementation that is used in the projects that I work on.
1: public class MemoryDataContext : IDataContext
2: {
3: private readonly Dictionary<Type, List<object>> inMemoryDataContext = new Dictionary<Type, List<object>>();
4:
5: public IQueryable<TEntity> GetRepository<TEntity>() where TEntity : Model
6: {
7: List<object> list = null;
8: if (!inMemoryDataContext.TryGetValue(typeof(TEntity), out list))
9: {
10: list = new List<object>();
11: inMemoryDataContext.Add(typeof(TEntity), list);
12: }
13: return list.Cast<TEntity>().AsQueryable<TEntity>();
14: }
15:
16: public void Insert<TEntity>(TEntity entity) where TEntity : Model
17: {
18: List<object> list;
19: if (!inMemoryDataContext.TryGetValue(typeof(TEntity), out list))
20: {
21: list = new List<object>();
22: inMemoryDataContext.Add(typeof(TEntity), list);
23: }
24: list.Add(entity);
25: }
26:
27: public void Delete<TEntity>(TEntity entity) where TEntity : Model
28: {
29: List<object> list;
30: if (inMemoryDataContext.TryGetValue(typeof(TEntity), out list))
31: {
32: list.Remove(entity);
33: }
34: }
35:
36: public void Submit()
37: {
38: OnSubmitPerformed();
39: }
40:
41: public void Submit(System.Data.Linq.ConflictMode mode)
42: {
43: OnSubmitPerformed();
44: }
45: }
This implementation stores all objects into a IDictionary<> instance based on the type of the entity. Having these implementations we can create a class that will allow us to retrieve some information from the database and provide it to the levels above. Then we can also test it. An example is below.
1: public class AnswerMetaDataService : IAnswerMetaDataService
2: {
3:
4: public AnswerMetaDataService(IDataContext context)
5: {
6: DataContext = context;
7: }
8:
9: protected IDataContext DataContext { get; set; }
10:
11: public MetaDataMap GetByIdForAnswer(int id, int answerId)
12: {
13: ParametersGuard.IntAboveZero("id", id);
14: ParametersGuard.IntAboveZero("answerId", answerId);
15:
16: var query = from f in DataContext.GetRepository<MetaData>()
17: join af in DataContext.GetRepository<AnswerMetaData>()
18: on f.Id equals af.MetaDataId
19: join u in DataContext.GetRepository<User>()
20: on af.UserId equals u.Id
21: where f.Id == id && af.AnswerId == answerId
22: select new MetaDataMap
23: {
24: Id = f.Id,
25: Name = f.Name,
26: OriginalName = f.OriginalName,
27: Size = f.Size,
28: UserId = af.UserId,
29: UserFirstName = u.FirstName,
30: UserLastName = u.LastName
31: };
32:
33: MetaDataMap result = query.SingleOrDefault();
34:
35: return result;
36: }
37: }
This class contains one method that retrieves some meta data information for an answer and for related user. The This class accepts an instance of IDataContext in its constructor. If we use this class in the application we will provide an instance of SqlDataContext class and if we want to test this class we will provide an instance of MemoryDataContext class. Probably, you recognized this as a Constructor Dependency Injection. I guess that how to use it in the application is clear. That is why we will continue with a sample test fixture for this class. In this sample NUnit is used.
1: [TestFixture]
2: public class AnswerMetaDataServiceTestFixture : BaseTestFixture
3: {
4: [Test]
5: [TestCase(0, 0, ExpectedException = typeof(ArgumentOutOfRangeException))]
6: [TestCase(1, 0, ExpectedException = typeof(ArgumentOutOfRangeException))]
7: [TestCase(1, 1)]
8: public void GetByIdForAnswerTest(int id, int answerId)
9: {
10: IDataContext dataContext = new MemoryDataContext();
11: IAnswerMetaDataService service = new AnswerMetaDataService(dataContext);
12:
13: MetaData metaData = CreateMetaData(1);
14: dataContext.Insert(metaData);
15: dataContext.Insert(CreateMetaData(2));
16: dataContext.Insert(CreateMetaData(3));
17: dataContext.Insert(CreateRelation(answerId, id, 1));
18: dataContext.Insert(CreateRelation(answerId, id + 1, 2));
19: dataContext.Insert(CreateRelation(answerId, id + 2, 3));
20: dataContext.Insert(CreateUser(1));
21: dataContext.Insert(CreateUser(2));
22: dataContext.Insert(CreateUser(3));
23:
24: MetaDataMap result = service.GetByIdForAnswer(id, answerId);
25:
26: Assert.NotNull(result);
27: Assert.AreEqual(result.Id, metaData.Id);
28: Assert.AreEqual(result.Name, metaData.Name);
29: Assert.AreEqual(result.OriginalName, metaData.OriginalName);
30: Assert.AreEqual(result.Size, metaData.Size);
31: }
32: }
First of all, this test method creates an instance of MemoryDataContext and initialize the service class with it. Then it initializes in-memory data context with several objects that are needed in order to test the query that we have in the method under test (I have few helper methods that creates in one place 
– easy to support). Finally, the method is executed and we can assert the result and to be sure that the method under test returned the object that we requested.
This solution gives us the abstraction that we need in order to test classes that use LINQ to SQL queries. It also allow us to test the exact LINQ queries. Sometimes, it is very useful when we need to create a large query with several parameters or we need to have some sub-queries in large query. Then if we have a mistake we can find it out even before executing the query against the database. For sure, to have a good test we should configure our in-memory database in a proper way.
A tip how to configure in-memory database properly. We should initialize in-memory database in a way that will allow us to test the query that we wrote. I guess it is obvious but I’ve decided to write a sample about it just to make this clear. Let’s assume that we have two LINQ generated objects Person and Company. Every person belongs to a company. Consequently, every person has a generated property Company. In order to get Peron’s name and its Company’s name we can write a query in two different ways. The first way is:
1: var query = from p in dataContext.GetRepository<Person>()
2: where p.Id == personId
3: select new { PersonName = p.Name, CompanyName = p.Company.Name }
The second way is:
1: var query = from p in dataContext.GetRepository<Person>()
2: join c in dataContext.GetRepository<Company>()
3: on p.CompanyId equals c.Id
4: where p.Id == personId
5: select { PersonName = p.Name, CompanyName = c.Name }
Actually, both queries are valid for LINQ to SQL and LINQ to Objects. But there is a difference for the in-memory DB initialization for the first query and the second one. For the first query we can use an initialization as below.
1: dataContext.Insert(new Person { Id = 1, Name = "My Name", CompanyId = 1 });
2: dataContext.Insert(new Person { Id = 2, Name = "Another Name", CompanyId = 1 });
3: dataContext.Insert(new Company { Id = 1, Name = "Our Company" });
And for the second query we need to initialize our in-memory data context as below.
1: Company company = new Company { Id = 1, Name = "Our Company" };
2: dataContext.Insert(new Person { Id = 1, Name = "My Name", Company = company });
3: dataContext.Insert(new Person { Id = 2, Name = "Another Name", Company = company });
There are more examples for the rule described above but they will not be listed here. Just remember that testing the query depends not only on the data that is retrieved but also on the way how the query is written.
Leave a Reply