Unit testing LINQ to SQL

Unit testing LINQ to SQL repositories can be very challenging. Unit testing such requires faking hard to mock classes and requires simulation to return your custom data for a particular LINQ statement. In this post, i will show how you can mock your LINQ repositories easily without much digging in.

As,  i was goggling [my start page is bing, its a matter of time when i will be bing-ing :-)] around, found a nice post by Ronnie Holm, where he shows how to unit test a  LINQ to SQL repository. I will follow his trail and use some of the codes from his post. Therefore, first of all we have an employee class:

  1. public class Employee
  2. {
  3.     public int ID { get; set; }
  4.     public DateTime HireDate { get; set; }
  5. }

Secondly, we have an LINQ DataContext implementation that has the Table<Employee> which we are going to mock with our expected collection.

  1. public partial class AdventureWorksDataContext : DataContext
  2. {
  3.     public AdventureWorksDataContext(string conntection): base(conntection)
  4.     {
  5.         // skip
  6.     }
  8.     public Table<Employee> Employees
  9.     {
  10.         get
  11.         {
  12.             return GetTable<Employee>();
  13.         }
  14.     }
  15. }

Next, we have a repository that contains the LINQ query which is going to be queried on our fake collection that will let us validate the LINQ query in subsequent calls.

  1. public class EmployeeRepository
  2. {
  3.     public EmployeeRepository(AdventureWorksDataContext context)
  4.     {
  5.         this.context = context;
  6.     }
  8.     public IQueryable<Employee> GetEmployeesByHireDate(DateTime start, DateTime end)
  9.     {
  10.         return  from e in context.Employees
  11.              where e.HireDate >= start && e.HireDate <= end
  12.              select e;
  13.     }
  15.     private AdventureWorksDataContext context;
  16. }

Here, i have removed the ToList()  from Ronnie’s code. In ideal case, people may not be having ToList() call into their LINQ query all the time. Secondly, i added the a way to pass our faked DataContext to the repository class. As there should be a minimal dependency to keep the code structure immutable and i think its not  a best practice to mock a call for all instances, where the instance starts to smell more like static and keeping a mock call in its instance domain, makes it less interfering.

Finally, its all about mocking , and the steps are:

  1. Create the mock for AdventureWorksDataContext.
  2. Set the context.Employees to return the expected collection.
  3. Finally, act and assert.


Therefore, it becomes:

  1. [TestMethod]
  2. public void ShouldAssertGetEmployeesByHireDate()
  3. {
  4.     var context = Mock.Create<AdventureWorksDataContext>();
  6.     Mock.Arrange(()=> context.Employees).ReturnsCollection(GetFakeEmployees());
  8.     var repository = new EmployeeRepository(context);
  9.     var employees = repository.GetEmployeesByHireDate(new DateTime(2008, 1, 1), DateTime.Now);
  11.     Assert.AreEqual(1, employees.Count());
  12.     Assert.AreEqual(3, employees.FirstOrDefault().ID);
  13. }
  16. private IList<Employee> GetFakeEmployees()
  17. {
  18.     return new List<Employee> {
  19.         new Employee { ID = 1, HireDate = new DateTime(2004, 12, 1) },
  20.         new Employee { ID = 2, HireDate = new DateTime(2006, 7, 1) },
  21.         new Employee { ID = 3, HireDate = new DateTime(2009, 3, 1) } };
  22. }


Here, you might like to ensure that your collection is set right, so it is possible to do

  1. IList<Employee> list = context.Employees.ToList();
  2. Assert.AreEqual(3, list.Count);


That’s it for today. Again special and indirect thanks goes to Ronnie, because i used his code and to him for mentioning JustMock in his post.



Link to the sample project  : LINQToSql001.zip 


Finally, all the things said in my blog, is solely my point of view and i think all tools are great, regardless where i work.



Leave a Reply

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


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>