Unit testing a stored proc called with Entity Framework Core 1.1.2

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?

I agree with @Nkosi. Is is not unit test. To write integration test I think you should use real database, because you cannot be sure that query or stored procedure in in-memory database will work in the same way as in SQL Server for example. — Valerii, Sep 07 '17 at 20:49
See the caveat at the bottom of [this post](https://www.scottbrady91.com/Entity-Framework/Entity-Framework-Core-In-Memory-Testing). — Gert Arnold, Sep 08 '17 at 09:16
@Nkosi,@Valerii, does that mean this method is untestable by unit tests? — Bryan, Sep 08 '17 at 13:16
I'll answer this: absolutely. Only a real SQL database (and probably only SQL server) understands this statement. — Gert Arnold, Sep 09 '17 at 19:39
@GertArnold I'm looking to test the Action Method, not the stored proc. If I could swap the the call to the stored proc to something sets the List products to empty for one test and adds an entry for another tests, that would be fine. — Bryan, Sep 11 '17 at 19:01
@GertArnold or am I just trying to test something that should not be unit tested? — Bryan, Sep 11 '17 at 19:02
You could create your own extension method `FromSql` in the unit test project. — Gert Arnold, Sep 11 '17 at 19:24
@Bryan did you review my suggestion about abstracting the EF dependency? What are your thoughts? — Nkosi, Sep 12 '17 at 23:09
@Nkosi it is a significant change to the code for the sake of the test. I would rather mock something - I am still looking — Bryan, Sep 13 '17 at 13:43
@Bryan I respect that view. It may be a little misguided however. The change is not for the sake of the test. It for the sake of SOLID code, that is easier to maintain, modify and test. The difficulty in testing your code is directly proportional to how well your code was designed to begin with. It is something you will eventually learn over time. It is better to make those changes early than to accrue technical debt that will cost you more in the long run. — Nkosi, Sep 13 '17 at 13:59
@Nkosi while I appreciate the time and effort you have put into this, it's just not the approach I am looking for. This question is largely an academic one, MS has made EF very testable for DbSets that map to a table, they have NOT made it testable for DbSets that are populated from a stored proc. From what I read, it is on the list for future releases. — Bryan, Sep 13 '17 at 15:15
Does this answer your question? [How to mock Entity Framework's FromSqlRaw method?](https://stackoverflow.com/questions/64302270/how-to-mock-entity-frameworks-fromsqlraw-method) — Michael Freidgeim, Jun 06 '22 at 02:44

Nkosi · Answer 1 · 2017-09-12T00:56:21.587

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>();
}

score 0 · Accepted Answer · answered Mar 18 '18 at 03:57

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/

Unit testing a stored proc called with Entity Framework Core 1.1.2

2 Answers2