All the information I could find on testing Linq to Sql (DLinq) followed one of the following approaches:

  • Using reflection to hack the internals of System.Data.Linq
  • Duplicating the database (e.g. to Sql Compact Edition)
  • Using the real database with rollback attributes on the test
  • Writing the linq code against a ‘repository’ rather than the designer-generated classes.

Here’s a way to mock the tables so that the linq to sql code can be tested against in-memory tables.

For the example code below, I’m using a dbml-generated DataContext which contains a reference to the Regions table of the Northwind database.

The wizard-generated code looks like this:

[System.Data.Linq.Mapping.DatabaseAttribute(Name="Northwind")]
public partial class MyDataDataContext : System.Data.Linq.DataContext
{

…

    public System.Data.Linq.Table<Region> Regions
    {
        get
        {
            return this.GetTable<Region>();
        }
    }
}

And the method I want to test looks like this:

public void DataAccess()
{
    var context = new MyDataDataContext();
    var max = context.Regions.Max(r => r.RegionID);
    context.Regions.InsertOnSubmit(new Region { RegionID = max + 1, RegionDescription = "New region" });
    context.SubmitChanges();
    }

So first, create an interface to replace the use of MyDataDataContext. Here is an IMockableDataContext which describes the methods you might need from System.Data.Linq.DataContext:

public interface IMockableDataContext : IDisposable
{
    void SubmitChanges();
}

Add IMyDataContext which describes the table properties, they are copied from the designer-generated MyDataContext, but the return types are changed to use IMockableTable instead of System.Data.Linq.Table:

public interface IMyDataContext : IMockableDataContext
{
    IMockableTable<Region> Regions { get; }
}

IMockableTable is based on ITable and IQueryable<TEntity>, the same as System.Data.Linq.Table – so any code using the Regions property should work fine if the types are defined using var:

public interface IMockableTable<TEntity> : ITable, IQueryable<TEntity>
{

}

IMockableTable is implemented by a wrapper class MockableTable. It can be constructed from either an object which implements ITable and IQueryable<TEntity> (like an instance of System.Data.Linq.Table) or from two separate objects (like a mocked ITable and an in-memory collection). All its members call into the object(s) supplied in the constructor.

public class MockableTable<TEntity> : IMockableTable<TEntity>
{
    private readonly ITable table;

    private readonly IQueryable<TEntity> queryable;
    
    public MockableTable(ITable table, IQueryable<TEntity> queryable)
    {
        this.table = table;
        this.queryable = queryable;
    }

    public MockableTable(ITable table)
        : this(table, (IQueryable<TEntity>)table)
    {
    }

    public IEnumerator<TEntity> GetEnumerator()
    {
        return queryable.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return ((IEnumerable)queryable).GetEnumerator();
    }

    public Expression Expression
    {
        get { return queryable.Expression; }
    }

    public Type ElementType
    {
        get { return queryable.ElementType; }
    }

    public IQueryProvider Provider
    {
        get { return queryable.Provider; }
    }

    public void InsertOnSubmit(object entity)
    {
        table.InsertOnSubmit(entity);
    }

    public void InsertAllOnSubmit(IEnumerable entities)
    {
        table.InsertAllOnSubmit(entities);
    }

    public void Attach(object entity)
    {
        table.Attach(entity);
    }

    public void Attach(object entity, bool asModified)
    {
        table.Attach(entity, asModified);
    }

    public void Attach(object entity, object original)
    {
        table.Attach(entity, original);
    }

    public void AttachAll(IEnumerable entities)
    {
        table.AttachAll(entities);
    }

    public void AttachAll(IEnumerable entities, bool asModified)
    {
        table.AttachAll(entities, asModified);
    }

    public void DeleteOnSubmit(object entity)
    {
        table.DeleteOnSubmit(entity);
    }

    public void DeleteAllOnSubmit(IEnumerable entities)
    {
        table.DeleteAllOnSubmit(entities);
    }

    public object GetOriginalEntityState(object entity)
    {
        return table.GetOriginalEntityState(entity);
    }

    public ModifiedMemberInfo[] GetModifiedMembers(object entity)
    {
        return table.GetModifiedMembers(entity);
    }

    public DataContext Context
    {
        get { return table.Context; }
    }

    public bool IsReadOnly
    {
        get { return table.IsReadOnly; }
    }
}

So that MyDataContext can be treated as an IMyDataContext, we can take advantage of the partial declaration of MyDataContext and add an extra partial declaration which includes the explicit implementation of IMyDataContext to create a MockableTable wrapper for the Regions table.

public partial class MyDataDataContext : IMyDataContext
{
    IMockableTable<Region> IMyDataContext.Regions
    {
        get
        {
            return new MockableTable<Region>(Regions);
        }
    }
}

The last stage in fitting the interfaces to the code is to modify the method to be tested:

public void DataAccess()
{
    var context = new MyDataDataContext();
    AddNewRegion(context);
}

public void AddNewRegion(IMyDataContext context)
{
    var max = context.Regions.Max(r => r.RegionID);
    context.Regions.InsertOnSubmit(new Region { RegionID = max + 1, RegionDescription = "New region"});
    
    context.SubmitChanges();
}

And now a test can be written (here I’m using NUnit and Moq):

[Test]
public void TestAddNewRegion()
{
    var mockRegionTable = new Mock<ITable>();
    
    var mockRegionData = new[] { new Region { RegionID = 5, RegionDescription = "Here" }, new Region { RegionID = 9, RegionDescription = "There" } };

    var mockRegions = new MockableTable<Region>(mockRegionTable.Object, mockRegionData.AsQueryable());

    var mockContext = new Mock<IMyDataContext>();
    mockContext.SetupGet(c => c.Regions).Returns(mockRegions);

    var data = new DataClass();
    data.AddNewRegion(mockContext.Object);

    mockRegionTable.Verify(x => x.InsertOnSubmit(It.Is<Region>(r => r.RegionID == 10 && r.RegionDescription == "New region")));
    
    mockContext.Verify(c => c.SubmitChanges());
}

It’s a fair bit of code, but most of it is a one-off:

  • IMockableDataContext and (I)MockableTable are reusable.
  • Adding tables to MyDataContext requires updating IMyDataContext and the partial declaration of MyDataContext.
  • Future mockable data contexts need a new IFooDataContext and partial declaraion of FooDataContext

Any comments, criticisms or suggestions for improvements are welcome!

Comments

Thank you very much. That really helped me

xiety - Jul 3, 2009

Thank you very much. That really helped me!

Like it! Ain't generic non-covariance a pain…

panamack - Mar 1, 2010

Like it! Ain't generic non-covariance a pain...

Thank you! That's what I looked for long time :)

Andrey - Oct 11, 2010

Thank you! That's what I looked for long time :)

Excellent. The only thing you are missing is a sa…

Anonymous - Oct 29, 2010

Excellent. The only thing you are missing is a sample of a full DataContext replacement.