How to mock and unit test Stored Procedures in EF

c# entity-framework stored-procedures unit-testing

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?

1
9
1/25/2015 9:37:52 PM

Popular Answer

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.

9
4/23/2015 1:34:47 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow