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:

public class Employee
{
    public int ID { get; set; }
    public DateTime HireDate { get; set; }
}

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

public partial class AdventureWorksDataContext : DataContext
{
    public AdventureWorksDataContext(string conntection): base(conntection)
    {
        // skip
    }
  
    public Table<Employee> Employees
    {
        get
        {
            return GetTable<Employee>();
        }
    }
}

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.

public class EmployeeRepository
{
    public EmployeeRepository(AdventureWorksDataContext context)
    {
        this.context = context;
    }
  
    public IQueryable<Employee> GetEmployeesByHireDate(DateTime start, DateTime end)
    {
        return  from e in context.Employees
             where e.HireDate >= start && e.HireDate <= end
             select e;
    }
  
    private AdventureWorksDataContext context;
}

I added 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 it's not a best practice to mock a call for future instances where it does not have any tie with current code flow.

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:

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

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

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

That’s it for today.

Enjoy!!!