100% Unit Testable Linq To Sql Repository

In this post, I will show you how you can architect your Linq To Sql repository which will have 100% code coverage. I will use our favorite Northwind database along with my ongoing UnityCommonServiceLocatorMVC project. First, lets add a Linq To Sql diagram in our project and drag n drop the Category and Product table that will look something like following:

Northwind-L2S

We are skipping the other tables as they are same that we are doing here, next we will create an interface which will abstract the Linq To Sql DataContext from our application.

public interface IDatabase : IDisposable
{
    IQueryable<Category> CategoryDataSource
    {
        get;
    }

    IQueryable<Product> ProductDataSource
    {
        get;
    }

    IQueryable<TEntity> GetQueryable<TEntity>() where TEntity : class;

    ITable GetEditable<TEntity>() where TEntity : class;

    void Insert<TEntity>(TEntity instance) where TEntity : class;

    void InsertAll<TEntity>(IEnumerable<TEntity> instances) where TEntity : class;

    void Delete<TEntity>(TEntity instance) where TEntity : class;

    void DeleteAll<TEntity>(IEnumerable<TEntity> instances) where TEntity : class;

    void SubmitChanges();
}

And now we will create a partial class for the DataContext that implements this interface:

public partial class Database : IDatabase
{
    public IQueryable<Category> CategoryDataSource
    {
        get
        {
            return GetQueryable<Category>();
        }
    }

    public IQueryable<Product> ProductDataSource
    {
        get
        {
            return GetQueryable<Product>();
        }
    }

    public virtual IQueryable<TEntity> GetQueryable<TEntity>() where TEntity : class
    {
        return GetTable<TEntity>();
    }

    public virtual ITable GetEditable<TEntity>() where TEntity : class
    {
        return GetTable<TEntity>();
    }

    public void Insert<TEntity>(TEntity instance) where TEntity : class
    {
        GetEditable<TEntity>().InsertOnSubmit(instance);
    }

    public void InsertAll<TEntity>(IEnumerable<TEntity> instances) where TEntity : class
    {
        GetEditable<TEntity>().InsertAllOnSubmit(instances);
    }

    public void Delete<TEntity>(TEntity instance) where TEntity : class
    {
        GetEditable<TEntity>().DeleteOnSubmit(instance);
    }

    public void DeleteAll<TEntity>(IEnumerable<TEntity> instances) where TEntity : class
    {
        GetEditable<TEntity>().DeleteAllOnSubmit(instances);
    }
}

By Default Linq To Sql exposes Sql Server tables as Table which is a sealed class, so we can not mock it. But Linq to Sql namespace has an interface ITable which the Table type implements and that is easy to mock, also this ITable exposes InsertOnSubmit, InsertAllOnSubmit, DeleteOnSubmit, DeleteAllOnSubmit. So in our interface we exposed the ITable instead of that concrete Table which makes this partial Database class completely unit testable. We will be using Moq and xUnit.net for our unit tests but you can also use RhinoMock, NUnit, MBUnit or MSTest.

public class DatabaseTest
{
    private readonly Mock<Database> _database;

    public DatabaseTest()
    {
        _database = new Mock<Database>("A dummy connection string");
    }

    [Fact]
    public void CategoryDataSource_Should_Call_GetQueryable()
    {
        _database.Expect(d => d.GetQueryable<Category>()).Returns(new List<Category>().AsQueryable()).Verifiable();

        Assert.NotNull(_database.Object.CategoryDataSource);

        _database.Verify();
    }

    [Fact]
    public void ProductDataSource_Should_Call_GetQueryable()
    {
        _database.Expect(d => d.GetQueryable<Product>()).Returns(new List<Product>().AsQueryable()).Verifiable();

        Assert.NotNull(_database.Object.ProductDataSource);

        _database.Verify();
    }

    [Fact]
    public void Insert_Should_Call_GetEditable_And_InsertOnSubmit()
    {
        var editable = new Mock<ITable>();
        var category = new Category();

        _database.Expect(d => d.GetEditable<Category>()).Returns(editable.Object).Verifiable();
        editable.Expect(e => e.InsertOnSubmit(category)).Verifiable();

        _database.Object.Insert(category);

        _database.Verify();
        editable.Verify();
    }

    [Fact]
    public void InsertAll_Should_Call_GetEditable_And_InsertAllOnSubmit()
    {
        var editable = new Mock<ITable>();
        var categories = new List<Category>();

        _database.Expect(d => d.GetEditable<Category>()).Returns(editable.Object).Verifiable();
        editable.Expect(e => e.InsertAllOnSubmit(categories)).Verifiable();

        _database.Object.InsertAll(categories);

        _database.Verify();
        editable.Verify();
    }

