Over the last few months I have been using LINQ to SQL for accessing a SQL Server database from an MVC 4 WEB API application. Overall it works very well, and when looking at the SQL it generates, it does generate very efficient SQL. Also, I really like the LINQ to SQL syntax as well, very simple and clean, and it is easy to translate a SQL query into the LINQ to SQL syntax.
So normally in the past when I was testing a repository in Java/Scala projects that used Hibernate, we just used SQLite for our unit tests. But since the syntax used in LINQ to SQL is just LINQ expressions, I was wondering if it would be possible to unit test the LINQ expressions in my repository by just passing in a list of objects.
I looked at several existing solutions for unit testing repositories using LINQ to SQL, which looked good, but they each had the same problem. They were creating a repository with a DataContext being provided in the constructor, which allowed it to be mocked in a test. But, the problem with this approach is that the DataContext is designed to be used for a single database transaction, so you should always create and dispose of a new DataContext for reach request to the database. For Example I would expect each method in the repository to look like:
using (var db = new EmployeeDataContext(new DataContext(_connectionString)))
{
return (from employee in db.Employees()
where employee.Id == id select employee).SingleOrDefault();
}
Instead of:
return (from employee in existingContext.Employees()
where employee.Id == id select employee).SingleOrDefault();
The only way this other approach would work reliably would be if the application were creating a new instance of the repository class on every request, but I did not want to do that. So the solution I came up with to get the tests working the way I wanted AND while still creating a new DataContext for each transaction, was to create a wrapper class for the DataContext. This allowed me create a stub for the DataContextWrapper that could be used to replace the real one in a test. This may not be the most eloquent solution, but it worked for what I needed. If anyone has a cleaner solution for accomplishing this, please let me know!
Let’s use the standard database “Employee” example, just to be consistent.
Note: I used Moqand NUnit in this example.
You can download the code from GitHub here.
Employee Entity
Just a standard LINQ to SQL entity object
[Table(Name = "dbo.Employee")]
public class EmployeeEntity
{
[Column(IsPrimaryKey = true, IsDbGenerated = true)] public int Id { get; set; }
[Column] public string FirstName { get; set; }
[Column] public string LastName { get; set; }
[Column] public DateTime StartDate { get; set; }
public override string ToString()
{
return string.Format("Id={0}, FirstName={1}, LastName={2}, StartDate={3}", Id, FirstName, LastName, StartDate);
}
}
Employee Data Context
DataContext for accessing the “Employees” table in our database.
public interface IEmployeeDataContext : IDisposable
{
ITable<EmployeeEntity> Employees();
void ExecuteCommand(string command, params object[] parameters);
void SubmitChanges();
}
public class EmployeeDataContext : IEmployeeDataContext
{
private readonly DataContext _dataContext;
public EmployeeDataContext(DataContext dataContext)
{
_dataContext = dataContext;
}
public ITable<EmployeeEntity> Employees()
{
return _dataContext.GetTable<EmployeeEntity>();
}
public void ExecuteCommand(string command, params object[] parameters)
{
_dataContext.ExecuteCommand(command, parameters);
}
public void SubmitChanges()
{
_dataContext.SubmitChanges();
}
public void Dispose()
{
_dataContext.Dispose();
}
}
Data Context Wrapper
Now, let’s wrap the EmployeeDataContext in another class that has a method called CreateDataContext() which allows us to create a new EmployeeDataContext.
public interface IDataContextWrapper
{
IEmployeeDataContext CreateDataContext();
}
public class DataContextWrapper: IDataContextWrapper
{
private readonly string _connectionString;
public DataContextWrapper(string connectionString)
{
_connectionString = connectionString;
}
public IEmployeeDataContext CreateDataContext()
{
return new EmployeeDataContext(new DataContext(_connectionString));
}
}
Employee Repository
The constructor of our EmployeeRepository now takes a parameter of type IDataContextWrapper. So in each method that calls the database, it can now call _dataContextWrapper.CreateDataContext() in a using block to create a new DataContext for the request and also ensure the DataContext will be disposed.
public interface IEmployeeRepository
{
EmployeeEntity AddEmployee(EmployeeEntity employee);
EmployeeEntity GetEmployee(int id);
List<EmployeeEntity> GetAllEmployees();
List<EmployeeEntity> GetEmployeesByLastName(string lastName);
List<EmployeeEntity> GetEmployeesByStartDate(DateTime minDate, DateTime maxDate);
}
public class EmployeeRepository : IEmployeeRepository
{
private readonly IDataContextWrapper _dataContextWrapper;
public EmployeeRepository(IDataContextWrapper dataContextWrapper)
{
_dataContextWrapper = dataContextWrapper;
}
public EmployeeEntity AddEmployee(EmployeeEntity employee)
{
using (var db = _dataContextWrapper.CreateDataContext())
{
db.Employees().InsertOnSubmit(employee);
db.SubmitChanges();
return employee;
}
}
public EmployeeEntity GetEmployee(int id)
{
using (var db = _dataContextWrapper.CreateDataContext())
{
return (from employee in db.Employees() where employee.Id == id select employee).SingleOrDefault();
}
}
public List<EmployeeEntity> GetAllEmployees()
{
using (var db = _dataContextWrapper.CreateDataContext())
{
return (from employee in db.Employees() select employee).ToList();
}
}
public List<EmployeeEntity> GetEmployeesByLastName(string lastName)
{
using (var db = _dataContextWrapper.CreateDataContext())
{
return (from employee in db.Employees() where employee.LastName == lastName select employee).ToList();
}
}
public List<EmployeeEntity> GetEmployeesByStartDate(DateTime minDate, DateTime maxDate)
{
using (var db = _dataContextWrapper.CreateDataContext())
{
return (from employee in db.Employees() where employee.StartDate >= minDate && employee.StartDate <= maxDate select employee).ToList();
}
}
}
Stub Data Context Wrapper
Now let’s create a stub class for our DataContextWrapper which will be used to replace the real one in our unit tests.
public class StubDataContextWrapper : IDataContextWrapper
{
private readonly IEmployeeDataContext _dataContext;
public StubDataContextWrapper(IEmployeeDataContext dataContext)
{
_dataContext = dataContext;
}
public IEmployeeDataContext CreateDataContext()
{
return _dataContext;
}
}
Stub Employee Table
Also, we will need to create a stub of the employee table, which will be used in our tests to wrap the list of EmployeeEntity objects we will return as the result from our EmployeeDataContext when it is mocked in our tests.
public class StubEmployeeTable : ITable<EmployeeEntity>
{
protected List<EmployeeEntity> internalList;
public StubEmployeeTable(List<EmployeeEntity> list)
{
internalList = list;
}
public void Attach(EmployeeEntity entity)
{
}
public void DeleteOnSubmit(EmployeeEntity entity)
{
internalList.Remove(entity);
}
public void InsertOnSubmit(EmployeeEntity entity)
{
internalList.Add(entity);
}
public IEnumerator<EmployeeEntity> GetEnumerator()
{
return this.internalList.GetEnumerator();
}
System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
return this.internalList.GetEnumerator();
}
public Type ElementType
{
get { return this.internalList.AsQueryable().ElementType; }
}
public System.Linq.Expressions.Expression Expression
{
get { return this.internalList.AsQueryable().Expression; }
}
public IQueryProvider Provider
{
get { return this.internalList.AsQueryable().Provider; }
}
}
Employee Repository Unit Tests
Now we can write our unit tests around the EmployeeRepository. The first step is to mock the EmployeeDataContext. Next we create a StubDataContextWrapper passing in the mock object. Then to create the EmployeeRepository we pass in the StubDataContextWrapper. We setup some EmployeeEntity objects with data we will use for testing, then finally we set the test data as the return value for the Employees() method on the mock of our EmployeeDataContext, using the StubEmployeeTable to wrap the result, to make it look like a response from a LINQ to SQL query.
[TestFixture]
public class EmployeeRepositoryTest
{
private Mock<IEmployeeDataContext> _mockEmployeeDataContext;
private IDataContextWrapper _stubDataContextWrapper;
private IEmployeeRepository _employeeRepository;
[SetUp]
public void Setup()
{
_mockEmployeeDataContext = new Mock<IEmployeeDataContext>();
_stubDataContextWrapper = new StubDataContextWrapper(_mockEmployeeDataContext.Object);
_employeeRepository = new EmployeeRepository(_stubDataContextWrapper);
var employee1 = new EmployeeEntity { Id = 1, FirstName = "John", LastName = "Smith", StartDate = new DateTime(2013,01,10)};
var employee2 = new EmployeeEntity { Id = 2, FirstName = "Frank", LastName = "Smith", StartDate = new DateTime(2013,01,15)};
var employee3 = new EmployeeEntity { Id = 3, FirstName = "Stan", LastName = "Johnson", StartDate = new DateTime(2013,01,20)};
var employees = new List<EmployeeEntity> {employee1, employee2, employee3};
_mockEmployeeDataContext.Setup(x => x.Employees()).Returns(new StubEmployeeTable(employees));
}
[Test]
public void AddEmployeeShouldAddNewEmployeeWhenDoesNotExist()
{
_employeeRepository.AddEmployee(new EmployeeEntity { FirstName = "New", LastName = "Employee", StartDate = new DateTime(2013, 01, 25) });
Assert.AreEqual(4, _employeeRepository.GetAllEmployees().Count);
}
[Test]
public void GetAllEmployeesByIdShouldReturnEmployeeWhenIdExists()
{
var results = _employeeRepository.GetAllEmployees();
Assert.AreEqual(3, results.Count);
}
[Test]
public void GetEmployeeByIdShouldReturnEmployeeWhenIdExists()
{
var result = _employeeRepository.GetEmployee(1);
Assert.AreEqual(1, result.Id);
}
[Test]
public void GetEmployeesByLastNameShouldReturnEmployeeWhenLastNameMatches()
{
var results = _employeeRepository.GetEmployeesByLastName("Smith");
Assert.AreEqual(2, results.Count);
}
[Test]
public void GetEmployeesByStartDateShouldReturnEmployeeWhenStartDateIsWithinRange()
{
var results = _employeeRepository.GetEmployeesByStartDate(new DateTime(2013, 01, 14), new DateTime(2013, 01, 16));
Assert.AreEqual(1, results.Count);
Assert.AreEqual(new DateTime(2013, 01, 15), results[0].StartDate);
}
}
That is all. Of course this example can be made more generic, but it is just a simple example. If you have an example that works better for LINQ to SQL, please let me know, I would be happy to hear about it!
My Adventures in Coding 