How to mock and unit test Stored Procedures in EF

Question

I am using a generic repository pattern Repository<TEntity> where repositories access the entities through a context. Then I have a service layer that accepts a context in the constructor. Now I can have multiple repositories in a service, accessing the entities through the same context. Pretty standard. This is perfect for tables/views that map to entities, but I cannot unit test data coming through stored procedures.

This is my current setup:

IDbContext:

public interface IDbContext : IDisposable
{
    IDbSet<T> Set<T>() where T : class;

    DbEntityEntry<T> Entry<T>(T entity) where T : class;

    void SetModified(object entity);

    int SaveChanges();

    // Added to be able to execute stored procedures
    System.Data.Entity.Database Database { get; }
}

Context:

public class AppDataContext : DbContext, IDbContext
{
    public AppDataContext()
        : base("Name=CONNECTIONSTRING")
    {
        base.Configuration.ProxyCreationEnabled = false;
    }

    public new IDbSet<T> Set<T>() where T : class
    {
        return base.Set<T>();
    }


    public void SetModified(object entity)
    {
        Entry(entity).State = EntityState.Modified;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new BookingMap());
    }

    // Added to be able to execute stored procedures
    System.Data.Entity.Database Database { get { return base.Database; } }
}

Generic Repository:

public class Repository<T> : IRepository<T> where T : class
{
    private readonly IDbContext context;

    public Repository(IDbContext context)
    {
        this.context = context;
    }

    public IQueryable<T> GetAll()
    {
        return this.context.Set<T>().AsQueryable();
    }

    public void Add(T entity)
    {
        this.context.Set<T>().Add(entity);
    }

    public void Delete(T entity)
    {
        this.context.Set<T>().Remove(entity);
    }

    public void DeleteAll(IEnumerable<T> entities)
    {
        foreach (var e in entities.ToList())
        {
            this.context.Set<T>().Remove(e);
        }
    }

    public void Update(T entity)
    {
        this.context.Set<T>().Attach(entity);
        this.context.SetModified(entity);
    }

    public void SaveChanges()
    {
        this.context.SaveChanges();
    }

    public void Dispose()
    {
        if (this.context != null)
        {
            this.context.Dispose();
        }
    }
}

Service:

public class BookingService
{
    IDbContext _context;

    IRepository<Booking> _bookingRepository;

    public BookingService(IDbContext context)
    {
        _context = context;

        _bookingRepository = new Repository<Booking>(context);
    }

    public IEnumerable<Booking> GetAllBookingsForName(string name)
    {
        return (from b in _bookingRepository.GetAll()
                where b.Name == name
                select b);
    }
}

Test:

[TestClass]
public class BookingServiceTest
{
    [TestMethod]
    public void Test_Get_All_Bookings_For_Name()
    {
        var mock = new Mock<IDbContext>();
        mock.Setup(x => x.Set<Booking>())
            .Returns(new FakeDbSet<Booking>
            {
                new Booking { Name = "Foo" },
                new Booking { Name = "Bar" }
            });

        BookingService _bookingService = new BookingService(mock.Object);

        var bookings = _bookingService.GetAllBookingsForName(name);

        Assert.AreEqual(2, bookings.Count(), "Booking count is not correct");
    }
}

This is perfect for tables/views that map to entities, but I cannot unit test data coming through stored procedures.

I looked up on the internet and found DbContext.Database property and I am able to execute stored procedures with the .SqlQuery() function and map them to an entity type.

This is what I added to the Repository<T> class:

public IEnumerable<T> SqlQuery(string storedProc, params object[] paramList)
{
    return this.context.Database.SqlQuery<T>(storedProc, paramList);
}

And call the .SqlQuery() function in my service class:

public IEnumerable<Booking> GetAllBookings(string name)
{
    return _bookingRepository.SqlQuery("EXEC GetAllBookings @name = {0}", name);
}

This works great (I am able to get some data), but my question is how can I mock and unit test this?

Answer 1

I realize this is an old question, but to anyone having a similar issue, here's my take.

Why not just use AutoFixture to create an object of the data you usually return from the stored procedure and mock your repository to return it?

public class FooBar
{
    private Fixture fixture;
    private Mock<BookingRepository> bookingRepository; 

    public FooBar()
    {
        fixture = new Fixture();
        bookingRepository= new Mock<BookingRepository>();
    }

    public void TestInitialize()
    {
        var sprocObject = fixture.Create<DbObject>();

        bookingRepository.Setup(x => x.GetAllBookings(It.IsAny<string>())).Returns(sprocObject);
    }

    // Unit tests
}

As Gert Arnold said, you should do integration tests if you want to test the actual stored procedure. If you're testing the service/repository logic you just need to return some data.

Answer 2

I just encountered a need to do this, and my googling led me to this question. I didn't like the answer from Sriram Sakthivel, I didn't want to have to introduce yet another abstraction when I already had one in place:

I already had an interface which I had extracted from my DbContext, and implemented in a test double.

I simply added int ExecuteSqlCommand(string sql, params object[] parameters) to my interface, and in the actual context I implemented it like this:

public int ExecuteSqlCommand(string sql, params object[] parameters)
{
    return Database.ExecuteSqlCommand(sql, parameters);
}

Which obviously just delegates to the actual EF Database property to do the work.

And in my test double I implemented it like this:

public int ExecuteSqlCommand(string sql, params object[] parameters)
{
    return 0;
}

Which doesn't really do anything, which is the point: You aren't unit testing the actual stored procedure, you just need a way to get it to return something useful.

I imagine at some point I 'might' need it to return something other than 0 in a unit test, at which point I'll probably introduce something like a Func<int> executeSqlCommandResultFactory to test double constructor so that I can control it, but at the moment YAGNI applies.