    [Fact]
    public void Delete_Should_Call_GetEditable_And_DeleteOnSubmit()
    {
        var editable = new Mock<ITable>();
        var category = new Category();

        _database.Expect(d => d.GetEditable<Category>()).Returns(editable.Object).Verifiable();
        editable.Expect(e => e.DeleteOnSubmit(category)).Verifiable();

        _database.Object.Delete(category);

        _database.Verify();
        editable.Verify();
    }

    [Fact]
    public void DeleteAll_Should_Call_GetEditable_And_DeleteAllOnSubmit()
    {
        var editable = new Mock<ITable>();
        var categories = new List<Category>();

        _database.Expect(d => d.GetEditable<Category>()).Returns(editable.Object).Verifiable();
        editable.Expect(e => e.DeleteAllOnSubmit(categories)).Verifiable();

        _database.Object.DeleteAll(categories);

        _database.Verify();
        editable.Verify();
    }
}

Now, lets create the repositories which uses this database:

public interface IProductRepository
{
    void Add(Product product);

    void Remove(Product product);

    Product FindById(int id);

    ICollection<Product> FindByCategoryId(int categoryId);

    ICollection<Product> FindAll();
}

public class ProductRepository : IProductRepository
{
    private readonly IDatabase _database;

    public ProductRepository(IDatabase database)
    {
        _database = database;
    }

    public void Add(Product product)
    {
        _database.Insert(product);
    }

    public void Remove(Product product)
    {
        _database.Delete(product);
    }

    public Product FindById(int id)
    {
        return _database.ProductDataSource.SingleOrDefault(p => p.ProductID == id);
    }

    public ICollection<Product> FindByCategoryId(int categoryId)
    {
        return _database.ProductDataSource.Where(p => p.CategoryID == categoryId).ToList().AsReadOnly();
    }

    public ICollection<Product> FindAll()
    {
        return _database.ProductDataSource.ToList().AsReadOnly();
    }
}

And the Unit Test for it:

public class ProductRepositoryTest
{
    private readonly Mock<IDatabase> _database;
    private readonly ProductRepository _repository;

    public ProductRepositoryTest()
    {
        _database = new Mock<IDatabase>();
        _repository = new ProductRepository(_database.Object);
    }

    [Fact]
    public void Add_Should_Use_Database()
    {
        _database.Expect(db => db.Insert(It.IsAny<Product>())).Verifiable();

        _repository.Add(new Product());

        _database.Verify();
    }

    [Fact]
    public void Remove_Should_Use_Database()
    {
        _database.Expect(db => db.Delete(It.IsAny<Product>())).Verifiable();

        _repository.Remove(new Product());

        _database.Verify();
    }

    [Fact]
    public void FindById_Should_Return_Correct_Product_For_The_Specified_Id()
    {
        SetupProductDataSource();

        var product = _repository.FindById(3);

        Assert.Equal(3, product.ProductID);
    }

    [Fact]
    public void FindByCategoryId_Should_Return_Correct_Products_For_The_Specified_CategoryId()
    {
        SetupProductDataSource();

        var products = _repository.FindByCategoryId(2);

        Assert.Equal(2, products.Count);
    }

    [Fact]
    public void FindAll_Should_All_Products()
    {
        SetupProductDataSource();

        var products = _repository.FindAll();

        Assert.Equal(5, products.Count);
    }

    private void SetupProductDataSource()
    {
        Category category1 = new Category{ CategoryID = 1, CategoryName = "Test 1" };
        Category category2 = new Category{ CategoryID = 2, CategoryName = "Test 2" };

        List<Product> products = new List<Product>
                                        {
                                            new Product{ ProductID = 1, ProductName = "Test 1", Category = category1},
                                            new Product{ ProductID = 2, ProductName = "Test 2", Category = category1},
                                            new Product{ ProductID = 3, ProductName = "Test 3", Category = category1},
                                            new Product{ ProductID = 4, ProductName = "Test 4", Category = category2},
                                            new Product{ ProductID = 5, ProductName = "Test 5", Category = category2}
                                        };

        _database.ExpectGet(db => db.ProductDataSource).Returns(products.AsQueryable());
    }
}

I am skipping the CategoryRepository as it almost identical, but you can check it in the attached source codes at the bottom of this post. If I run these unit tests with NCover, we will get the following picture:

Northwind-NCover

As you can see that we have 100% coverage for both ProductRepository and CategoryRepository but 56% for the Database. But if you look it closely, you will find that we have 100% code coverage except the GetQueryable and GetEditable methods in the Database partial class .

