Unit testing a stored proc called with Entity Framework Core 1.1.2

entity-framework entity-framework-core testing unit-testing

Question

I have a method that uses Entity Framework to call a stored proc, the stored proc returns some data.

Something like the following

public async Task<IActionResult> Get(int customerId)
{
   List<Product> products = _context.Products.FromSql("EXECUTE dbo.SpGatherProductInfo {0}", customerId)
        .ToList();

   if (!products.Any())
   {
      return NotFound();
   }

   return Ok(products);
}

If this were a simple query to a table, I would create an in-memory db, add some fake entries and everything would be fine.

But this uses a stored proc, how do I unit test this?

1
1
9/7/2017 8:18:58 PM

Accepted Answer

A colleague of mine figured out how to mock the stored proc calls.

Then I just use that mock inside a test.

I have written a detailed blog post about it here, there is a lot of code so I won't just copy paste everything from the blog -

https://nodogmablog.bryanhogan.net/2017/11/unit-testing-entity-framework-core-stored-procedures/

0
3/18/2018 3:57:41 AM

Popular Answer

You are focusing too much on implementation concerns. In this scenario entity framework is an implementation concern.

This looks like a good case for encapsulating that concern out into an abstraction.

public interface IProductService  {
    Task<List<Product>> GatherProductInfo(int customerId);
}

and injecting that into the controller

public class ProductsController : Controller {
    private readonly IProductService service;

    public ProductsController(IProductService service) {
        this.service = service;
    }

    public async Task<IActionResult> Get(int customerId) {
        List<Product> products = await service.GatherProductInfo(customerId);

        if (!products.Any()) {
            return NotFound();
        }

        return Ok(products);
    }
}

The IProductService implementation would depend on the context and the actual stored procedure execution while the controller only depends on the abstraction. The controller should not concern itself with where the data comes from.

This now allows the controller to be unit tested in isolation without tight coupling to implementation concerns like entity framework.

public async Task Product_Not_Found() {

    //Arrange
    var customerId = 1;
    var products = new List<Product>();// Currently empty but could easily
                                       //  be populated for another test.
    var mock = new Mock<IProductService>();
    mock.Setup(_ => _.GatherProductInfo(customerId)).Returns(products);

    var controller = new ProductsController(mock.Object);

    //Act
    var result = await controller.Get(customerId);

    //Assert
    result.Should().NotBeNull()
        .And.BeTypeOf<NotFoundResult>();
}


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