Download: LinqTSqlCodeCoverage.zip

Shout it

13 Comments

  • This is a good example. I've only been using Linq2Sql for a few months and am looking at NHibernate since Ms announced it was going to favor EF development.

    What are your short and long term goals in regards to Linq2Sql?

  • I've tried this several times, and keep running into the fact that much of the benefit of having IDatabase is lost. For example, you will not be able to create an EFDatabase, SDSDatabase, or XmlDatabase, since each have different implementations of IQueryable, especially when it comes to querying across child relationships.

  • @justin: Thanks, yes there will be no further development in L2S, still people are using it. The PDC Demo of EF looks promising though I am not sure how they will solve the common requirements such as POCO support, True Persistence ignorance etc etc.

    @Daniel: The purpose of this post was to demonstrate how to architect your L2S application which provides the maximum code coverage.

    Obviously the implementation of Database should vary depending upon the underlying data access technology.

  • I get 2 errors as below:

    1.
    LinqToSql.Repository.Database' does not implement interface member System.IDisposable.Dispose()

    2.
    LinqToSql.Repository.Database' does not implement interface member IDatabase.SubmitChanges()

    And I checked the generated Database.designer.cs
    // This code was generated by a tool.
    // Runtime Version:2.0.50727.3082

    The version is different from yours (2.0.50727.3053).

    The other thing is that how do I achieve this repository approach with my own domain objects? I can see you are using your own domain objects in Kigg project but I yet to figure out how you do the mappings etc.

  • In my opinion, Unittesting linq query providers does not make sense, because the queries are compiled by the underlying query provider. Linq 2 objects behaves different from Linq 2 sql or Linq 2 whatever.

    You should write integration tests for this, or skip it. These unit tests do not give you the guaranty that it actually works.

  • We did the exact same thing on our project so that we could test the DataContext. In the process we developed some code generators to generate the interface. This way when you make changes in the .DBML file, you don't have to manually update the interface. We also created our interface with ITable properties for the tables in the DataContext instead of just ITable properties.

    http://krisscott.net/archive/2008/07/14/use-a-generator-to-test-that-pesky-datacontext.aspx

    http://melgrubb.spaces.live.com/blog/cns!A44BB98A805C8996!256.entry?wa=wsignin1.0&sa=608476965

  • Great information. Thank you. As an "Ioc-DI-MVC" neophyte I have a question about the project(s).

    First of all, in debug mode when the project is first run, when a null value is passed for the controllerType to CommonServiceLocatorControllerFactory, an HttpException is thrown:

    "/favicon.ico" could not be found or it does not implement IController.

    I can hit Ctrl + F5 and avoid this (strangely though, I two browser windows open).

    Then, when I try to go to the Log On page where the AccountController actions are needed, another error appears:

    The current type, UnityCommonServiceLocationMVC.IFormsAuthentication, is an interface and cannot be constructed. Are you missing a type mapping.

    With the earlier project I added a type mapping for the AuthenticationController (even though there are no dependencies to inject) in web.config and this seems to have resolved the issue.

    I am not quite clear, new to Unity as I am, how to resolve this now that the mappings are moved to the Bootstrapper.cs file. I tried adding a mapping to the AccountController there, but evidently the correct syntax escapes me.

    This begs the question: must I map my other types that do not require a dependency, in order to get this strategy using the CommonServiceLocator to work?

    Thank you

  • I have a question and not sure how did you manage such thing in Kigg.
    Suppose you cached you model object or serialized it some where. After a while you wished to update the cached object or deserialized object. In EF the object should be attached to the new ObjectContext. What is the case in LINQ to SQL. what if you wanted to update an instance after its original context is destroyed?!

  • Thanks for the great post.
    When I try to open the source code, I get an error for UnityCommonServiceLocatorMVC.csproj. "The project type is not supported by this installation"

    I am using vs2008 Team System so I have all the products installed. Is there some kind of add-in I need to install to use this project?

    Thanks
    -Joe

  • UPDATE:
    I tried loading on another computer and it loads just fine. So the issue is on my end.

    Thanks
    -Joe

  • @mosessaur: in KiGG the caching was done using decorator pattern which keeps the rest of the application unaware about the caching.

    You should not cache which you have to update frequently. Consider you are using other than _InMemory_ caching like memcached or velocity, keeping it sync will become nightmare.

  • @Zazen: Download the code of http://weblogs.asp.net/rashid/archive/2009/02/26/implementing-unitofwork-pattern-in-linq-to-sql-application.aspx, I have modified the mapping for AccountController, you should be able to run it now.

  • Thank you. I really appreciate the work you have done helping me to begin learning MVC.

Comments have been disabled for this content.